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