Kerberos delegation works for as many SQL Servers as you want to use, but it also works for other services. XML and .NET Web services in the SQL Server environment give you new options for querying data from external sources. Additionally, Microsoft has released a SQL Server 2000 add-on, SQLXML (which you can obtain at http://msdn.microsoft.com/downloads/default.asp?URL=/downloads/sample.asp?url=/msdn-files/027/001/824/msdncompositedoc.xml), which lets .NET applications use stored procedures as .NET Web services. That option presents many possibilities for using SQL Server as an intermediary for other SQL Servers or even other nonrelational data sources. Delegation, or having the user's security credentials move from server to server, is the best way to make use of these new capabilities.
Requirements
To use Kerberos between SQL Server clients and servers, you must have a Win2K domain, which can be running in either mixed or native mode. You must install SQL Server 2000 on a Win2K server, and all clients accessing the server must be running Windows XP (Professional or .NET Server) or Win2K (any server version or Professional). Computers running an OS other than XP or Win2K can't use Kerberos for authentication or encryption.
Additionally, all computers and user accounts that will use Kerberos authentication must be members of the same domain as the SQL Server 2000 server or of domains that have trust relationships with that domain. In general, all computers and users should be members of the same forest, but it's possible to set up trusts with external forests and domains that will let members of those domains access SQL Server 2000. However, for this discussion, let's assume that all clients and servers reside in the same forest.
To use delegation, the MSSQLServer service needs to log in with a domain account instead of the LocalSystem account. You need to set two account options for that domain account by going into the AD Users and Computers Microsoft Management Console (MMC) snap-in and selecting the Account tab. First, clear the Account is sensitive and cannot be delegated check box. Then, select the Account is trusted for delegation check box for the SQL Server service account, as Figure 3 shows.
Also, the server's computer account must have the Computer is trusted for delegation check box selected. You can set this option in the Properties dialog box for the computer in the AD Users and Computers tool. These settings require administrator privileges, so you'll need to enlist the aid of your network administrator if you don't have those privileges.
SQL Server Books Online (BOL) says that you also need to use the setspn utility in the Windows 2000 Server Resource Kit to create a Service Principal Name (SPN) for the SQL Server service. However, SQL Server 2000 automatically creates an SPN when it starts. You can verify whether the SPN exists by using the —L parameter on setspn to view the SPN for the account the service uses to log in.
This Is a Test
To test your setup, you need at least a Win2K domain controller, a client computer running Win2K or XP Professional, and a server running Win2K as a member server with SQL Server 2000. Although keeping your service packs up-to-date is always a good idea, Kerberos authentication should work with any combination of service packs on the computers. Besides the servers, you need to install the Win2K Server Resource Kit on the Win2K Pro and SQL Server systems. Two resource kit utilities, KerbTray and setspn, let you see the contents of your Kerberos tickets.
After configuring the SPN and verifying that the SQL Server service has an entry in the AD, you need to make sure the account you'll be using on the client can connect to SQL Server. The easiest way is to use Query Analyzer and a Win2K/NT account to connect. To ensure that SQL Server logins don't cause problems, configure SQL Server to accept only Win2K/NT authenticated logins. If this connection test is successful, you know the user can authenticate correctly.
The next steps determine whether the client computer uses Kerberos for authentication. On the client, find the KerbTray tool in the resource kit's tools list and open it to see the list of tickets in the local computer's ticket cache. Make sure you have a ticket for the SQL Server. Repeat the process on the SQL Server computer to verify that you have a ticket for the client. Finally, in Query Analyzer, run SELECT SUSER_SNAME() to find out whether the client account's SID is the login ID. For SQL Server logins, the system user ID (SUID) will be blank, so if you see a SID, you know you've logged in with a Win2K/NT login.
You can gather more information about the authentication process by enabling auditing of login events and logging of Kerberos events. If you enable auditing of login events through the domain policy, you can see events on all servers in the domain. To enable logging of Kerberos events, you need to add the following subkey to the registry:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\ControlLsa\Kerberos\Parameters
In the Parameters subkey, add a DWORD value named LogLevel and set the value to 1. You need to add these entries to every server for which you want to log Kerberos events. After you restart the server, you can see these events in the System Event log.
If you have problems getting the client to use Kerberos authentication, check the SPN setup first. If you don't configure the SPN correctly, authentication reverts to the Windows authentication protocol. Another potential problem area is the permissions granted to the computers and the client account. The local, domain, and group policies applied to the computers and accounts involved can prevent you from connecting to SQL Server. If you suspect problems with policies, security-related settings are the most likely cause.
Prev. page
1
2
[3]
4
next page 
