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:
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.
Reference:
http://blogs.msdn.com/b/sharepointdesigner/archive/2007/08/27/an-introduction-to-single-sign-on-sso-with-data-views.aspx
