Exercise 3: Consuming SQL Azure Data in SharePoint 2010 using BCS

Because SQL Azure uses a separate set of credentials than your Windows (and SharePoint) credentials, you can use Secure Store Service to manage the connection to the remote SQL Azure database.

Task 1 – Creating an Application ID in Secure Store Service

1. Open SharePoint Central Administration. Start > All Programs > Microsoft SharePoint 2010 Products > SharePoint Central Administration.

2. Navigate to the Secure Store Service and click a Secure Store Service instance. Manage Service Applications > Secure Store Service.

   Figure 8

   Secure Store Service

   Note:

   If there is no service instance, you may have to create one. To do this click the New button and then select Secure Store Service.

3. Create a new Application ID. Click New and then complete the first page of the wizard. You’ll need to fill in a Target Application ID (e.g. SQLAzureAppID), Display Name (e.g. Azure App ID), Contact Email (admin@acme.com), select a Target Application Type (you can leave it on Individual for this exercise), and then select a Page URL (leave the default selection Use Default Page selected). Click Next.

4. Configure the Application ID fields as per Figure 9. Click Next when done.  

   Figure 9

   New Application ID

5. Add administrators to the Application ID and click OK to complete the wizard. You have now created an Application ID.

Task 2 – Creating an External Content Type that Connects to SQL Azure

1. Open SharePoint Designer 2010. Open your SharePoint site and then click Site Actions > Edit in SharePoint Designer.
2. Create a new External Content Type. External Content Types > External Content Type.

3. Click the Name and Display Name fields to add a name (e.g. MyAzureECT) and leave the Namespace, Version and Identifiers with their defaults. Select the Contact option from the Office Item Type and leave the Offline Sync for external List as Enabled. To configure the external content type to load your SQL Azure data, click the Click here to discover… link.

   Figure 10

   New External Content Type

4. Click Add Connection to add a connection to your SQL Azure instance. Select the SQL Server option and click OK.

   Figure 11

   Adding a New Connection to the External Content Type

5. Add your SQL Azure database name in the Database Server field, the database name in the Database Name field, and add an optional Name parameter in the Name (optional) field. Select Connect with Impersonated Custom Identity and add the Application ID name you created earlier in the HOL.

   Figure 12

   Completing the External Content Type Connection using Impersonated Identity

6. Add your SQL Azure database name in the Database Server field, the database name in the Database Name field, and add an optional Name parameter in the Name (optional) field. Select Connect with Impersonated Custom Identity and add the Application ID name you created earlier in the HOL. Click OK.

7. After it’s connected, the connection will appear in the Data Source Explorer.

   Figure 13

   New SQL Azure Connection in Data Source Explorer

8. Right-click the new connection and select Create All Operations. This will create a full CRUD-enabled external content type.

   Figure 14

   Creating All Operations for the External Content Type

9. When prompted with the first page of the Operations wizard, click Next. In the Parameters Configuration view, map the StoreName to the Last Name Office Property. Leave the other defaults and click Next and then click Finish (don’t add any filters as there is not enough data).

   Figure 15

   Operations Wizard

10. Save the external content type. Click the Save icon in the upper-left hand part of the window.

    Figure 16

    Final External Content Type

11. Create a new external list using the new external content type. Click Create Lists and Forms and provide a List Name and click OK. Leave the other default options.

    Figure 17

    Creating External List

12. Navigate to the SharePoint list, and you’ll likely find that you do not yet have any permissions associated with the list.

    Figure 18

    Access Denied in External List

13. Add permissions to the external list. SharePoint Central Administration > Manage Service Applications > Business Data Connectivity Services. Click the new external content type you created and then select Set Object Permissions. Type the user’s alias into the Add field and then click Add. Associate the permissions you want with the newly added user.

    Figure 19

    Adding User to Permissions List for External List

When you return to the list, you will now find that the list can display without any security error.