Make an External List from a SQL Azure table with Business Connectivity Services and Secure Store

As a SharePoint Administrator or Global Administrator in Microsoft 365, you can use services in Microsoft SharePoint to access data from a Microsoft SQL Azure database. Because SQL Azure is a cloud-based relational database technology, the connection works completely in the cloud. This article describes how to use SharePoint technologies to access data from a SQL Azure database without having to write code.

To use data from a SQL Azure database, you have to create an External List by using Business Connectivity Services (BCS) and Secure Store. BCS connects SharePoint solutions to external data, and Secure Store enables user authentication for the data. By using an External List, you can display the contents of a table from SQL Azure in SharePoint. Users can read, edit, and update the data, all in SharePoint.

For more information about how to use BCS to use external data, see Introduction to external data.

SQL Azure databases are cloud-based relational databases that are created by using SQL Server technology. To learn how to get started with these databases, see Getting Started with Microsoft Azure SQL Database Using the Microsoft Azure Platform Management Portal

Overview of steps in the process

To create an External List that enables you to access data from SQL Azure, you have to complete a sequence of separate steps.

The following table lists the steps and the required software for that step.

What to do:	Where to do it:
Step 1: Set permissions on the BCS Metadata Store	SharePoint admin center
Step 2: Create a Secure Store Mapping	SharePoint admin center
Step 3: Create an External Content Type (ECT)	SharePoint Designer 2010 OR Visual Studio
Step 4: Create an External List	SharePoint admin center
Step 5: Grant permissions to manage your ECT	SharePoint admin center

How BCS and Secure Store work together

Business Connectivity Services (BCS) connects to data in an external data store. You can display the data in an External List, and maintain the data elsewhere. BCS enables you to connect SharePoint solutions to two kinds of resources:

• A SQL Azure database

• A WCF web service that acts as an end-point for some other kind of data store

BCS enables you to access an external data source by using the Secure Store. Secure Store keeps encrypted copies of credentials. It enables a SharePoint Administrator to associate a SharePoint group that uses a single SQL Azure account that can access the target database. When a SharePoint user browses the data in the External List, Secure Store uses the associated SQL Azure account to request the data from SQL.

To make this possible, a SharePoint Administrator defines an External Content Type (ECT) in SharePoint Designer or in Visual Studio. Then, someone with appropriate permissions creates an External List on a SharePoint site by referencing the ECT. Users who have appropriate permission can view or update the list.

Note

SharePoint in Microsoft 365 doesn't support offline use of external lists.

This illustration shows how the connections between the different elements occur:

The following list describes the steps in the connectivity process. Each step in this list corresponds to a number in the previous diagram.

1. The user signs in to SharePoint and opens an External List. The Business Data Connectivity (BDC) service in SharePoint queries External Content Type for that list in the BDC metadata store that contains the list. The query asks for the following information: how to access the external system, which operations are supported, and what credentials to use.

2. The BDC service runtime sends the request (SOAP over HTTP) to the endpoint of the SQL Azure Windows Communication Foundation (WCF) service.

3. The SQL Azure service returns the data in a SOAP envelope.

4. The SharePoint site displays the external list in the user's browser. The user can then perform all the configured operations on the data source for which the user has permissions.

Step 1: Set permissions on the BCS Metadata store

To do this step, follow the procedure in Set permissions on the BCS Metadata Store for a Business Connectivity Services on-premises solution in SharePoint 2013.

When you finish the steps in that procedure, return to this page and start Step 2: Create a Secure Store credentials mapping.

Step 2: Create a Secure Store credentials mapping

Typically, when you create a credentials mapping in Secure Store, you map multiple SharePoint users to a single SQL Azure account. You might use a SharePoint group, or just list all the user names. The SQL Azure account has appropriate permissions to access the target database table. The database that you target in SQL Azure is known as the Secure Store Target Application, or just the Target Application.

Tip

Make sure that you have SQL Azure credentials ready. You'll use these credentials when you create the mapping between SharePoint users and a SQL Azure account.

Create the Secure Store Target Application

To create a Secure Store Target Application, follow these steps.

1. Go to More features in the SharePoint admin center, and sign in with an account that has admin permissions for your organization.

Note

If you have Office 365 operated by 21Vianet (China), sign in to the Microsoft 365 admin center, then browse to the SharePoint admin center and open the More features page.

2. Under Secure store, select Open.

3. On the ribbon, to open the page where you can specify settings for a Target Application, select New.

4. In the Target Application Settings section, do the following:

• 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 email 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.

5. In Credential Fields section, enter the field names that you want to use for the user name and password of the external data system. By default, the Secure Store uses the Windows User Name and Windows Password. We recommend that you accept these values. You cannot edit these Field Types after you finish creating the application.

   

6. In the Target Application Administrators section, in the Target Application Administrators field, enter the name of a group or a list of users who can edit this Target Application. You can also search for the name of a group in Microsoft Online Directory Server. Typically, this section usually contains the name of the SharePoint Administrator or Global Administrator.

7. In the Members section, in the Members field, enter the name of the group that will use the Target Application. Generally, this is a group from the Microsoft Online Directory Service (MSODS).

   If you are a Global Administrator, you can create groups in MSODS in the Microsoft 365 admin center.

8. To create the Target Application, and return to the Secure Store Service page, select OK.

Store credentials for the Target Application

After you create the Target Application, you are ready to enter the credentials that Secure Store uses to access the external data. To set the credentials, follow these steps

1. Go to More features in the SharePoint admin center, and sign in with an account that has admin permissions for your organization.

Note

If you have Office 365 operated by 21Vianet (China), sign in to the Microsoft 365 admin center, then browse to the SharePoint admin center and open the More features page.

2. Under Secure store, select Open.

3. Select the arrow next to the new Target Application, and then select Set Credentials.

4. In the Set Credentials for Secure Store Target Applications (Group) dialog, enter the user name and password of the account. The account must have access to the target database. In the following image, the username is Windows User Name, and the password is Windows Password.

   Important

   Keep a secure record of this information. After you set these credentials, an administrator cannot retrieve them.

   

Step 3: Create the External Content Type

You can create an External Content Type (ECT) by using Microsoft Visual Studio, or by using Microsoft SharePoint Designer 2010. This procedure describes how to create an ECT in SharePoint Designer 2010. Microsoft SharePoint Designer 2010 is available as a free download from the Microsoft Download Center.

You must be a SharePoint Administrator or Global Administrator in your organization to perform this task.

To create an ECT, follow these steps.

1. Start Microsoft SharePoint Designer.

2. To open the SharePoint team site at the root collection, select Open Site. The URL for the root collection will resemble this example URL: https://tailspintoys.sharepoint.com. SharePoint might display a prompt for valid credentials.

   Note

   If SharePoint displays a prompt for you to add a new user, ensure the user account you use has sufficient permissions. The user account must be able to make and test BCS changes to the SharePoint site. Typically, a SharePoint or global admin performs these steps.
   If you want to change to a different user, select Add a new user, select Personal or Organization, sign in to the site as the SharePoint Administrator or Global Administrator, and select Sign In.

3. After the site opens, in the Site Objects tree on the left of the application window, select External Content Types.

4. Select the External Content Types tab, and in the ribbon, to begin the creation process, select External Content Type.

5. In the External Content Type Information section of the page, change the Name and Display Name. Make certain that the Name is descriptive. The Display Name is a friendly name for the ECT.

6. To open the Operation Designer page, select the link Click here to discover external data sources and define operations.

   

7. To open the External Data Source Type Selection dialog, select Add Connection.

8. To access the SQL Azure database, select SQL Server.

   

   Note

   You cannot use on-premises data sources, such as .NET Type, with SharePoint in Microsoft 365. In addition, you cannot use a SQL Server data source that is on-premises with SharePoint in Microsoft 365.

9. When you select SQL Server, specify the following:

   • Database Server name

   • Database Name

   • Name

   Important

   The URL you use to access the database contains the Fully Qualified Server Name. For example, if you access the database via https://aaapbj1mtc.database.windows.net your Fully Qualified Server Name is aaapbj1mtc.database.windows.net. > If you log on at a higher level, such as the Management Portal for Microsoft Azure, you can discover the Fully Qualified Server Name. On the portal page, under Subscriptions, select the name of your subscription. Then, under Fully Qualified Server Name, expand your subscription and the server name. Names of databases appear under each server name.

   

   In the SQL Server Connection dialog, select Connect with Impersonated Custom Identity. Then, in the Secure Store Application ID text box, type the Secure Store Application ID that stores credentials for the target database and then select OK.

10. If you see a prompt for credentials to access the external data source, to access the external data system, enter the correct User name and Password credentials. Then, to connect, select OK.

    The Data Source Explorer tab, you can view a list of tables that are available from the SQL Azure database. To see a list of possible operations for this table, open the shortcut menu for the table.

    You can select specific options such as New Read Item Operation and New Update Operation for the table. Or, you can just select Create All Operations.

    

11. To open a wizard, select Create All Operations, and then select Next.

    On the Operation Properties page of the wizard, in the Errors and Warnings pane, read about any issues. It is important to resolve reported issues that you see. For example, you may have to choose a field to show in an external item picker control. For a customer table, you could choose the customer name.

    

    Important

    The wizard may display a warning message if unique, required fields, such as 'CustomerID', exist in the target table. This is valid if the specified field is required and unique in the table, such as a primary key.

    

    Note

    For more information about how to define filters in external content types, see How to: Define filters for External Item Picker controls .

12. To accept the operations properties that you configured, select Finish. SharePoint Designer displays the operations as a list of ECT Operations.

When this step is complete, you are ready to create an External List to use the data from the external source.

Step 4: Create an External List

You can create an External List by using SharePoint Designer, or by adding an External List as an app on the SharePoint team site. This procedure describes how to create an External List from the team site in SharePoint.

Create an External List by using SharePoint

1. Go to the home page of the SharePoint team site.

2. Select Settings > Add an app.

3. On the Your Apps page, in the search dialog, enter External List, and search.

4. To open the Adding External List dialog, double-click the External List tile.

5. In the Name box, enter a name for the list.

6. In the External Content Type box, enter the name that you want to use. For example, you might use the name of the ECT that you created in SharePoint Designer. Alternatively, you can select the database icon to browse for the name of an ECT.

7. Select Create.

Create an External List by Using SharePoint Designer 2010

1. In SharePoint Designer 2010, on the ribbon, select Create Lists and Forms.

   SharePoint Designer may display a message that states, "Creating lists and forms requires the external content type to be saved". select Yes to save the ECT.

   In the Create List and Forms for databasename Customers dialog, in the List Name text box, enter a meaningful name for the External List. For example, if you created an ECT for the "Customers" database table, you might use "Tailspintoys Customers" in the list name.

2. From the list of Operations, select a Read Item Operation.

3. In the System Instance text box, enter the name of the SQL Azure database.

   

4. To create the External List in the SharePoint site, select OK, and then select Save.

Step 5: Grant permissions to manage the ECT

To finish setting up the External List, you have to grant permissions to the people who will use the list. To grant permissions, follow these steps.

1. Go to More features in the SharePoint admin center, and sign in with an account that has admin permissions for your organization.

Note

If you have Office 365 operated by 21Vianet (China), sign in to the Microsoft 365 admin center, then browse to the SharePoint admin center and open the More features page.

2. Under BCS, select Open.

3. Select Manage BDC Models and External Content Types.

4. Select the check box next to the name of the ECT that you just created, and then select Set Object Permissions.

   Important

   You must manually assign permissions to manage the ECT to a Global Administrator or SharePoint Administrator by using the Set Object Permissions command. If you do not assign these permissions explicitly, the admins won't have permission to manage the ECT.

   

5. In the set object permissions dialog, select the check boxes for all the permissions (( Edit, Execute, Selectable in Clients, and Set Permissions) that the SharePoint admin needs.

   Note

   Make sure that at least one user or group has Set Permissions rights. If you don't assign someone this right, you might create an unmanageable BCS connection.

   

6. Select Propagate permissions to all methods of this external content type. Doing this overwrites any existing permissions.

   Note

   If you want to add a group that can use the External Lists, you must also give the group Execute rights. That enables users in the group to run a query to the external source, and view the results in SharePoint.