An Introduction to Single-Sign-On (SSO) with Data Views

Why SSO?
The Microsoft recommended practice for connecting to SQL Server is to use Windows authentication, meaning that the Windows domain credentials of the end user are passed to SQL Server.
But there’s a catch when you’re trying to access a database from the web server: you must find a way to overcome the "double hop" problem. Windows credentials can only make one "hop" between machines on a network. The first hop is from the browser to the web server; but to get to a SQL Server machine on your network, a second hop is involved.
There are two ways around the double hop problem: (1) establishing a Constrained Delegation relationship between the web server and the database server, and having the network domain configured to allow Kerberos Protocol Transition, or (2) using the LogonUser API to switch to the end-user identity on the web server before performing a single hop to the database server. SSO uses approach #2. Approach #1 is typically too complex and potentially impactful on the entire domain for corporate IT departments to allow it.
SSO provides a secure way to overcome the double hop problem. MOSS 2007 (Enterprise Edition) includes a feature to create and manage SSO connections. You can use the built-in SSO provider, or substitute your own if your enterprise has an existing SSO solution.
How does SSO work?
The SSO feature maintains a mapping between a user, or group of users, and the credentials (username and password) needed to access a particular data source. This mapping is referred to as an Application Definition (or "app def" for short). Only server administrators can create and modify app defs, using the browser-based Central Admin UI.
When the DataFormWebPart needs to access a remote data source using SSO, it calls the SSO API to retrieve the necessary credentials for the given app def. If they happen to be Windows credentials, the web part temporarily "logs in" with those credentials, and then attempts to connect to the data source. This means the Windows credentials are only making one hop – from the web server to the database server – and not two.
SPD can create Data Views using both kinds of SSO app defs:

• Group
• Individual

A Group app def is used to let everyone in a domain group access a database using a single account. For example, you might have a special account for database use that only has read-only permissions on a few tables; SSO lets you force everyone in your workgroup to connect to the database with the limited permission account.
An Individual app def lets users provide their own account information (username and password). The first time a connection is attempted and the end user's credentials are not already in SSO, the Data View will redirect to a web page to collect and store them. Subsequent attempts will reuse stored credentials without prompting.
Either type of app def can be used to store Windows credentials (from a network domain account, in the format "domain\account"), or basic (non-Windows) credentials. In the case of SQL Server, you can use SSO to establish either Windows auth connections or SQL auth connections. You can also use SSO access web services that require a specific username and password, or Windows authentication.
What if I don't have SSO?
You just need to ask your admin to turn on the SSO feature if you're running MOSS 2007 Enterprise Edition. SSO is not enabled by default.
Otherwise, if you need to make a Windows authenticated database connection, there are two alternatives: (1) use a "basic auth" connection with an embedded username and password in the database connection string, or (2) get your network domain admin to set up Constrained Delegation and Kerberos Protocol Transition.
How do I create a Data View using SSO?
Let’s suppose someone has already created an Application Definition to connect to the Northwind SQL Server database, and that the app def name is "nwsql."

1. Boot SharePoint Designer, and open a web site on the MOSS 2007 server where the app def is defined.
2. Launch the Data Source Library task pane, using the menu command Data View > Manage Data Sources.
3. Expand the Database Connections category, and click the link to Connect to a database:
   
   
   
4. Click the Configure Database Connection button in the dialog that appears. Enter the server name, and then choose the option for SSO authentication.
   
   
   
   IMPORTANT: if the dialog does not show an option for "Use Single Sign-On authentication," the SSO feature is either not activated or not available on the SharePoint server. Contact your server admin for details, or read the last section of this article about how to configuring SSO.
   
   Next, click the Settings button to bring up this dialog:
   
   
   
   These are the 3 critical pieces of information you need to establish an SSO connection. The server admin who configured the SSO app def will be able to provide them; for more information, see step 6 below in the section about configuring an SSO connection. Note that this dialog is asking for the Application Name, not the Display Name. For example, suppose the SSO admin used a display name of "Product Support – Group Definition" and an application name of "ps_g." That means the dialog should be filled out as follows:
   
   
   
   By default, the username field is called "Username" and the password is called "Password" when the SSO app def is created.
   
5. Click the Next button to pick the Database and Table you want to query. From here on, there is no difference between using an SSO data connection and a non-SSO data connection. However, keep in mind that external databases typically have lots and lots of data, so it’s a good idea to set a Filter on the data connection to limit the number of returned records.

What does the markup look like for an SSO connection string?
An SSO ConnectionString looks like this in SPD’s Code view, for a typical SQL Server connection:

ConnectionString="Data Source=servername;User ID=<CredentialValue Class='User ID' Resource='sso:appname' Type='Username'/>;Password=<CredentialValue Class='Password' Resource='sso:appname' Type='Password'/>;Initial Catalog=databasename;"

That looks a bit strange, until you observe that there are HTML-encoded tags inside the string.
For example, suppose your SQL Server is called “dbserv”, and your database is called “Northwind”, and your SSO application is called “myapp”. In that case, the ConnectionString property would look like this (when HTML-decoded):

Data Source=dbserv;
User ID=<CredentialValue Class='User ID' Resource='sso:myapp' Type='Username'/>;
Password=<CredentialValue Class='Password' Resource='sso:myapp' Type='Password'/>;
Initial Catalog=Northwind;

As you can see, this is just a normal SQL Server connection string, with some embedded tags that get substituted with the credentials retrieved from the SSO app def – the Username and Password strings.
How do I troubleshoot SSO data connections?
Unfortunately for SPD users, most error messages coming back from the data source layer are purposely vague for security reasons – basically they just tell you "sorry, it didn't work."
However, SSO does write more helpful info in the server’s Event Viewer. To find it, search for the app def name in the Description field of Error events. For example, suppose I have an app def called "ps_g" that stops working. A server admin can open Event Viewer, and go to the Application log:

Invoking the command Action > Find brings up the following dialog. Uncheck everything but Error, put the app def name ("ps_g" in this case) in the Description, and click Find Next:

Open the matching error events to see what went wrong:

Once you've gotten an SSO-based data view working, it usually stays working, except for the following conditions:

• A password reset on one of the SSO accounts – in which case you’ll have to reconfigure the app def
• A change to the SSO app def by a server admin – either it gets deleted or modified
• The SSO service is no longer running on the server – probably not set to AutoStart after a reboot

How do I configure SSO and create an Application Definition?
Office Online has documentation on how to configure Single Sign-On. But the first time through it really helps to have pictures, and the steps can be simplified somewhat in the "single-machine farm" case. Just a warning: there are about 6 long steps to set up SSO the very first time; but after the initial work is done, it typically takes less than a minute to create each new app def.
You must be a server administrator logged into the main farm machine to configure SSO.

1. Login to the server machine as an administrator (either on the console or using Terminal Server), and start up a Central Admin browser session (Start > Programs > Administrative Tools > SharePoint 3.0 Central Admin).
   
2. Click the Operations tab, and then in the Security Configuration section, choose Manage settings for single sign-on:
   
   
   
   Note: the very first time you try to configure SSO, you will see the following red error message at the top of the page:
   
   
   
   If you don't see this message, skip ahead to the next step; the SSO service is running. Otherwise, go to the Windows control panel (Start > Control Panel > Administrative Tools > Services). Find the entry in the dialog that says Microsoft Single Sign-on Service and edit its properties by double-clicking:
   
   
   
   Set the Startup type to Automatic:
   
   
   
   Go to the Log On tab, choose This account, and provide the credentials for the admin account you are currently logged in as (in "domain\account" format):
   
   
   
   Go back to the General tab, click the Start button, then click OK, close the Control Panel dialogs, and finally click Refresh in the browser window. You should no longer see a red error message at the top of the page, and the Manage server settings link should now be enabled.
   
3. Click the Manage server settings link. In the form that appears, you typically only need to provide a domain account name in the first two textboxes, the rest is filled in for you with default values:
   
   
   
   With a sandbox server, you can use the same admin account name for both (the account used above when configuring the SSO service).
   
   Click OK to return to the Manage SSO page.
   
4. Click the link for Manage encryption key, then click the button for Create Encryption Key. Click OK in the page that appears afterwards:
   
   
   
   Click the Operations link at the top of the page, then the link to Manage settings for single-sign on.
   
5. Click the link to Manage settings for enterprise application definitions, then click the New Item link to create a new application definition with this form:
   
   
   
   The Display Name is what users see in the browser UI in Central Admin. The Application Name is a shorter version that will appear in connection strings. Also, note that the fields for holding credential information are always called "Username" and "Password" by default.
   
   As described earlier, you will need the following 3 pieces of information from this page in order to use the app def in SharePoint Designer:
   
   
   • Application name ("nwsql" in the example above)
   • Field 1 Display Name ("Username" in the example)
   • Field 2 Display Name ("Password" in the example)
   SharePoint Designer data views can use either Group or Individual app defs, with or without Windows authentication. SPD cannot use a "Group using restricted account" app def.
   
6. Click the browser’s Back button to take you back to the Manage Settings page, and click the link to Manage account settings for enterprise application definitions:
   
   
   
   Select the app def name to change (the "Display name" from the previous form) and enter the account name to use when making the database connection.
Click the Set button, which brings up a form to enter the username and password for the app def:



Click OK, then click Done.

Just to recap, all 6 of these steps are required when setting up SSO for the very first time, but afterwards you only need to perform the last 2 steps for each new app def.

Reference:

http://blogs.msdn.com/b/sharepointdesigner/archive/2007/08/27/an-introduction-to-single-sign-on-sso-with-data-views.aspx