An Introduction to Single-Sign-On (SSO) with Data Views
Hi there, I'm Russell Sasnett, a Program Manager for SharePoint Designer. Today's topic is about creating views and forms on external SQL Server databases using Windows authentication. This requires using database connections that integrate with the Single Sign-On (SSO) feature of MOSS 2007.
First I'll give a little background on SSO, then I'll show you how to create a data view using an existing SSO connection (called an Application Definition). Finally, I'll guide you through the process of creating a new SSO connection from scratch; typically this is something handled by an IT department or the administrator of a MOSS server farm.
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."
Boot SharePoint Designer, and open a web site on the MOSS 2007 server where the app def is defined.
Launch the Data Source Library task pane, using the menu command Data View > Manage Data Sources.
Expand the Database Connections category, and click the link to Connect to a database:
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.
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.
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).
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.
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.
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.
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.
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.
- Russ
Comments
Anonymous
August 27, 2007
Siguiendo con la tradicional recopilación periódica de recursos sobre WSS 3.0 & MOSS, en esta ocasiónAnonymous
October 20, 2007
Great article Russell. Good Job. On alternate #2, to connect to a database using a trusted connection, are there additional steps beyond the normal set up of the web application to use Kerberos delegation? Our web application is running fine but I have not been able to get SharePoint Designer using the asp:SqlDataSource to connect to a database via a trusted connection. The error I receive is: This control does not allow connection strings with the following keywords: ‘Integrated Security’, ‘Trusted_Connection’. Here is the custom connection string I am using: Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI; (also tried) Server=myServerAddress;Database=myDataBase;Trusted_Connection=True; Thanks again for the great article.Anonymous
November 02, 2007
After initial configuration and the step: "and provide the credentials for the admin account you are currently logged in as (in "domainaccount" format):" What steps do I need to follow so that the SSO service is not running under my account but uses a service acount?Anonymous
November 09, 2007
What do you do if there is no domain? single serverAnonymous
November 14, 2007
I recently discovered this very interesting article that covers the basics of configuring and using SSOAnonymous
December 11, 2007
SharePoint Designer data view– SSO error: Can anybody help me? Can I use SSO with data views on a MOSS single server deployment? I built an MOSS single server for testing using SQL 2000 and AD authentication. I used a User account (member of the Administrators group) for installing both MOSS and SQL 2000. I used the same account for configuring SSO application (step 6 in this), but I got en error message (An error occurred while retrieving the list of databases from SarverName: An Authentication error occurred. …), when I tried to build a db connection in SharePoint Designer. The SSO db was created, but in Event viewer the info was: SSOsrv couldn’t read the user credentials Error code: 0x80040e14 .. (A rough translation from hungarian) For configuring SSO and application I followed this article (except I used an individual account instead of group account):Anonymous
December 17, 2007
I kept having some problem with the Step 6. At 'Manage account settings for enterprise application definitions' I put in the serverdomainusername and then click on 'Set'. And the system prompt me 'please enter valid group in the form domaingroup'. Why is the error? Is it domaingroup of the database server which I am trying to connect to? Or is it domaingroup of my sharepoint 2007 server? I am reall stuck. Anyone can help?Anonymous
March 14, 2008
The comment has been removedAnonymous
July 08, 2008
What are the pros and cons of the SharePoint Single Sign-On Service?Anonymous
July 30, 2008
We are using only WSS 3.0 for a site that needs to exchange data with a SQL db. simple list; WSS user will enter data that will post to SQL; another list will retrieve data from SQL. Is this possible to develop using Designer?Anonymous
August 13, 2008
The comment has been removedAnonymous
August 25, 2008
Hi Russell, On the manage single sign on settings page I get the following error: "A Single Sign-on error has occurred. Please contact an administrator. Details: The network path was not found". I have followed all the steps in MS articles about giving permissions to the admin account for setting up SSO. In the even viewer I get the error saying this account does not have enough permissions to setup the SSO. This is a domain admin account. I have also added it to the local admin group. The SQL permissions are also according to the MS recomendations. Can you please advise what else I can check. I have not found any solution for this issue in any blog till now. Thanks, -OmerAnonymous
August 27, 2008
Hello. Great blog! Question though - is it possible to create a custom Web Part that authenticates with AD without using the SSO Database? For example - we have an in-house application that authenticates with AD. We wrote a Web Part to display some information from the custom application inside of SharePoint. The information it displays is dependant on the user who is authenticated. Right now, we use the SSO database but it's a pain when a user changes their password - they also have to change their password in the SSO database. Any suggestions? Thanks!Anonymous
August 28, 2008
Hello All, I have followed the instruictions to configure SSO and to create an application definition, but when I ltry to add the database connection in Sharepoint Desinger the option for SSO is not there. Is there a way to confirm on the server that SSO is funtioning properly? Any help is greatly appreciated. Thanks, DaveAnonymous
September 04, 2008
Hello I have the same problem like Dave (followed all instructions but the option for SSO in Designer is not there). However, there was an error in Event Viewer: "User ... failed to add enterprise application defintion SSO. The error returned was 0x806300b8. For more information, see the Microsoft SharePoint Products and Techologies Software Development Kit (SDK)." I will continue on this, but help will be appreciated. NicoAnonymous
September 11, 2008
Hi, I have problems migrating from SSO in SPS2003 to SSO in Moss2007 I have try to backup the SSO db in the SPS farm from SQL Server (Management Studio) and restore it in the MOSS farm. I have also made the backup and restore of the cryptographic key (from SPS to MOSS). The problem is that I'm not able to read the credentials stored in the SSO db in the MOSS farm: it seems a decryption problem.I used a simple web part that lists the credentials stored in the SSO for the current user. Anyone has a suggestion for this issue or some experience on it? ThanksAnonymous
December 19, 2008
Introduction – SharePoint Form Libraries and DBXL 2.3 DBXL 2.3 introduces a new feature: the abilityAnonymous
March 25, 2009
do you have succinct directions and configurations needed both for configuration in SP Central Admin I can tell my Server admin to configure to enable access to read/write, to an Oracle database in a different domain, behind a firewall and how to create the database connection for a document library or list (not form based or Excel based) ? SP Designer seems to input its own SQL commands into the data connection file after creating it, that renders it incompatible with Oracle database because of use of SQL statements/syntax.Anonymous
May 12, 2009
External References Plan for single signonAnonymous
January 13, 2010
A good article. May be you can write the similar article about sharepoint2010.Anonymous
June 18, 2011
Please pass onto russ sasnett...To contact parryd@temple.edu Re. Ricky Leacock