SharePoint 2010 SQL Server External Content Types

I needed to connect from SharePoint 2010 to a SQL server using a static username and password and have this data presented in a list on SharePoint. In this post I outline what I did. The general steps are:

1. Create a Secure Service Account that can be used to access the database using one account regardless of who is on SharePoint. This is done using the Central Administration Console.
2. Create an External Content Type using SharePoint Designer that connects to the SQL Server.
3. Create a List in SharePoint (or SharePoint Designer) that looks over this External Content Type.

Creating a Secure Service Account:
This is the account that will be used to connect from SharePoint to SQL server without the need to have an account on SQL Server for every user. 

• The first thing to do is to create an AD (Active Directory) user that will be used to access the database. Give it a username and a password. E.g. SPSQLDB
• Create a security group in AD that will house the accounts that will be able to gain access to our SQL Server. Make sure the account from step one (SPSQLDB) is a member of this group.
• Open up the SharePoint 2010 Central Administration and click on 'Application Management' down the left hand side. 
• Click on 'Manage Service Applications' under 'Service Applications'.
• Click on the 'Secure Store Service' to bring its interface up.
• Click 'New' 

•  In the fields of 'Target Application ID', 'Display Name' and so on, put in the details that you want. At this stage they do not have to match the accounts created above. 
• I selected 'Group' in the Target Application Type as this will let me access one set of login details from an entire AD group. Then 'Next'.
• I left the field names as default and made sure that the field type was set to Windows User Name and Windows Password. Then 'Next'.
• In the 'Target Application Administrators' I add in the accounts that I will allow to edit the Target Application settings. (e.g. all of the users who have access to Central Admin and who I don't mind playing around with this)
• In the Members field I add in all accounts/groups that I want to have access to run the link between SharePoint and SQl server. In my example I want only a certain group of users to access it. (If you don't get this right then when the web part is added an error will occur with the web part itself and the users won't be able to see the data.)
• Click OK and we have created our SSC

So, now we have an SSC account, but, we need to add in the user that we will be access the SQL Server with.

• Click on the new SSC that we have created. 
• Click on 'Set Credentials'.
• In this dialogue box that appears we want to put the UserName and Password from our user we created all the way at the beginning of this step SPSQLDB. Make sure that the domain is added in the username. E.g. domain\username

Creating an External Content Type
I will use SharePoint Designer to create an External Content Type that will connect to the SQL Server.

• Open up SharePoint Designer at the root level and click on 'External Content Types' on the left.
• Click on 'Add Connection' and select 'Sql Server' as the data source.

• In the window above put in the database server, name and then click 'Connect with Impersonated Windows Identity' and put in the SSA ID from the one we just created. Again adding in the domain when asked for username and password.
• Note: You will probably need to play with permissions and accounts access on the SQL Server for this account to gain access. 
• If successful you will end up with a list of database tables. 
• Pick a table you want the List or content to be over and right click on it:

The Database Table Options
The operations are defined as:

• New Read Item Operation: This will return a single item (database row) from the database based on some parameter (e.g. ID).
• New Read List Operation: This will return the entire table as a list.
• New Create Operation: This will allow for new rows to be created in the database. 
• New Update Operation: This will allow for a row to be updated in the database. 
• New Delete Operation: This will allow for a row to be deleted from the database.
• New Association: An Association is the ability to link two content types togethor. Think of it as linking tables in an SQL JOIN query.

I am going to be creating a 'Read Item Operation' and a 'Read List Operation'. I  do not want to change anything on the database itself.

New Read Item Operation

• Right click on the table name and click 'New Read Item Operation'
• In the dialogue box/wizard that appears accept the default names or change them and click next.
• In the Input Parameters Configuration select the identifier that we will be using to query the database. A default value for the identifier can be selected and a filter for the data can be set. (This filter will filter the results based on other parameters).
• I for example am choosing a 'StudentID' field as my identifier.

•    In the next screen we need to map the return parameters. I left them as defaults. However I did get some warnings:

• This is to do with the items being both required and read-only. I unselected the 'required' field. We will never be updating from this list and so we know that the element in question will come back from the database if it is there. 
• Click on Finish.

New Read List Operation

• Edit the display names as desired. 
• Add any filters that you want (Sorry, I was going to walk through the filters and how to, but I realised this blog post would get huge.). 
• At least add a limit:
• Click 'Add Filter Parameter'
• Click Filter: And add the filter type of limit
• Choose the data source element this is based over. (Primary Key is probably better)
• Add in a Default Value.
• Add the return parameters.
• Select some columns to add in as the picker columns.

NOTE: If you do not add in a limit that is below 2000 then there is a good chance you will get an error when you add the web part that displays an external list. This is because by default SharePoint is set to 2000 limit on external results. 

Once you are happy with everything click 'Save'.

Using the External Content Type
From within the SharePoint Central Administration portal click on 'Manage Service Applications'. Then go to 'BDC Service Applications'. Here you will find the service that we created.

We want to let certain (or all) people have access to the service and therefore access to the link between our SharePoint and the SQL server. This will let them view data returned from the SQL server.

I am going to let all_staff have execute permissions and then a specific group of people have set permissions and edit rights to the link.

Back in SharePoint add an External List and select the External List we created in the options:

• Site Actions
• More Options
• Filter By: List
• External List
• Add in a Name
• Select the External List we created. 

 Fingers crossed that it works.

If it didn't work:
Ok, so hopefully it has worked. If it hasn't worked then hopefully you will have gotten some error code (correlation ID). Below are some links to do with checking the logs and what could possibly have gone wrong:

• Finding the error logs: http://social.msdn.microsoft.com/Forums/en/sharepoint2010general/thread/d42b11cb-54c1-4c45-bfba-acf3fd486717
• About limit throttling: http://social.msdn.microsoft.com/Forums/en-US/sharepoint2010general/thread/8a861878-180f-4126-b982-574ea514676c 
• Access denied error: http://social.msdn.microsoft.com/Forums/en-IE/sharepoint2010general/thread/7e597ef9-0f8f-4df8-a99c-1c66c536d717