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 0.0.0.0 – 255.255.255.255). 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 http://nwindazure.codeplex.com).

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).
image
Go to “Manage Secure Store Service” and create a new Secure Store Application called “Northwind” as shown bellow.
image
image
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).
image
image

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”.
image
image

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.
image
Name the external content type “Customer” and choose the external system to connect to.
image
Choose “SQL Server” for data source type.
image
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).
SNAGHTML4499d00
Create all operations for the Customer table (read list, read, edit, new, delete)
image
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 ).
image

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.
image
image

Test It

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

The End

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

16 comments:

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:

http://www.layer2.de/en/products/Pages/Cloud-Connector-for-SharePoint-2010-Office365.aspx

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 http://www.microsoft.com/download/en/details.aspx?id=13602 (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 http://blogs.msdn.com/b/steve_fox/archive/2011/11/12/leveraging-wcf-services-to-connect-bcs-with-sharepoint-online.aspx

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:

http://www.layer2.de/en/community/FAQs/cloud-connector/Pages/SharePoint-Lists-As-Azure-Web-Frontend-Office-365.aspx

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 http://www.wiziq.com/course/125-comprehensive-introduction-to-sql 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.