I haven’t wrote a blog post on SharePoint in awhile but this one seemed worthy of the effort.
I’ve been experimenting with Office 365 lately and all I wanted to do was add a connection to a Windows Azure SQL Database and surface it up as an external content type.
There is of course no straightforward documentation on how to do this but plenty of content advising that you can. I walked through the motions of creating a DB. I then went into the secure store and created a Target Application ID (Removing from User Name and Password) and then set the user name and password (a separate step).
Next I loaded SharePoint Designer (why? because I couldn’t find NEW External Content Type button anywhere else) and created a new external content type. I selected the appropriate Application ID and tried to connect and was met with the ever so useful error: “Access denied by Business Data Connectivity”
I’ve searched for quite a while and the most common resolution is someone saying that you cannot connect to SQL Azure directly and to use WCF wrapper or some 3rd party product.
I hate writing wrappers for things that should be simple so I kept looking. I finally tracked it down and I’m blogging about it so I can look it up later when I forget and hopefully help someone else
In the SharePoint admin center click BCS then Manage BCS Models and External Content Types
Select Set Metadata Store Permissions
Add your user account and select appropriate permissions.
You will now be able to complete creation of the external content type. You will need to come back in here and use the Set Object Permissions once it is created..
Hope this helps!