Feb

How to create External Content Type Based on a SQL Server 2008

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

Step 1:

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

Step 2:

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.

ECT SP 2010 Designer

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

External Content Types

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.

External Data Source

Then it will ask you information about your database server and database name.

SQL Server Connection

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.

BDC Error Access Denied

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

SP 2010 Central Admin

Click on Application Management then click Manage Service applications under Service Applications.

SP2010 Application Management

From the following service applications select Business Data Connectivity.

SP2010 Manage Service Application

Following window will open; here we need to set metadata store permission for the use who wants to create External Content Type.

View External Content Types

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.

Metadata Store Permissions

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.

SP 2010 Designer ECT List

Step 3:

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.

View External Content Types

Give the appropriate group access, such as domain users = Edit, here I just gave access to myself with these permissions.

Permissions

Step 4:

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.

Secure Store Application

Click the New button in the ribbon at the top left.

Secure Service Target Application

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.

Click Next

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.

SQL Account Setting

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.

Edit Secure Store Target App

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.