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
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
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
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.
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.
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.