Using Single Signon with Database Credentials

This one was a real puzzle. I was able to get a BDC application working using RevertToSelf credentials but wanted to use a secure database account so that thousands of users didn’t have to have accounts in SharePoint. Instead, all domain users would access the BDC LOB using a dedicated SQL Server account.

Obviously the Single Signon was required but the SDK documentation and samples are, how to put it – a little vague? Not much information is available on the MegaHyperIntraWeb either. Eventually I got it working with some help from a fellow programmer, Christopher Bowman. Hopefully these steps will help others. Please send me some feedback on your experiences with this!

1. First, make sure you can connect to your BDC Line-Of-Business app using PassThrough or RevertToSelf authentication first, even if it’s to one column from a single table, just to be sure that your BDC metadata is correct.

2. Enable the Single Sign on service. Go to Services and set Microsoft Single Signon to start automatically. Give it a domain account as an identity.

3. Go to Operations in the SharePoint Configuration site. Click “Manage Single Signon” and sign in using the identity account in step 2.

4. Click on “Manage Server Settings” to create a new SSO database.

5. Click on “Manage settings for enterprise application definitions” to create a new Single Sign On application. The name you choose will be referred to later in the BDC application definition. For Account Type, choose “Group” so that all users will log in using the same database user id and password. Don’t select the “Use Windows Authentication” checkbox. The fields you fill out here are the credentials you need to pass to your application. In this case we are trying to authenticate to our LOB application’s SQL Server database using the connection string, so “Field 1” is called “User ID” and “Field 2” will be “Password”.

6. Still in the “Manage Single Signon” section, go to “Manage Account Information for enterprise application definitions” and select the application you just created. Put “\Domain Users” as the group that will try to authenticate using SSO. Finally, fill out the User ID and Password that you will connect to the LOB database with. Make sure this account exists in the LOB SQL Server database and has rights to the particular LOB db. I think it only needs Public rights but if you are connecting to stored procs using the BDC you may need execute permissions. If you get stuck try making this account a db owner of the LOB database.

7. Now it’s time to change your BDC application definition to use SSO and database credentials. Here are the settings you need: <LobSystemInstance Name="LOB_INSTANCE_NAME" > <Properties> <Property Name="AuthenticationMode" Type="System.String">RdbCredentials</Property> <Property Name="DatabaseAccessProvider" Type="System.String">SqlServer</Property> <Property Name="RdbConnection Data Source" Type="System.String">Name of your db server</Property> <Property Name="RdbConnection Initial Catalog" Type="System.String">Name of your LOB database</Property> <Property Name="RdbConnection Integrated Security" Type="System.String">false</Property> <Property Name="RdbConnection Pooling" Type="System.String">true</Property> <Property Name="SsoApplicationId" Type="System.String">This is the name of the SSO Application you created in step 5</Property> <Property Name="SsoProviderImplementation" Type="System.String">Microsoft.SharePoint.Portal.SingleSignon.SpsSsoProvider, Microsoft.SharePoint.Portal.SingleSignon, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c</Property> </Properties> </LobSystemInstance>

There are a couple of gotchas here: Note that the type name and namespace of the SsoProvider is Microsoft.SharePoint.Portal.SingleSignon ... all the examples show Microsoft.SharePoint.Portal.SingleSignOn with a capital “O” but this is incorrect and you will receive an error about type name not being found if you don’t change this. The other gotcha is to disable SSPI. I do this explicitly here by setting “RdbConnection Integrated Security” to false but by default it is false anyway.

8. Upload the modified BDC application definition, remembering to increment the version number first.

9. Manage the permissions of your BDC Application, giving the “Domain Users” group “Execute” permission to the BDC. You may have to add the group first.

10. Manage the permissions of each your BDC Application’s entities, making sure that the “Domain Users” group is added and has “Execute” permissions here as well.

11. If you are already searching on your BDC Application, do a full crawl of that content source again so it updates the security permissions to prevent security trimming.

12. To test, try viewing a BDC web part using any account that does not specifically exist in the LOB SQL Server Database...if you can still view the data, the Single Signon worked!