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..
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.
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
Return to SharePoint Designer again. If you click ok on the error box you need to add the connection again. (see previous steps)
After clicking finish don’t forget to click Save button!
No we can go and add a list to the SharePoint BCSDemo site.
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
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.
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.
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 .
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.
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.