SharePoint 2010 using BCS with SQL Server database

SharePoint BCS (Business Connectivity Services) can be used to display information from you business applications in a SharePoint environment.

In this blog I try to explain how to use BCS to get data from a SQL server database.

Above image is a simple view of the environment I’m using.

• SHP01 = SharePoint 2010 Web & Application server using NTLM security
• SQL01 = SQL 2008R2 database server used to store the SharePoint databases
• SQL02 = SQL 2008R2 database server used to create the BCS test data
• PC01 = Regular Windows 7 client environment to be used

I created a site collection on SHP01 http://demosites.di.local/sites/BCSDemo using the blank site template

Domain user DI\Farm1Admin1 is the site collection administrator.

I created a domain user DI\adBCS_Reader that is used to create a BCS list based on Windows Authenticed secure store.

On SQL02 a demo database dbDemoBCS was created using the script you can download [here]. It contains a table tblDemoBCS that now has 1999 rows, why 1999? I will get to that later in this blog.

The user DI\adBCS_Reader is added to the database security with db_datareader rights. A SQL Login sqlBCS_Reader is created and also added to the database with db_datareader rights.

I think we are ready so lets get started.

BCS with SQL and Windows Authentication

First thing we need to do is create the secure store target application, we need central administration for this.

CA > Application Management > Manage Service Applications > Secure Store Service Application > Click on New (Click image to view configured values)

The secure store application is now created and we need to set the credentials

We are now ready to create a BCS Application using the SQL02 database.

We need SharePoint designer to do this, I use the PC01 client with the DI\farm1admin Login.

First open the site in SharePoint designer… and then follow the images..

Change Name of the BCS Application and start creating the data source

Now we need to give the SQL connection information, the Name setting is optional but to make sure that we can set the correct security later when we add the SQL based BCS I will give it a name. This I recommend to do when you create several BCS applications to the same database using different credentials, also use different connections.

After clicking OK we need to verify the security and need to login with the credential that is registered as credential for the Secure Store Application ID.

Now we can run into the first error you can get when using BCS:

I use the account DI\Farm1Admin1 as the site collection administrator an I logged in with this account on PC01 for using SharePoint designer. This means that when adding a BCS application (External Content Type) what we are doing you need to have privileges in the BCS service to do this.

CA > Application Management > Manage Service Applications > Manage the BCS Service Application

No add the Farm1Admin1 account with all possible priveleges:

Return to SharePoint Designer again. If you click ok on the error box you need to add the connection again. (see previous steps)

Now the connection is added, expand the database to view the tables, right click the table and select create all operations…

After clicking finish don’t forget to click Save button!

No we can go and add a list to the SharePoint BCSDemo site.

The list is created and data is displayed..

Now let’s see what happens when we switch user on the site, I use DI\Arjan who is member of the BCSDemo owners for the site.

This one we no already, lets get to CA > Application Management > Manage Service Applications > Manage the BCS Service Application. This time click the just created BCS application and select Set Permissions

I add the DI\Domain Users of course you could select a special group or only users that need to use this application. This time only execute will be enough.

A different error is displayed.

Why is this error displayed? It’s not really clear from the message. But I do have a solution that solved this so lets implement.

Remember that we created the secure store application? At some point we had to define some members that where allowed to impersonate as the given security privileges. We need to add the users that are going to use this application to this same members setting.

CA > Application Management > Secure Store Service Application > Manage

You can get to the members setting only by editing the secure store application. Just continue all steps until you get to where you can add members and add your desired privileges. I added domain users to keep it simple.

Return back to the site with the user with less privileges as the site collection administrator (in my case DI\Arjan), is the list displayed???

Yes it is!

BCS with SQL and SQL Authentication

In this case I want to use the SQL Authentication, most of the steps are the same as above and will skip some screens to not make this a massive blog .

First create the secure store application, to not error I setting all corrections done above immediately.

No differences yet only in the name..

Here is a part of the difference between windows authentication.

This time I added domain users immediately, I now my errors already .

This time we entered the SQL Login as credential.

We can switch to SharePoint Designer and add a new BCS application.

The previous connection is still there, we need to add a new one to make it work with the new secure store application.

This time we verify the SQL Login to have access to the database.

Now we create all operations again, see previous steps to display how. But end up with a screen like below and don’t forget to click the save button!

Go to CA to set permissions for the created BCS Application.

Remember this from previous given error solution..

Now return to the demo site and add a list based on the SQL BCS Application, I hope you remember how other wise scroll back .

Is the list showing?


Does it also show when using a other login on the site?

One more time displaying the error:

Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Microsoft SharePoint Foundation-compatible HTML editor such as Microsoft SharePoint Designer. If the problem persists, contact your Web server administrator.

Correlation ID:b6729321-564e-4a39-a47a-c621db88894e

Why? It has to do with the number of records in the table, soon I will update for now the sun is shining.

Hope you enjoy this blog! When you have comments, better solution and recommendation I like to hear from you.