SharePoint 2010 : Security - Secure Store Service & Using SSS with BCS

1. Secure Store Service

In SharePoint 2007 there used to be a service called a single sign on. Single sign on wasn't exactly like single sign on as you would consider it in the case of live ID or kerberos tickets. Instead of being a ticket based single sign on, it was more like a credential policeman. In other words, the application would supply an application ID and the SSO service would give you with the credentials that you would need to log into the backend system. Other than the fact that this SSO feature was misnamed, there was another big problem with SharePoint 2007's SSO feature. The problem was that it would rely on the office single sign on service which would rely on the windows single sign on service, so configuring SSO on a server required you to have many accounts with a lot of elevated privileges. Many organizations would have a problem setting up so many accounts with so many elevated privileges. To get around all these issues, you did have the ability to implement a custom SSO provider and register it with SharePoint. This technique is demonstrated in this article http://blah.winsmarts.com/2007-8-Using_BDC_with_RdbCredentials_-_when_you_have_no_Domain.aspx. You can still implement custom SSO providers, but hopefully the need to do so will reduce a lot with the introduction of the secure store service.

In SharePoint 2010, the SSO service is being replaced with a secure store service (SSS). It is a shared service that provides the storage and mapping of credentials such as account names and passwords to individual applications. It allows you to securely store this data in an encrypted database and these credentials can then be queried for at runtime by running applications.

For example, if you are trying to create a Visio visualization that talks to a SQL server database, you could use the identity of the logged in user and pass that identity all the way back to the backend system. While that approach will work, it will require you to manage permissions at the backend system level, and it will not allow you to use connection pooling effectively. Therefore, using the SSS Service, you can provide credential mapping on a per user, per application basis.

Before you can start using the SSS Service, you need to configure it inside central administration first. Therefore, visit central administration and under the application management section click manage service applications. If you haven't already provisioned an instance of the secure store service, provision it using the new button in the ribbon. If you have provisioned a secure store service, select it and choose manage from the ribbon. The first thing you will need to do in a newly provisioned SSS Service instance is to generate a new key. This key will be used to encrypt the backend database that will store all the credentials. So, pick a key that you're not going to forget. Once you have generated the key, you can then click the new button to create a new target application. Before you create a new target application, let me explain a practical scenario that you will use here.

2. Using SSS with BCS

Business connectivity services allow you to query an external system and bring that data into SharePoint as an external content type. This external content type can then be used as external content lists. The external content type that I had created included the authentication information that SharePoint would use to query the backend system.

• If the user's identity is used to query the backend system, this means that you're adding your headache of managing user identities in every backend system.

• You're also defeating the purpose of connection pooling because a new pool will be set up for every user identity that SharePoint runs across. These connection pools will be maintained on the server so the effects are greatly accentuated.

• Perhaps a bigger problem is the double hop issue. Your identity will be sent to the IIS server that is running SharePoint. Since SharePoint doesn't really have your password, it only has your identity, there will be a problem sending that identity to a backend system. Classically, this problem has been sorted out by enabling and configuring kerberos. Kerberos, however, is a beast that is difficult to configure, and it will work only with windows identities. Ideally, you should be able to send a claims based identity all the way to the backend system and you should be able to solve the multiple hop issue with such a claims based identity. Of course beyond two hops, you also have to consider if back-end systems accept a claims based identity.

The secure store service solves all of the preceding problems. Before you dive into creating a new target application in the secure store service, first create an external content type that talks to the northwind database and brings the customers table into a SharePoint external list. When asked for the connection details, ensure that you intend to connect using the user's identity to the backend system. This can be seen in Figure 1.

Figure 1. Creating a new BCS External Content Type and specifying authentication

If you are unclear on exactly how to set up this external content type. The steps are exactly the same here, so I will skip mentioning them. Once you have set up this external content type, create an external list called customers that uses this external content type. When logged in as administrator, and assuming the administrator has access to the backend northwind database, you would see that SharePoint is able to bring external data into the external list, as shown in Figure 2.

Figure 2. The External List based on the external content type

Now in the same site, add two new users called "John Doe" and "Jane Doe". You will have to create these users in your active directory first. Both of these users are restricted users in that they do not have access to the backend SQL Server system. When logged in as John Doe into your SharePoint site, try accessing the customers list again. Note that John Doe is unable to query data from the backend system. This can be seen in Figure 3.

Figure 3. BDC denied you access.

Let's try and diagnose what's going on here. Start SQL server profiler and begin by looking at failed login attempts to the northwind database. At this point, you would note that John Doe's request is not even making it to the backend database. This makes sense because the BCS external content type you have created hasn't yet given John Doe permission to use that content type. Back in central administration, under manage service applications, click manage for the business connectivity services application associated with your web application. Select the customer's external content type, and click set object permissions button from the ribbon. Ensure that you give John Doe and Jane Doe appropriate rights to this external content type. This can be seen in Figure 4.

Figure 4. Fixing access rights on the External Content Type

Now that you have given access to the external content type, you need to make some changes to your external content type. The changes are twofold.

• You need to configure your external content type to use the SSS Service.

• In the SSS Service, you need to create a new target application ID.

Back inside SharePoint designer, open the Northwind Customers external content type, and click the "External System" link. Ensure that your external system now chooses to use an authentication mode that "Impersonates custom Identity", and it uses a secure store application ID of Northwind. This can be seen in Figure 5.

Figure 5. Connection properties for the external content type

Now that you have told your external content type to use a secure store application ID of Northwind, inside of the secure store service itself you need to create a new target Application ID with the same id of "Northwind". Back in central administration, under manage service applications choose to manage the secure store service and create a new target application ID. Fill in the details as shown in Figure 6.

Figure 6. Target application settings in the SSS

Once you have specified the basic details of your target application ID, you need to specify what information this target application ID chooses to store. In this case, you intend to store a SQL server username and password, so go ahead and specify the details, as shown in Figure 7.

Figure 7. Specifying the structure of your credentials in the SSS

In the final screen of creating a target application ID, you need to specify the application administrators for this target application. Choose to specify the administrator as the target application administrator, as shown in Figure 8.

Figure 8. Specifying the target application administrators

Your target application is all set! Now you need to add some credentials into it.

You might be wondering why I created two users John Doe and Jane Doe? I did that to illustrate an important difference which you're getting into next. Once you have created the target application ID of Northwind, you need to set credentials inside this target application ID. You can do so by selecting the appropriate target application ID, and then choosing to set credentials either by accessing it by the ECB menu or through the set credentials button in the ribbon. This can be seen in Figure 9.

Figure 9. The Set Credentials Button on the ribbon

Here you should set credentials only for John Doe. In other words, when John Doe tries to access the customers list and, therefore, the external content type, his request will automatically be mapped to a SQL server username and password. Specify new credentials for John Doe, as shown in Figure 10.

Figure 10. Specifying credentials for John Doe

As far as Jane Doe goes, do not specify any credentials for her. Back in your SharePoint site, while logged in as John Doe, access the customers external list. You would note that John Doe is now able to seamlessly access the external list. If you were to run SQL profiler to check and see who exactly is accessing the backend list, you would note that his requests are being converted to the "sa" account, as shown in Figure 11.

Figure 11. SQL Server audit log. The sa account is making the request.

As you may have guessed, the SharePoint BCS infrastructure has queried the northwind target application ID in the secure store service, and it is using the credentials supplied from the secure store service to authenticate to the backend system.

John Doe was lucky because you went ahead of time and set credential mapping for him. What happens if users who don't have set credentials try accessing this external list? Such a user would be poor and neglected Jane Doe. Try accessing the same external list while logged in as Jane Doe. SharePoint will now present you with a user interface, as shown in Figure 12.

Figure 12. Jane Doe being prompted to enter credentials

SharePoint was smart enough to understand that this external content type relies on a target application ID called Northwind. However, credential mapping has not been set in advance for Jane Doe, but since the target application ID and the structure of credentials has already been set, SharePoint will present you with a user interface to try and authenticate you to the backend system. Clicking that link will present you with the user interface, which will prompt you to enter a SQL username and password. This can be seen in Figure 13.

Figure 13. Jane Doe entering her credentials.

Supplying the correct username and password will now allow Jane Doe to access the backend system as well.