The first questions you should ask yourself is how do you want to access the data in SQL Server and what accounts you want to get the data with. Most examples you can find will use Pass through, so essentially you are passing the logged-on user’s credentials to SQL Server. This is a problem when you have thousands of users. Do you really want to give customers direct access to the database? Ok, what are our other options then?
We could use Revert To Self. This means that we would use the identity of the application pool to get our data. This is a viable option if we treat all users to our application the same. Unfortunately my client wanted customers to be able to perform CRUD operations on their own data, but no one else. If we used Revert To Self, the database would not know if the current request is for a user that should or should not be able to update the requested information. So that leaves us with one final option; Impersonation.
Impersonation is implemented using the Secure Store Service (SSS) in SharePoint 2010. The idea is that the SSS will detect the current logged-on user’s identity and based on permission rules that we create in the Secure Store, the request to SQL Server will be permitted or denied. If permitted, the SSS will use an impersonated identity defined in the Secure Store to make the request to SQL Server. This approach is ideal if you are going to deploy the solution to multiple environments as users and users’ permissions could be different between the environments and it pushes security as a configuration step, abstracting it from the solution itself. Another benefit to this approach is that the client wanted to authenticate customers via Claims authentication, but wanted their staff to login using AD. The SSS allows us to use Claims groups, as well as AD groups, and give us the capability to assign Claims users permissions to the database that are different from the AD users permissions.
This is the approach we took for authenticating to the SQL Server and I will go into further detail about this in a future blog. For now, you can find more information on Authenticating to Your External System on the BCS Team Blog.