External Content Types using SQL Server Authentication
Business Connectivity Services (BCS) is new in SharePoint 2010, which is used to be called Business Data Catalog (BDC) in SharePoint 2007. You can create External Content Type (ECT) with BCS that let you define a connection to a web service or database server that is external to SharePoint.
How to create External Content Type Based on a SQL Server 2008 Table:
I am going to show steps for a scenario where we will use single SQL account to impersonate when we access the external data from SharePoint.
Things to note before to implement scenario:
- Which SQL Server account needs to use for impersonation?
- Which group of users need to give access to the data? In my scenario I am going to use domain users group which is everyone. I will restrict use access at site level, if anybody will have access to SharePoint site they will have automatically access to external SharePoint list, which will be connected to SQL Server table
Create SQL Server account and give appropriate level of access to the database that will be used in SharePoint External Content Type (ECT).
Note: Whatever permission you will give to SQL Account, will be effective permissions of the group (domain user group in this scenario)
SQL Server 2008
Open SharePoint Designer 2010 and create External Content Type. Type in key information Name and Display Name, offline item type and select offline sync for external list.
Select external system by clicking on hyper link “click here to discover external data sources and define operations” This window will appear, here click on Add Connection button
It will ask you which type of data connection you want to create. There are three following types of data source type available, select SQL Server.
Then it will ask you information about your database server and database name.
In my case database server name is vmsql2008 and database name is live. For initially I will use option connection with user’s identity then we will change it later with impersonated custom identity.
Note: to choose option connect with user’s identity, you should have access to database.
After filling all above information click Ok. I got this error message after click ok button
Error Message: Access denied by Business Data Connectivity.
I checked database server to make sure I have access to database on this server, which I had sufficient access right. This issue is related to metadata store permissions on business data connectivity services. Current user, who is trying to create external content list does not have access to business data connectivity services.
To resolve this error message Open SharePoint 2010 Central Administration
Click on Application Management then click Manage Service applications under Service Applications.
From the following service applications select Business Data Connectivity.
Following window will open; here we need to set metadata store permission for the use who wants to create External Content Type.
Click on set Metadata store permission and add user. Here I am adding user name “uahmed” because this was id I was using to create ECT using SharePoint Designer.
Now if we go back where had error message “Access denied by Business Data Connectivity” and try to create ECT, we will not get this error message.
Now configure your data source, define external content type operations and save it. Name of my ECT is ECT Room List.
In SharePoint 2010 central administration, Click Application Management then under Service Applications selects Manage Service Applications. Then from service applications list select Business Data Connectivity.
Here you can see ECT you just created using SharePoint Designer 2010. Select ECT Room list and click on Set Object Permissions from bread crumb menu.
Give the appropriate group access, such as domain users = Edit, here I just gave access to myself with these permissions.
In SharePoint 2010 central administration, Click Application Management then under Service Applications selects Manage Service Applications. Then from service applications list select Secure Store Service.
If this is the first time that secure store has been used, you will need to generate a new key before the first secure store item can be created. In our case secure store key has been already generated.
Click the New button in the ribbon at the top left.
It will ask you to fill this information.
Target Application ID – Make something relevant to you database
Display Name – Can be same as the target application or you can choose whatever you want.
Contact Email – the email address of the target application’s primary contact person
Target Application Type – Select group from the drop down list. This is so that a whole group of domain users can impersonate the one SQL account. If individual was selected, it would be a one to one mapping.
For the first field name, call it SQL Account, and pick “User Name”. For the second field name, call it SQL password and pick “Password”. Click NEXT.
Enter target application administrators; this is the list of users who can manager setting of target application.
Members: This is the group that you want to be able to impersonate the SQL account. In our case we want to give access everybody in the company, so pickup domain users group and click ok.
Now our target application is appearing in the list with the name of misRooms. Select tick box next to it and click on set Credentials from drop down list.
Type the name of the SQL account that you want to use, and password click ok.
Open Content Type you just created in SharePoint Designer, click on Edit Connection Properties under external content types menu.
Select Authentication Mode to impersonate custom identity and Secure Store Application ID box, type ID that you created above.
Click and save the external content type.
Now, we can create external list from the external content type and all domain users will have access to it.