Passing Credentials

Congratulations to Kirby Richter, senior consultant at Magenic Technologies in Golden Valley, Minnesota, and Francis Apel, data warehouse and database administrator at Madison, Wisconsin-based Promega. Kirby won first prize of $100 for the best solution to the December Reader Challenge, "Passing Credentials." Francis won second prize of $50. Here's a recap of the problem and the solution to the December Reader Challenge.

Problem

Beck is the DBA for several SQL Server 2000 systems. The systems are installed on Windows 2000 servers that are enabled with Kerberos security protocol and that are running Active Directory (AD) services. Some of the SQL Server services run under the LocalSystem account, and others run under a MSSQLService account. Several of Beck's company intranet applications need access to all SQL Servers from any of the SQL Server machines. The applications are written in Active Server Pages (ASP), and the browsers run on Win2K clients. The applications grant users access to each SQL Server by assigning users Win2K logins to the appropriate database roles. Beck wants all application users to be able to access the SQL Servers only through their assigned database permissions so that he can manage the users' credentials through the Win2K groups and leverage the database roles to segment the users. To achieve this access, Beck sets up linked-server connections between the SQL Servers. He also configures each linked server to connect all logins to other SQL Servers when users log in under their own credentials. But when he tries to execute procedures that use distributed queries from an application, he gets the following error message:

Server: Msg 18456, Level 14, State 1, Line 1Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.

What's wrong with Beck's setup? And what should he do to let users access data from one SQL Server to another using linked servers?

Solution

Beck received the error message because the user credentials from the client machine couldn't pass successfully from one SQL Server to another. This error typically occurs when the client makes multiple hops to the server and the original credentials are lost during connection from one server to another. You can say a client makes multiple hops when the client connects to Server A (an IIS server in our example), and Server A connects to Server B (installed with SQL Server), which in turn connects to other servers. To ensure that this operation succeeds, Beck needs to enable security account delegation on each of the SQL Servers. This Windows 2000 feature lets the client connect to multiple servers and pass the authentication credentials of the original client to each of the servers. He can use the Active Directory Users and Computers administration tool to check the Computer is trusted for delegation box for the SQL Server running under the LocalSystem account and check the Account is trusted for delegation box for the MSSQLService account.

In addition, for the SQL Server running under the MSSQLService account, Beck can assign a Service Principal Name (SPN), which provides verification for SQL Server running on a particular machine and lets security account delegation work correctly. Then, Beck can assign SPN by using the SetSPN utility from the Microsoft Windows 2000 Resource Kit. For example, to add the SPN for server dbprod1, he can enter the following code:

setspn -A MSSQLSvc/dbprod1.company.com:1433 MSSQLService

After enabling security account delegation for the SQL Server servers, Beck can run the distributed queries successfully. For more details on setting up security account delegation, see the Security Account Delegation topic in SQL Server Books Online (BOL).