If you decide to run your SQL Server under the LocalSystem account, you need to trust the server's computer account for delegation. Start by opening the MMC Active Directory Users and Computers administrative snap-in. If this snap-in isn't installed on your Win2K computer, you can add it by installing the AdminPak, which you can find on the Win2K Server installation CD-ROM (\i386\Adminpak.msi). Next, browse the domain for the server object and view the server's properties by double-clicking the computer icon. Figure 2 shows the Properties page for Athens. On the General tab, after you select the Trust computer for delegation check box, the message Trusting a computer for delegation is a security-sensitive operation pops up. The most important security concern associated with trusting a computer for delegation is that the computer could potentially impersonate any user who connects to it. Therefore, trusting for delegation a computer that's outside a firewall or that's not physically secured isn't a good idea.
If you decide to run the SQL Server service under a domain user account, you need to trust the user account for delegation. Open the Active Directory Users and Computers snap-in, then find the account you're using. Next, open the account's Properties page, then click the Account tab. Figure 3 shows the Properties page for the SQL Server service account. Under Account options, select the Account is trusted for delegation check box. For account delegation to take effect, stop and restart the SQL Server service.
SQL Server 2000 Books Online (BOL) describes how to enable account delegation. The documentation says you should add a Service Principal Name (SPN) to the server you're trusting for delegation if you use a domain account for the service account. However, I found that adding an SPN can cause all Windows authentication on the server to fail. If SQL Server is running under the LocalSystem account, SQL Server adds an SPN automatically and appropriately deletes the SPN when you change the service account, not when you shut down the server—as the Microsoft documentation states. In short, you never have to worry about SPNs.
Finally, you should be aware of two configuration settings that should be set by default. First, for a user account such as Jane, make sure that the Account is sensitive and cannot be delegated option isn't selected under Account options on the Account tab in the Active Directory Users and Computers snap-in, as Figure 3 shows. Second, confirm that SQL Server is using the TCP/IP Net-Library.
Applying Account Delegation
Now, let's look at two ways you can apply account delegation. In the example that Figure 1 shows, user Jane runs a query that accesses data on two different SQL Servers. First, you need to create a linked server on Athens that points to Byzantium and uses the login's current security context. The easiest way to create the linked server is by executing the following stored procedure:
EXEC sp_addlinkedserver 'BYZANTIUM'
The stored procedure sets the correct security configuration automatically. Alternatively, you can use Enterprise Manager to create a linked server. Open Enterprise Manager, then open the Security folder. Right-click the Linked Servers icon and select New Linked Server. Select SQL Server, then on the General tab, type the server's IP host name. On the Security tab, select the Be made using the login's current security context option, as Figure 4, page 39, shows. If account delegation has been properly enabled and if Jane has the necessary permissions, she can then connect to Athens through Query Analyzer and run the following SELECT statement:
SELECT 'The number of authors on Athens is ' +
count(*) FROM pubs.dbo.authors
UNION
SELECT 'The number of authors on Byzantium is ' +
count(*) FROM BYZANTIUM.pubs.dbo.authors
Now let's look at how account delegation would work if Athens hosted an IIS Web server. As with SQL Server, IIS can run under either the LocalSystem account or a domain user account. If you choose to run your IIS server under the LocalSystem account, make sure that the server's computer account is trusted for delegation. If you choose to run your IIS server under a domain user account, make sure the user account is trusted for delegation. The code sample that Listing 1 shows is an ASP page that queries a remote SQL Server through a trusted connection. A trusted connection uses the user's Windows login to authenticate on Byzantium, the remote server in Figure 1. To change the authentication method for an IIS server folder, open the Internet Services Manager (ISM) administrative tool. Expand the Web server tree, then right-click the folder you want to manage. Select Properties, then click the Directory Security tab. Under Anonymous access and authentication control, click Edit, which brings up the Authentication Methods dialog box that Figure 5 shows. To force IIS to impersonate the Web user who's connecting to the remote SQL Server, you need to clear the Anonymous access check box and select Integrated Windows authentication. Click OK to close the dialog box.
Prev. page
1
[2]
3
next page 
