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.

alfallah12345 said...

أفضل جامعة عربية

obat aborsi said...

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

jatin tyagi said...

I have been reading out a lot of your articles.
I will certainly bookmark your Blog.
Free Devops Training
Hybris Training
Emc San Training

Meiqing Xu said...

abercrombie and fitch outlet
christian louboutin
michael kors outlet store
coach outlet
gucci handbags
canada goose sale
canada goose outlet
toms shoes outlet
michael kors outlet clearance
burberry outlet canada

linda paris said...

niche niche dini

Syaniezt Barbie said...

The Article is very interesting and I like it. Harga Fiforlif di Surabaya kota , Jual Fiforlif di Surabaya kota , Agen fiforlif di Surabaya kota , Manfaat Fiforlif , Distributor Fiforlif di Surabaya kota

Syaniezt Barbie said...

The Article is very interesting and I like it. Agen jual fiforlif Balikpapan , Harga Fiforlif di Balikpapan , Jual Fiforlif Murah di Balikpapan , Manfaat Fiforlif , Distributor Fiforlif di Balikpapan

Dwi satriyo said...

The Article is very interesting and I like it. Harga Fiforlif di Semarang , Jual Fiforlif di Semarang , Agen fiforlif di Semarang , Manfaat Fiforlif , Distributor Fiforlif di Semarang

Nunung Kilat said...

This tutorial is very useful for me.
iklan baris gratis
pasang iklan gratis

dong dong23 said...

nike air max thea
pandora bracelet
adidas nmd
ralph lauren outlet
gucci outlet online
ed hardy jeans
oakley sunglasses outlet
mbt outlet
michael kors bags
adidas outlet store
2017.5.11chenlixiang said...

polo ralph lauren outlet
michael kors handbags
mlb jerseys
cheap nfl jerseys
asics,asics israel,asics shoes,asics running shoes,asics israel,asics gel,asics running,asics gel nimbus,asics gel kayano
foamposite shoes
louis vuitton outlet
chrome hearts
oakley sunglasses sale
fitflops uk

Dang Dang Dut said...

Nice Information, thank you for sharing this knowledge

Download aplikasi digital info pendidikan informasi beasiswa s2 jadwal pendaftaran sbmptn game tebak gambar android akreditasi universitas terbuka informasi pendaftaran kuliah

شركة المثالية لتنظيف said...

شركة كشف تسربات المياه بالدمام

123 Moviese said...

Street 2015 Hindi Dubbed Full Movie Watch Online
Street 2015 Hindi Dubbed Full Movie Watch Online
Street 2015 Hindi Dubbed Full Movie Watch Online

raybanoutlet001 said...

rolex watches
ugg outlet
ralph lauren outlet
ugg outlet
ugg boots
valentino outlet
coach bags
converse trainers
michael kors outlet
michael kors handbags

Construction Company City of Yonkers said...

The team at Ajrin Construction Inc. is the best of the best general contractors and support staffs, as selected from within the industries over City of Yonkers NY based upon their achievements and their understanding of complex projects.
Construction Company City of Yonkers

Alice Taylor said...

I would like to thank you for your nicely written post

قمم التميز said...

شركة تنظيف
تهتم شركة قمم التميز باعمال وخدمات التنظيف فى اى مكان فى تنظيف الشقق – البيوت – شركة تنظيف منازل بالجبيل
– المصانع واى مكان يحتاج الى اعمال التنظيف من اجل ان تساعد فى الوصول الى افضل ما تجدة من نتائج مميزة
تنظيف يحتاج للاشياء معقدة من الادوات ، مثل الغرف المنزل لها ادوات التنظيف الخاصة، الصالون المدهب يحتاج لمنظف الخاص ، وسطح فى المنزل يتطلب مواد متخلفة ، فكثير من المنظفات المطلوبة حتى تكون عملية التنظيف سهلة ومن المنظفات : مبيض التواليت ، منظف الحمام، المطهرات ، المنظف للنوافذ والارضيات والغسيل ، سائل الغسيل و اعمال المنزل الصغيرة يمكن انجازاها بفوطة جافة ومنظف جيد ، المنظف المصنوع بقل الامكنيات ، معلقتين من الخل الابيض مع لتر من الماء الساخن وضعهم فى بخاخة . بيكربونات الصوديم لتنظيف البلاط يمكن مزج ثلاثه اجزاء من الماء الساخن مع جزء من الصودا لتنطيف الفرن والثلاجة ، وبيكربونات الصوديم بقليل من سائل الجلى يتكون معجون سميك صالح لتنظيف الحمام ، يمكن استخدام بيكربونات الصوديم وضعه فى صحن صغير ولطرد الروائح الكريهة من الثلاجة ، كربونات الصوديم فعالة فى ازالة البقع الدهنية لانها قلوية ويجب لبس قفازات لاستخدامها .
شركة تنظيف بيارات بالجبيل
الخل الابيض وعصير الليمون يمكن تنظيف السطوح الزجاجية والخشبية المصقولة ، ويمكن استخدام الخل الابيض والليمون للتخلص من الروائح الكريهة والتعطير .
عند التنظيف يجب لبس القفازات لحماية الايدى من المواد المنظفه ، ويمكن لبس الفقازات عند غسيل الصحون وتكون مخصص لعمل فى المطبخ .
شركة شفط بيارات بالجبيل
والمنزل النظيفة من غير فوضوى أجمل بكثير وافضل ،وإذا توفر جميع الادوات النظافة داخل بيت تكون عمليه النظافة سهلة ،
للتنظيف المنزل من اعلى الى اسفل ، اى تنظيف الغبار من اعلى الى اسفل ويمكن استخدام الاجهزة الكهربائية فى عملية التنظيف . شركة نقل اثاث بالجبيل
تنظيف النوافذ باستخدام قطعة قطنية فى مسح الزجاج وللتجفيف بورقة من الجرائد ، وتنظيف الاسطح الزجاجية للمنضدة باستعمال عصير ليمون ودعكها ثم تجفيفها بفوطة ورقية. ويمكن استعمال معجون الاسنان فى ازالة الخدوش الصغيرة من الزجاج .
لتنظيف الاثاث استعمال منظف على قليل من نشادر ، عدم وضع الاثاث فى أشعه الشمس فالشمس تجفف الأثاث ، للتخلص من الحلقات التى تكون على المنضدة بقليل من المنظف مع النشادر ثم مسحها بفوطة جافة ثم تلميعها بالملمع .
لتنظيف الحمام باستخدام الادوات النظافة الخاصة للحمام فى الحمام ، تنظيف السيراميك والمرايا باستخدام مطهر الجراثيم وتنظيف كل شئ فى الحمام مثل مقبض الباب ومفتاح الكهرباء ، والتخلص من القاذورات فى الاركان الضيقة فى الحمام ، تنظيف الخلاطات المياه ومسحها بقطعة قماش مغموسة فى خل او زيت الطعام ، لتنظيف الحمام باستخدام المناديل المعقمة ومسح منطقة الدش والمرحاض

bursa transfer said...

berita sepakbola
master domino
situs poker

Sooban Sandeel said...

This is great movie of Hollywood you never watch before the story about house.
Home Again 2017 Full HD Movie Online Free
Home Again 2017 Full HD Movie Online Free
Home Again 2017 Full HD Movie Online Free
Home Again 2017 Full HD Movie Online Free
Home Again 2017 Full HD Movie Online Free

AFUbestUni said...


obat aborsi said...

Jual Obat Aborsi, Obat Penggugur Kandungan Ampuh

Anonymous said...

I love your blog.. very nice colors & theme. Did
you make this website yourself or did you hire
someone to do it for you? Plz reply as I’m looking to create my own blog the riviera puri and would like to know where u got this from.
thanks a lot. Properti Jabodetabek.

[url=]the riviera puri[/url]
[url=]properti jabodetabek[/url]

dewa daru said...

Obat Aborsi,, Obat Penggugur Kandungan,,
Nice bag you can earn money from that
Thanks for your posting
Visit me @, Obat Aborsi,,
Obat Penggugur kandungan,

mary Brown said...

Hi, Great.. Tutorial is just awesome..It is really helpful for a newbie like me.. I am a regular follower of your blog. Really very informative post you shared here. Kindly keep blogging. If anyone wants to become a .Net developer learn from Dot Net Training in Chennai. or learn thru ASP.NET Essential Training Online . Nowadays Dot Net has tons of job opportunities on various vertical industry.
or Javascript Training in Chennai. Nowadays JavaScript has tons of job opportunities on various vertical industry.

Kamlesh said...

SSC MTS Result

SSC CHSL Admit Card

MP Patwari Result

Rajasthan Police Result

HTET Answer Key

SSC CHSL Answer Key 2018

10th Result 2018

Dora Ali said...

best university in UAE
best university in UAE
best university
أفضل جامعة
أفضل جامعة عربية
أفضل جامعة عربية
أفضل جامعة عربية
أفضل جامعة عربية
أفضل جامعة عربية
أفضل جامعة عربية

洛噷 said...

2018120 leilei3915
michael kors outlet online
true religion outlet online
vintage nhl jerseys
mulberry handbags
uggs outlet
coach outlet
adidas superstar shoes
jordan retro shoes
michael kors outlet
cheap ugg boots

Texus Technology said...

packers and movers panchkula
Packers And Movers in faridabad
Packers And Movers in indore

chenmeinv0 said...

true religion jeans
soccer jerseys
nike mercurial
adidas zx
oakley sunglasses
puma shoes
furla handbags
uggs outlet
ray ban sunglasses

raybanoutlet001 said...

nike shoes
ralph lauren uk
red bottom
coach outlet
ralph lauren uk
coach factory outlet
coach outlet
jordan 4
coach outlet store online
chrome hearts

dong dong23 said...

longchamp handbags
jordan 5
van cleef & arpels jewelry
coach outlet online
michael kors outlet
michael kors outlet online
converse shoes
christian louboutin
ray ban sunglasses
nike outlet store

khairy said...

شركة نقل عفش بالمدينة المنورة
نقل عفش بالمدينة المنورة
نقل عفش
نقل عفش
نقل عفش
نقل عفش
نقل عفش
نقل عفش
تنظيف بالطائف
نقل عفش

dong dong23 said...

salomon shoes
nike blazer pas cher
kenzo clothing
adidas crazy
nike air max 95
roshe run
nike air max
new balance
marc jacobs handbags
michael kors

رنيا محمد said...

شركة مكافحة النمل الابيض بالقطيف

anggita nirbaya said...

By taking the time to read a lot of information like this to add my insight . cara menggugurkan kandungan

Litz Harly said...

When you finish the advance terms with a moneylender, the primary thing that you make sure to do is to fax your papers to the credit supplier. Prior to the records can be faxed they must be organized, Check Cashing Chula vista