Secure Store Service and Business Connectivity Service deep dive

Well it’s a quite a long time since I have blogged on some particular aspect of SharePoint. Recently I was using Secure Store Service with the purpose to connect with LOB application through Business Connectivity Service and finds out some great functionality of these two services.

If you guys don’t know about Secure Store Service, Following is the some excerpts from http://msdn.microsoft.com/en-us/library/ie/ee557754.aspx

The Secure Store Service replaces the Microsoft Office SharePoint Server 2007 Single Sign On feature. Secure Store Service is a shared service that provides storage and mapping of credentials such as account names and passwords. It enables you to securely store data that provides credentials required for connecting to external systems and associating those credentials to a specific identity or group of identities. 

And for Business Connectivity Service, here are some excerpts from

http://msdn.microsoft.com/en-us/library/ee534979.aspx

Microsoft Business Connectivity Services (BCS), formerly named the Business Data Catalog, provides read/write access to external data from line-of-business (LOB) systems, Web services, databases, and other external systems within Microsoft SharePoint 2010.

There are some interesting articles contributed by the SharePoint Gurus on this regard. Following are the some of the articles.

http://blogs.msdn.com/b/sridhara/archive/2010/01/27/setting-up-bcs-with-secure-store-application-impersonation.aspx

http://fabiangwilliams.wordpress.com/2010/04/16/how-to-create-configure-consume-sharepoint-2010-secure-store-in-business-connectivity-services/

So what’s the purpose of writing another? Well I want to explore its strongest points and how logical architecture of these services work together.

Take a simple Scenario from our legacy applications

There are whole set of the users who need to access the data but they don’t have the access on it, in legacy application we pass connection string containing the username and password of authorize user to use the data.

	User / Group (KalsoftAdmin)	User / Group (BCSAccess)
Access on SQL server database	Yes	No
Credential Owner	No	Yes
Impersonated User	Yes	No
Access on SharePoint	Yes	Yes

This means that I  access SharePoint as a user from BCSAccess group, but don’t have the rights to access SQL Server database, so I use KalsoftAdmin credentials to access the database.

Following are the major steps.

• Setup users / groups in Active Directory
• Set user permission on SQL Server Database
• Configure Target Application ID in Secure Store Service
• Configure External Content Type for using SQL Server Database
• Set Permission on External Content Type for the user accessing SharePoint
• Create List and Validate

Setup users / groups in Active Directory

To do this I got two users and one group in my Active Directory.

Users

1. Kalsoft Admin
2. Shakir.majeed

Group

1. BCSAccess

BCSAccess has only one member that is shakir.majeed (Note that KalsoftAdmin isn’t there in the list)

Set user permission on SQL Server Database

I have a database named as RockShare in which I have one table named as Sale, following are four  fields in it

1. Amount
2. Resource
3. Date
4. ID (Primary Key)

(It is better to create this table so that you can follow the steps properly). Also I have given access to KalsoftAdmin user as a DBOwner.

Configure Target Application ID in Secure Store Service

In order to configure Target Application ID we need to first configure Secure Store Service,  to do so follow these step.

1. Go to Central Admin–>System Setting–> Services on the Server, verify that Secure Store Service is running, if not click on the start link
2. Go to Central Admin–>Application Management –> Manage Service Application
3. I am assuming that you don’t have Secure Store Service configured till yet. If No then skip the next 3 steps
4. In Manage Service Application Click onto the New button in the Ribbon under the heading create
5. Enter the name of the Service, Database server, Database name to create secure store service dataset (don’t mingle it with the Database want to access, this database will only keep the credentials, that’s it), application pool name if you want configure new app pool for it and finally if you want to Enable Audit, select the Check Box. And Click OK.
6. Attach this new service application, to do this, go to Central Admin–>Application Management –>Manage Web Application –> Select the Web Application to which to you want to connect this new Service Application,  and on the ribbon click the Service Connections,  This will hover new window, from here verify that your new service application already attach, if not chose custom from the dropdownlist and select all of the service application you want to attach and click OK. In my case it’s already attached.
7. Now go to Central Admin–>Application Management –> Manage Service Application click on the New Service Application.
8. To use this Secure Store Service Application, you have to generate the key. Click on to Generate New Key button in the Ribbon.
9. This will take you to new hovered page section, you need to provide Pass Phrase, based on that Secure Store Service will create encrypted key. And click OK.
10. Now Click New to create Secure Store Target Application.
11. Now fill out the form as per the description stated in the page
    1. Target Application ID ( This is the unique Identifier)
    2. Display name
    3. Contact Email
    4. Target Application Type (Choose Group from Drop Down List)
    5. Choose none from Target Application Page URL
    6. Click Next
12. As I will be using Windows account to access the database, so don’t need to add/ edit the field from this page. Click Next.
13. Now provide the administrator name in the Target Application administrator’s user picker. Plus provide Group who will access the site, in our case it is BcsAccess in the Members user picker. And Click Ok. This will create the Secure Store Target Application.
14. Now I have to set the credentials on this Target Application, Select the Target Application and click on the Set.
15. Now provide username (Domain\username), password and confirm password for the user who have the access on the database.  And click OK
16. This will end our configuration of Secure Store Target Application settings.

Configure External Content Type for using SQL Server Database

1. Open Sharepoint Designer, open your SharePoint Site, Navigate to External Content Type.
2. Click in External Content Type New button in the Ribbon
3. Provide the name for the External Content Type. Under the External Content Type Operations  And Click on to the “Click here to discover the external data sources and define operations”.
4. Click on Add Connection, Chose SQL Server from External Data Type Connection-Data Source.
5.  Provide the Database Server, Database Name, chose Connect with Windows Identity (this will enable Secure Store Application ID text box.
6. It will then ask the credentials you have entered in Secure Store Target Application. Provide the credentials and Click OK.
7. Once the connection completely formed, a database source appears, navigate to this data source to find the desired Table. Right Click on the table and from the list select Create All Operations.
8. This will open the configuration window. Click next to go to next step.
9. Keep Selected all the Fields, Click on to ID, Select  Map to Identifier in the Check Box .
10. From the Filter Parameters, without doing anything here, click Next
11. Click Finish to complete operation configuration and Press CTL+S to save this External Content Type.

Set Permission on External Content Type for the user accessing SharePoint

Now I have to provide the access to BcsAccess on ECT,

1. Go to Central Admin–>Application Management –> Manage Service Application–> Click on the Business Data Connectivity Service.
2. Select the External Content Type you have earlier from the SharePoint Designer, In my case it is “DemoECT”. And Click on the Object Permission in the Ribbon.
3. Here you will give provide the name of the user or group who will use this ECT, in my case it is BCSAccess. Select all the options from the permission and click Ok

 

Create List and Validate

Now it is time to create the list and validate our scenario

1. Navigate to the SharePoint Site in the browser, Click Site Action–> View all site content from the ribbon.
2. Click Create. Chose External List Template, Click Create.
3. Choose the External content type from the picker, enter name of the List and Click OK
4. Now To validate the data, lets sign in from shakir.majeed, and wow I can see the data
5. Too see if I can get the data from KalsoftAdmin Account, let me login from that account.

As you can see that even though Kalsoft Admin account is the DBowner of that Database, it cannot access the data, because the ECT is configure only to BCSAccess group and KalsoftAdmin is not the part of the group. More over SSC provides KalsoftAdmin credentials only when BCSAccess users try to access the List from SharePoint.  Fantastic! J

Do give your feedback,

have a nice day,

Keep SharePointing.