SarePoint developers are frequently asked to expose SQL data as a SharePoint list, but doing so in MOSS 2007 was a difficult chore. Using the old Business Data Catalog (BDC) required the tedious creation of XML files and cumbersome work in Central Admin. Fortunately, SharePoint 2010’s Business Connectivity Services (BCS) has made the process almost painless. This article gives you step-by-step instructions for creating, reading, updating and deleting SQL table rows using a SharePoint external list.
Suppose you want to display and edit this SQL table’s data as a SharePoint list.
First, we need to set up an Active Directory user and group that will handle the permissions for connecting to the SQL database. Let’s create an AD service account with a non-expiring password that cannot be changed. Then we’ll create an AD group whose members are the new service account we just created and the other users and groups who are to be granted access to the SQL table through the SP list. BCSUser is the service account and BCSUsers, with an “s”, is the group.
We also need to give the service account permissions to the SQL database.
Next, open central admin and make sure the Business Connectivity Service is started.
Now we’ll create a Secure Store Target Application so we can map the members of the BCSUsers group to the credentials of the BCSUser whenever they access the SQL table through our soon to be created external list. Go to Central Admin -> Manage Service Applications -> Secure Store Service -> New
Give your SSTA an ID, display name and a contact email address. Since we want to map an AD group to the BCSUser, select Group as the Target Application Type. Click Next.
The next page allows you to configure the fields that may be required when supplying credentials to the external data source. We’ll just pass through the user’s Window’s credentials so the defaults are okay. Click Next.
Now enter at least one Target Application Administrator and the AD group we want to map to the BCSUser credentials.
Next, we need to tell the SSTA which user’s credentials we are going to use for mapping. Choose Set Credentials from the Target Application’s drop down.
Enter the BCSUser’s credentials and click OK.
We’ve completed our preliminary work in Central Admin and will now use SharePoint Designer to create an External Content Type. (You can also use Visual Studio to create External Content Types)
Open your target site in SP designer and create a new External Content Type.
Name your External Content Type and then “Click to discover external data sources and define operations”.
Add a SQL Server type connection.
Specify the SQL Server and the database name. Select “Connect with Impersonated Windows Identity” and enter the name of your SSTA.
Enter the BCSUser credentials to connect.
The database is now shown in an explorer view.
Right click on the table we want to use as a list and select “Create All Operations”. This will add all operations required to create, read, update and delete items from our list.
The only configuration you must do is to specify a unique identifier. The ID field of our SQL table will work nicely. Check the box to map it to the identifier and then click Finish.
Save the External Content Type.
Now we need to set or confirm permissions for the new External Content type in Central Admin. Go to Manage Service Applications -> Business Data Connectivity Service and select Set Permissions from your new content type’s drop down.
All authenticated users are given the minimum permissions for using the new content type. At least one user is required to have all permissions. Here, I’ve set that for the administrator. (not shown)
Finally, we can create an External List based on the new External Content Type.
We have our External List! Let’s try it out.
Confirm that the list is editable.
Try logging on as different users from the BCSUsers group to check their ability to see the list.
Now try a user that is not in the BCSUsers group.
In this post, we’ve used the most common BCS method for surfacing a SQL table in SharePoint 2010. As you probably gathered from working through these steps, SP 2010 gives us several options for authenticating users, passing credentials and connecting to a SQL database. I hope this example helps you to further explore the BCS and find the configuration that works best in your environment.