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!

Data Source Agnosticism using the Business Data Catalog

There are a couple of main connection options that are available when using the Business Data Catalog (BDC) to access your non-SharePoint applications: a direct connection to databases, or to web services. The latter option is intriguing because we can couple it with some new .NET 2.0 functionality to add some real flexibility to our system.

While it may be tempting to hook the BDC straight up to a database, using a web service provides us with far more options. To begin with, the web service can just be populated with dummy data to get it up and running. Doing the same thing with a database would involve creating lots of tables and columns, making sure the contraints and other database objects are appropriate and functional, and then populating the dummy data either by hand (a potentially painful task) or by some code (which may or may not exist). Even if the database is already set up, connecting to the web service provides us with far more flexibility and even “future-proofing”.

For one thing, the web service guarantees that as long as future versions of the BDC support SOAP, they can continue to connect for years to come. This may or may not be true with databases (although it is hard to imagine SQL Server support being dropped!). The web service also allows us to provide disconnected data and can even mash together other data sources before feeding the BDC.

Finally, using a web service introduces a potentially huge flexibility option. I’m talking about using the new Provider model to populate the web service which the BDC will call. What are some of the advantages of this, and how would you do it?

The obvious advantage is that while you currently support Oracle or SQL Server, you may wish to provide yourself with the flexibility of using an alternative data source. Data Source Agnosticism, while a noble goal, has always been problematic due to the complexity of writing and maintaining the code. New to .NET 2.0, the Provider model is essentially a combination of Abstract Factory pattern with an XML configuration file. The burden of writing agnostic code is vastly reduced. After your first attempt to create a custom provider, the number of places you will want to use Data Agnostic Code will blossom!

To begin with, creating a custom provider is not a lot more work than usual. You need to create a web application that will expose the web service. The code of the web service will call a custom provider that will return data in some format. The web service is likely to just expose this directly through its method stub, although it could do various interesting things, such as validation, caching, and even mashing up a variety of data sources into one resulting data set. The BDC will then call the web service using XML that you’ve written. I’ll try to put together a working example shortly.

No More Silos!
Silos are only good when you're a farmer. One of the most significant additions to MOSS 2007 is the Business Data Catalog, or BDC. This is a shared service that allows you to create metadata describing how to connect to a variety of data sources and send or receive information from them. Out of the box it allows you to connect to a variety of data sources ranging from Oracle and SQL Server to web services. Because it is a metadata-driven abstraction, it delegates to the appropriate ADO.NET provider when performing the actual querying of the data.

The Business Data Catalog is good news because it reduces the amount of data silos that exist out there. Most organizations have dozens of small “one-off” applications that were developed to suit short-term tactical needs but have never gone away. They become headaches for IT staff and a real problem when figuring out how to retain their valuable data without going through hoops maintaining their aging or non-standard systems.

The BDC is a valuable way of surfacing their data through a standard API while reducing much of the development overhead. Once the metadata API has been written telling SharePoint how to connect to your legacy database, there is a BDC object model you can use to quickly and easily access your legacy data sources. Because this is standardized, your developers don’t need to know what the legacy data source looks like, or even what it is. They can spend all their time leveraging its data.

Soon your legacy data will start popping up on SharePoint team sites, to the delight of your clients!