Nov 5, 2011

Connecting SQL Azure To SharePoint Online with BCS

*Diret connections to SQL Azure aren't supported by SharePoint Online. You need a Azure WCF Service as a wrapper around SQL Azure. Steve Fox has written a blog post about this: Leveraging Windows Azure WCF Services to Connect BCS with SharePoint Online. Seems that it had worked was a bug ;-)*

Exciting news SharePoint Online now supports Business Connectivity Services (BCS)! What many people really missed in SharePoint online was the ability to connect to external systems. With the last update Microsoft has enabled the eagerly awaited BCS support in SharePoint Online. BCS within SharePoint Online supports SQL and WCF connections which you can easily setup with SharePoint Designer. What is not supported are custom .NET connectors build in Visual Studio. The reason for this restriction is the fact that custom .NET assemblies have to be deployed to the global assembly cache on the server and this sort of deployments aren’t allowed in SharePoint Online.
In this post I’m going to show you how to connect SharePoint Online to SQL Azure.

Setup Northwind Sample DB in SQL Azure

If you haven’t an Azure account yet, you can request a free trial here. Go to the Azure Management Portal and select database on the left pane. Choose your subscription and create a new database server. Enter your admin credentials for the server and add a firewall rule to enable connections from outside (IP Range – Create a new database called Northwind. In the ribbon click on manage to open the SQL Azure Management tool. Login, go to database schema and data and click open query and upload and run the following SQL script (the script has been adapted from the following CodePlex project

Configure SharePoint Secure Store

In order to be able to connect BCS to SQL Azure we must add the SQL credentials to the Secure Store in SharePoint Online. Go to the SharePoint Online Admin Console within the Office 365 Management Portal. You will find two new menu items (Manage Business Data Connectivity and Manage Secure Store Service).
Go to “Manage Secure Store Service” and create a new Secure Store Application called “Northwind” as shown bellow.
After the application has been created you need to set the credentials. You could create a new user in SQL Azure or you can use the admin user that you have specified when you’ve created the database (not recommended for production).

Configure Business Data Connectivity Services

Before you can create a new external content type you have to give your user admin rights in BCS. Go to “Manage Business Data Connectivity” and  select “Set Metadata Store Permissions”.

Create the External Content Type in SharePoint Designer

Now we can create a new external content type in SharePoint Designer. Start SharePoint Designer and open a site in SharePoint Online.
Name the external content type “Customer” and choose the external system to connect to.
Choose “SQL Server” for data source type.
Copy and paste the full qualified name of your Azure SQL Server to the “Database Server” field (you can find the name in the Azure Management Portal). Choose “Connect with Impersonated Custom Identity” and enter “Northwind” (the name that we have defined for our Secure Store Application).
Create all operations for the Customer table (read list, read, edit, new, delete)
Go to the following dialogs with next, next, next and save the external content type. To create a list based on on our new external content type select “Create Lists & Form” (optionally you can decide to generate infopath forms instead of usual SharePoint list forms ).

Set Permissions for The External Content Type

To define who has access to our new external content type go back to the SharePoint Online Admin Console and navigate to “Manage Business Data Connectivity” and choose set permissions.

Test It

Now check that everything works as expected. Open your team site and navigate to your new external list.

The End

In my next post I’ll show you how to connect an Azure WCF Service to SharePoint Online.


admin said...

Great post, thanks for sharing!

Jesper Osgaard said...

Brilliant article

Nalaka said...

Brilliant article.
When the external list get rendered in SP-0 I am getting the following error. Only few records. So not a throttling issue. Is there anyway to view the error log SP-O

Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Microsoft SharePoint Foundation-compatible HTML editor such as Microsoft SharePoint Designer. If the problem persists, contact your Web server administrator.

Correlation ID:c3f2be92-9463-48d8-a3a5-874eb51bf9cd

Jesper Osgaard said...

I get the same error "Unable to display this Web Part". Opening the exteral list in SPD I get this "soap:ServerException of type 'Microsoft.SharePoint.SoapServer.SoapServerException' was thrown.An error has occurred."

Nikolas Charlebois-Laprade said...

Same here, I keep getting the Unable to displays this Web Part error. Any updates on this. Are you still able to connect to your external list now that the BCS update has finally rolled-out completely?

Christian said...

Now I get the same error "Unable to display this Web Part... ". I will contact support...

SharePointFrank said...

A great step forward with data intgeration in Office 365. At this time our clients use the 3rd party Cloud Connector to sync almost any external on-premise and cloud data directly to native SharePoint lists in the cloud, codeless and bi-directional, if required:

Would be intersting to compare with cloud BCS. Anny differences to on-premise?

Nikolas Charlebois-Laprade said...

Christian, I know it is sad to hear, but I'm actually glad you are now getting the error as well :) At least now we now it is an MS issue and not something we did wrong. Thanks again for you great blog post.

Anonymous said...

Any updates as I am getting the same error?

Christian said...

Unfortunately yes, they told me that direct connections to SQL Azure are not supported. And the updated the service description accordingly (Microsoft SharePoint Online for Enterprises Service Description.docx)
Seems that it was a bug that worked ;-)
You have to use an Azure WCF-Service as wrapper for SQL Azure. Read

SharePointFrank said...

Anyway 3rd party connectivity solutions can bridge the gap. See this sample of a bi-directional and codeless connection between a native SharePoint list and a SQL Azure table query:

Nikolas Charlebois-Laprade said...

Thanks for following up on this. That means more fun for us then, gotta learn Azure Services as well.

christi parks said...

Hello, i would like to ask that what is the benefits of sql training, what all topics should be covered and it is kinda bothering me … and has anyone studies from this course of SQL tutorial online?? or tell me any other guidance...
would really appreciate help… and Also i would like to thank for all the information you are providing on sql training.

Anonymous said...

Brilliant! thank you very much for sharing.
PS: Connected actual Azure (2013) and MS SharePoint Online (2010).

Chandrapal said...

thanks for sharing your valuable information..

infopath said...

Thanks a lot for sharing. You have done a brilliant job, and I am really happy I discovered your website.

Chanel Replica Handbags said...

From a creative perspective, one of the best bags ever designed and produced this year belongs to Loewe. The Puzzle Bag continues to awe. Its very origamic structure makes it so easy to carry the bag in so many different ways.

Hug Day said...

happy hug day 2016
happy hug day 2016 Date SMS Messages Quotes
How Hug Day Is Celebrated
Happy hug day sms

Rakshith Roy said...

very neat explanation keep it up
sharepoint administrator training

veterans day free meal said...

Ramadan EID Mubarak Greetings
Wishe You Happy New Year 2017
Mohenjo Daro First Day Collection
Funny Father's Day Cards
Happy Funny Mothers's Day Cards
Clash of Clans Game
Shivaay Collection
Box Office Collection of Dangal
Happy Diwali Quotes
Merry Christmas Cards 2016
Download Happy New Year Wallpapers 2017
Box office collection of Raees
US Open Tennis Match Time Table
total Sultan box office collection
Trip to Shimla Manali
HSC Online Result
Funny Mothers's Day Cards
Funny Father's Day Cards
HSC Result
CBSE Board Exam Result 2016
CBSE Board Exam Result 2016
total Raees box office collection
Happy Diwali HD Wallpapers
Shivaay collection
Gifts Ideas for Mothers Day
Mohenjo Daro Box Office Collection

Anonymous said...

Very good idea you've shared here, from here I can be a very valuable new experience windows azure training in hyderabad

风骚达哥 said...

20160423 junda
air jordans
cheap oakley sunglasses
kate spade outlet
adidas stan smith
ray ban sunglasses
michael kors outlet
nike air max shoes
cheap omega watches
yeezy boost 350
michael kors outlet online
michael kors outlet online
nike free runs
air jordan uk
michael kors outlet online
pandora charms
ray ban outlet
calvin klein outlet
fitflops sale clearance
toms outlet
nike huarache white
sac longchamp
toms outlet
michael kors outlet clearance
bottega veneta outlet
armani watches
gucci handbags
nike blazer
oakley sunglasses
true religion jeans
cheap jordans
ray bans
bottega veneta handbags
jimmy choo outlet
oakley sunglasses
prada outlet
reebok shoes
true religion
burberry outlet

Libin Huang said...

20160426libinferragamo outlet
pandora outlet
ferragamo shoes
nike air max
ferragamo shoes
hermes belt for sale
michael kors factory outlet
swarovski crystal
ferragamo shoes
michael kors outlet
louis vuitton handbags
louis vuitton handbags outlet
swarovski outlet
beats by dre
cazal outlet
mont blanc pens
longchamp solde
fitflops outlet
cartier sunglasses for men
michael kors outlet
louis vuitton outlet
swarovski crystal
cheap nfl jerseys
adidas outlet store
tory burch outlet
cheap oakley sunglasses
mulberry bags
rolex watches,rolex watches,swiss watches,watches for men,watches for women,omega watches,replica watches,rolex watches for sale,rolex replica,rolex watch,cartier watches,rolex submariner,fake rolex,rolex replica watches,replica rolex
oakley sunglasses
tory burch outlet
ray ban sunglasses
louis vuitton outlet
polo ralph lauren
michael kors uk
tiffany outlet

ALDAR said...

HD Videos

HD Video Songs

Mp3 Songs

Sultan box office collection said...

Sultan box office collection

Sultan Box Office Prediction

Sultan Total Collection
Sultan 1st Day Collection
Sultan movie worldwide collection 

Sultan first opening day collection

Sultan Movie Review Ratings

Yuanyuan Lin said...

7.14llllllyuan"timberland shoes"
"soccer jerseys wholesale"
"longchamp pliage"
"cheap oakley sunglasses"
"true religion jeans"
"louis vuitton pas cher"
"rolex orologi"
"christian louboutin shoes"
"michael kors clearance"
"cheap nba jerseys"
"oakley sunglasses wholesale"
"tory burch shoes"
"tory burch outlet"
"chicago blackhawks jerseys"
"longchamp outlet"
"omega watches"
"adidas trainers"
"cheap nike shoes"
"nike outlet store online"
"michael kors outlet clearance"
"michael kors uk"
"montblanc pens"
"michael kors canada"
"longchamp handbags"
"ray ban sunglasses"
"abercrombie and fitch"
"mcm outlet"
"puma shoes"
"ralph lauren uk"
"nike air max 90"
"rolex watches,rolex watches,swiss watches,watches for men,watches for women,omega watches,replica watches,rolex watches for sale,rolex replica,rolex watch,cartier watches,rolex submariner,fake rolex,rolex replica watches,replica rolex"
"mulberry outlet"
"air max 90"
"ferragamo outlet"
"christian louboutin outlet"

Fangyaya said...

michael kors purses
christian louboutin outlet
burberry handbags
coach outlet online
fitflop clearance
jordan 3s
michael kors
michael kors outlet
air jordan 8
replica rolex watches
adidas outlet store
michael kors outlet clearance
retro 11
cheap true religion jeans
cheap true religion
nike air max uk
replica rolex watches
christian louboutin shoes
supra footwear
louis vuitton bags
designer handbags
polo ralph lauren
cheap basketball shoes
adidas originals
coach outlet store online
coach outlet
cheap jordan shoes
oakley vault
jordan retro 4
coach factory outlet online
louis vuitton outlet
air jordan shoes
ed hardy outlet
gucci handbags
adidas yeezy
kobe shoes
discount jordans
cheap jordans
oakley sunglasses
coach factory outlet online

Justin said...

It is an implied principle in the realm of credits that one needs a co-underwriter to guarantee that the understudy advance gets endorsed. All things considered, it doesn't imply that you would not get advances in the event that you don't have a co-endorser to consent to on your arrangement. payday loans san-diego

Dangal Box Office Collection said...

Dangal Box Office Collection
Dangal 1st day Box Office Collection
Dangal first day Box Office Collection
First Day Dangal Box Office Collection
Dangal Box Office Prediction
Dangal Box Office Income
Dangal Total Box Office Collection

ane semprul said...

This blog is so nice to me. I will continue to come here again and again. Visit my link as well. Good luck
obat aborsi
cara menggugurkan kandungan
obat telat datang bulan
obat penggugur kandungan

Justin said...

All in all, on the off chance that you think you fit the necessities depicted in this article and are accepting undue hardship because of the reimbursement of your understudy credits, then chapter 11 is a choice you ought to investigate. Cash Advance

marko said...

Getting the privilege instructive stage is a standout amongst the most critical necessities of each person. It is one of the crucial belonging which everybody needs to do. It makes the unskilled individual to a tender man. You take in the ethical estimations of life. payday loans

Paulo said...

What this implies for all intents and purposes, is that as opposed to paying off one advance in 3 years, another in 5, and another in 10, or having one credit's financing cost be settled and another variable, every one of your advances are aggregated under a solitary framework. payday loans

dada24 Xu said...

canada goose jackets
ugg outlet
cheap uggs
canada gooses outlet
louis vuitton
rolex replica watches
fitflops sale clearance
gucci outlet
cheap jordans

Paulo said...

A restorative school advance is a credit outlined particularly for therapeutic school understudies that takes care of the expense of medicinal school educational cost and other worked together charges. Cash Advance San-diego

Paulo said...

Where the banks had over-stretched out and taken risks to profit, these choices were severely uncovered as rash. When trust in the business sectors fell, the carpet was pulled from under them and everything caved in.