In this guide I will be exposing an Azure Database in a SharePoint Online list, there’s a quite a lot of configuration so I’ve tried to capture everything step by step so you can follow along at home.
This blog post is going to operate under the assumption that you have already created your Windows Azure Database
Although I will not take you through how to setup your Azure SQL Database I will step by step show you how to find the information you will need to create the external content type and display it as an external list
There is only a few pieces of information that you will require to connect an azure database to SharePoint Online so please follow these instructions and make notes.
- Login to your Azure Account (https://manage.windowsazure.com)
- Once logged in on the left hand nav click SQL Databases
A List of your available databases will appear like so, The First piece of information to write down is the SERVER name
Click SERVERS and choose your database Server
Choose your database server where the database you would like to connect to SharePoint Online is stored.
- Click DASHBOARD
- On the Very right hand side you will notice your Administrator Login details, write them down.
I now have the following Information, these are extremely important without them you will be unsuccessful.
Server Name: icxrr89hp1
Administrator Login: SQLAdmin
We are now going to need to configure the secure store entry we will need to use some of the Azure database information to complete the secure store configuration so make sure you wrote those details down.
Log onto your SharePoint Admin Centre as a global Admin, and click secure store
- You will need to create a new secure store entry click New
You will be required to enter in parameters such as Target Application ID, Display Name, and Contact Email & Application Type.
Here is Microsoft’s definition of each.
Under Target Application ID, specify a value for a unique ID. This ID maps the External Content type to credentials that are required to authenticate the user. You cannot change the Target Application ID once you create the Target Application.
Under Display Name, specify a user-friendly name for referring to the Target Application.
Under Contact E-mail, specify the e-mail address that you want people to use when they have a question about the Target Application (external data system).
Under Target Application Type, verify that the value is set to Group Restricted. Group Restricted means that the Secure Store contains a mapping that connects a group of SharePoint users to a single, external data account that can act on their behalf. In addition, a Group Restricted application type is restricted to the specified external data system.
Taken from http://office.microsoft.com/en-au/office365-sharepoint-online-enterprise-help/make-an-external-list-from-a-sql-azure-table-with-business-connectivity-services-and-secure-store-HA102933008.aspx
By default, the Secure Store uses the Windows User Name and Windows password, I recommend to leave as is.
In the members field enter the name of the group that will use the Target Application.
Once you have created your secure store entry you will need to set the Credentials on the Target Application
Select the Target Application and click Set
You will be required to set credential owners, Windows User Name and Windows Password – now this is where I made a mistake first time around setting this up – the username you need to enter is the Azure SQL Database Administrator followed by @ your Azure SQL Database name.
In my case these details are:
Administrator Login: SQLAdmin
Server Name: icxrr89hp1
So I end up with the username of SQLAdmin@icxrr89hp1
As you can see the Windows Username is my Azure SQL Administrator account ‘@’ My SQL Database Server name
Creating the External Content Type
Now that the Secure Store is configured correctly we need to create the external content type, you will need SharePoint Designer 2013 installed to be able to create the external content type.
- Open up SharePoint Designer 2013
Click Open Site
Type the url in the site name: field and click Open , this can take a minute or two to load so be patient
- Once the Site collection has loaded on the left hand navigation pane click External Content Types
On the SharePoint Ribbon click External
This will load up the new external content type wizard,
- Give the Content Type a name
- select the ‘Click here to discover external data sources and define operations‘ to begin the process
- Click Add Connection
- Select SQL Server as Data Source Type and click OK
- Once you select SQL Server, specify the following
- Database Server Name
IMPORTANT please note the Database Server is to be entered as your databasename.database.windows.net , you must include this for it to work! You cannot use a SQL Server data source that is on-premise with SharePoint Online
Select Connect with Impersonated Custom Identity and type in your secure Store App ID that we had from Secure Store
- Click OK
You will prompted to enter in the BCS Secure Store info for your Application ID enter in the Administrator account the Database Server name as the domain and your password , click OK to proceed
Once done you will be able to expand out all your Tables Columns etc.
- Right click the table you would like to make a list and click Create All Operations
- An All Properties display will appear click Next
Configure your Parameters Configuration accordingly to your needs , click Next
- Enter any Filter Parameters you have especially on a large table this will be needed, in my case I only have dummy data so I’m going to leave it empty.
- Click Finish
- Press ctrl+s to save
Create a list from your External Content Type
Now that we have our external content type created we need to create a list from our new content type
- In SharePoint Designer Click External Content Types > Your Content Type
On the SharePoint Ribbon click Create Lists & Form
Give your new list a name and click OK
Once Created navigate to your site collection where you created the list and you should see your data from your external database available in a SharePoint List