Connecting the Business Data Catalog to a Microsoft SQL Server Database or any other ADO.NET based data source is an easy task when using Windows Credentials since the users own credentials or the application pool identity can be passed to the business application directly or a user/password combination can be used in the definition file (not recommended).

While it is for many reasons the best practice to use Windows Credentials to connect to any data source, there are sometimes scenarios where other authentication mechanisms have to be used. When connecting the Business Data Catalog to a Microsoft SQL Server Database using SQL Server authentication, user credentials must be transmitted using the Microsoft Single Sign-On Service.

MOSS 2007 includes an administrative interface to the Microsoft Single Sign-On service as well as an own pluggable Single Sign-On provider implementation. The following paragraphs describe how to setup and configure Single Sign-On and Business Data Catalog for SQL authentication-based data connections.

For reference, see [1].

Setup and configure Single Sign-On

General

Installing Single-Sign On capabilities require high attention on security. The following paragraphs describe a step-by-step approach using best practices for security configuration when installing Single Sign-On for the Microsoft Office System Server (MOSS) 2007.

The general reference on how to install and configure the Single Sign-On Service can be found in Technet (see [2]).

Step 1: Understanding the architecture

To use Single Sign-On, the Microsoft Single Sign-On service (SSOSrv) must be installed on all Microsoft Windows front-end Web servers in the farm.
SSOSrv must also be installed on all servers running Excel Services. If the Business Data Catalog search is used, SSOSrv must also be installed on the index server.
In addition, the Encryption-Key server has to be determined. This is always the first server in the system where the SSOSrv is started.

Step 2: Setting up security accounts and groups

In order to setup Single Sign-On, it is best practice to setup the following security accounts and groups:

SSO Service Account:

• The account the Microsoft Single Sign-On Service is running on.
• Must be a domain user account. It cannot be a group account.
• Must be an Office SharePoint Server farm account.
• Must be a member of the local Administrators group on the encryption-key server.
• Must be a member of the Security Administrators role and db_creator role on the computer running Microsoft SQL Server. 
• Must be a member of the MOSS SSO Administrators Group.

MOSS SSO Administrators Group

• Can configure MOSS SSO Server settings such as the database, MOSS SSO Application Definition Administrators or encryption keys.
• Usually, these are the same administrators that also configure MOSS farm-level settings in Central Administration
• Must be a Windows global group, cannot be a domain local group account or a distribution list.
• Must be member of the Farm Administrators SPGroup on Central Administration.
• The single sign-on service account must be a member of that group.
• At least the farm administrator's account who is configuring Single Sign-On should be member of that group.
• All group members must be local Administrators on the encryption-key server. Do not make this account a member of the local Administrators group on the encryption-key server.

MOSS SSO Application Definition Administrators Group

• Can manage credentials of an enterprise application definition, including changing the password of a group enterprise application definition and changing or deleting credentials for an individual enterprise application definition.
• Must be a Windows global group, cannot be a domain local group account or a distribution list. 
• Must be a member of the Reader SharePoint group on Central Administration.

Please note: It is crucial, that all requirements are met. If not, you will receive an error stating "You do not have the permission to perform this action" when setting up the MOSS SSO Server in Central Administration.

Step 3: Configuring the Single Sign-On Service

1. On the server, click Start, Control Panel, Administrative Tools, and then click Computer Management.
2. In the Computer Management console, expand Services and Applications, and then click Services.
3. Right-click Microsoft Single Sign-On Service, and then choose Properties.
4. On the General tab, change the Startup type to Automatic.
5. On the General tab, under Service Status, click Start.
6. Click OK to save your changes and close the Properties window.
7. Repeat steps 1 through 6 for each applicable server in the farm.

Step 4: Registering the Single Sign-On Service in MOSS

This step is not described in [2], however it performs some additional configurations on all servers where Single Sign-On will be enabled. For example, it adds the SSO Service Account to the IIS_WPG local group and configures the SSO Service (Step 3) to run under this account.

1. On Central Administration, on the top navigation bar, click Operations.
2. On the Operations page, in the Security Configuration section, click Service Accounts.
3. Choose Windows Service and the Single Sign-On Service.
4. Select Configurable for the account and enter the SSO Service Account by using the form domain\username.
5. Click OK to save your changes and close the Properties window.

Step 5: Setting up the MOSS Single Sign-On Server

You must open Central Administration on the computer that runs Office SharePoint Server 2007 to manage server settings for single sign-on.

1. On Central Administration, on the top navigation bar, click Operations. 
2. On the Operations page, in the Security Configuration section, click Manage settings for single sign-on.
3. On the Manage Settings for Single Sign-On page, in the Server Settings section, click Manage server settings.
4. On the Manage Settings for Single Sign-On page, in the Account name box in the Single Sign-On Administrator Account section, type the MOSS SSO Administrators Group by using the form domain/group.
5. In the Enterprise Application Definition Administrator Account section, in the Account name box, type the MOSS Application Definition Administrators Group by using the form domain/group or domain/username.
6. In the Database Settings section, in the Server name box, type the NetBIOS name of the single sign-on database server (for example, computer_name or computer_name\SQL_Server_instance). Do not type the fully qualified domain name. 
7. In the Database name box, enter the name of the single sign-on database server. 
8. In the Time Out Settings section, in the Ticket time out (in minutes) box, type a value for how many minutes passes before a single sign-on ticket expires. The time-out should be long enough to last between the time that the ticket is issued and the time that the enterprise application redeems the ticket. Two minutes is the recommended value.
9. In the Delete audit log records older than (in days) box, type a value for how many days the audit log holds records before deleting them.
10. Click OK.

If you should get a "You do not have the permission to perform this action error" check the application log and make sure that all security requirements defined in step 2 are met.

Step 6: Creating the Application Definition

In the single sign-on environment, the back-end external data sources and systems are referred to as enterprise applications. For each enterprise application that Office SharePoint Server 2007 connects to, a corresponding enterprise application definition needs to be configured.

In order to connect to a Microsoft SQL Server database using SQL authentication a application definition has to be created in which the credentials of the SQL user will be stored.

1. On Central Administration, on the top navigation bar, click Operations. 
2. On the Operations page, in the Security Configuration section, click Manage settings for single sign-on.
3. On the Manage Settings for Single Sign-On page, click Manage settings for enterprise application definitions.
4. Click on New Item
5. Enter a display name for the application in the Display Name box
6. Enter an application name for the application in the Application name box
7. In the "Field 1: Display Name" , enter "User ID" and set "Mask" to "No"
8. In the "Field 2: Display Name", enter "Password" and set "Mask" to "Yes"
9. Click OK
10. Back on the Manage Settings for Single Sign-On page, in the Enterprise Application Definition Settings section, click Manage account information for enterprise application definitions.
11. On the Manage Account Information for an Enterprise Application Definition page, in the Enterprise application definition list in the Account Information section, click the application definition for which you want to manage account information.
12. In the Group account name box, type the name of the group that is allowed access to the enterprise application.
13. In the Enterprise Application Definition section, select Update account information.
14. Click Set. 
15. On the Provide Account Information page, in the Logon Information section, type the user name and password of the SQL user that will be used to connect to the Microsoft SQL Server database.
16. Click OK.

Configuration of the Business Data Catalog

Actually, there is no configuration to be done for the Business Data Catalog itself to interact with Single Sign-On. However, for every Application Definition where Single Sign-On is used, a LobSystemInstance node has to be added to the LobSystemInstances collection. Every LobSystemInstance specifies an "access point - in terms of connection and security information - to the LOB system, the Microsoft SQL Server database in this example scenario.

...
    <LobSystemInstance Name="Demo LOBDatabase">
      <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">moss-sql01</Property>
        <Property Name="RdbConnection Initial Catalog" Type="System.String">SQLAuthDemo</Property>
        <Property Name="RdbConnection Integrated Security" Type="System.String">false</Property>
        <Property Name="RdbConnection Pooling" Type="System.String">false</Property>
        <Property Name="SsoApplicationId" Type="System.String">SQLAuthDemoSSOApp</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>
...

In order to use Single Sign-On, please make sure that besides the regular connection information, the following properties are set:

RdbConnection Integrated Security: Tells the Business Data Catalog explicitly to not use integrated security.

SsoApplicationId: Name of the Single Sign-On application as specified in Step 6.5 of the Single Sign-On setup.

SsoProviderImplementation: Default pluggable implementation of the SharePoint SsoProvider.

Besides of the above configuration which is done directly in the application definition file, there are no further actions required to enable Business Data Catalog to connect using Single Sign-On services.

References

[1]: Microsoft MSDN: Business Data Catalog Authentication
[2]: Microsoft TechNet: Configure single sign-on