Account Delegation Through Impersonation

Use this AD feature for user authentication

Recent world events have increased awareness of the importance of all forms of security. IT professionals are responsible for ensuring that the systems they develop and administer are as secure as possible against both external and internal threats. A crucial and often overlooked aspect of computer security is the database-management system. Security in a database-management system such as SQL Server begins with authentication, which is the process of ensuring that a user requesting a service is a valid user and is authorized to access that service.

Two authentication methods are available for SQL Server 2000: Windows authentication and SQL Server authentication. Windows authentication uses accounts that the Windows OS maintains. SQL Server authentication uses accounts maintained entirely in SQL Server. Unlike Windows accounts, SQL Server accounts don't reside in a centralized security database, and you can't manage them with account policies. Microsoft makes it clear in the white paper "Microsoft SQL Server 2000 Security" (http://www.microsoft.com/sql/techinfo/administration/2000/securitywp.asp) that Windows authentication is more secure than SQL Server authentication, which is supported only for backward compatibility as well as for Windows Me and Windows 98 operability. When you install SQL Server 2000, the default security configuration is Windows authentication mode, a hint that future SQL Server releases might support only this configuration.

However, many DBAs and developers still use SQL Server authentication for application development because of a glitch in Windows authentication that occurs when a user accesses SQL Server resources on a server other than the one on which the user was initially authenticated. You can observe this glitch, called the double-hop problem, when the following scenario occurs: User Jane logs in to a workstation, then uses Windows authentication through a utility such as Query Analyzer to connect to a SQL Server named Athens. Next, Jane runs a remote query against a table on another SQL Server called Byzantium by using a linked server, as Figure 1 shows. If the linked server properties are set to use account impersonation, Jane receives the following error message:

Error 18456: Login Failed for user 'NT AUTHORITYANONYMOUS LOGON'.

This error can also occur if Athens hosts Microsoft IIS serving Active Server Pages (ASP) files that open trusted connections to Byzantium. (For more information about the double-hop problem, see the Microsoft article "PRB: Message 18456 from a Distributed Query" at http://support.microsoft.com/default.aspx?scid=kb;en-us;q238477.) Developers often work around the double-hop problem by using SQL Server accounts. For example, in the application code, developers often hard-code a SQL Server account username and password into database connection strings. Unfortunately, this solution creates an account-management nightmare, and anyone who can access the source code can easily read the passwords. Let's look at a better solution.

Account Delegation to the Rescue

Account delegation can help you overcome the double-hop problem so that you can use Windows authentication. The feature, which works only on Windows 2000 running SQL Server 2000 in an Active Directory (AD) domain, operates by letting Athens impersonate user Jane when Jane executes a remote query that connects to Byzantium.

To understand how account delegation works, let's take a brief look at Kerberos security, the authentication method AD uses in Win2K domains. Kerberos controls access to network resources by using service tickets. A service ticket is an encrypted piece of data that the domain controller (DC) issues to a user who successfully proves his or her identity with a password, smart card, or biometric identification. To connect to Athens, the user first obtains a service ticket for Athens from the DC. This service ticket is specific for both Jane and Athens. Jane can't use the ticket to connect to another server, and Athens won't accept the ticket from another user. When Jane runs a process (such as a remote query) on Athens that accesses a SQL Server database on Byzantium, Athens (or the service account under which SQL Server is running) obtains a service ticket on behalf of Jane to allow the connection to Byzantium. Jane never directly connects to Byzantium; Athens makes the connection by using Jane's security credentials. This process is transparent to Jane.

To enable account delegation, you need to make a few configuration decisions. First, you have to decide whether to run the SQL Server service under a domain user account (called a service account) or to run the service under the LocalSystem account. If the SQL Server service is running under the LocalSystem account, the server won't be able to send mail or participate in replication. Additionally, SQL Server won't be able to access file resources for tasks such as backing up to—and restoring from—a disk file on a network share. Using a domain user account overcomes these limitations but can be a security risk. For example, if an attacker gains access to SQL Server by using an account assigned to the systems administrator's (sa's) server role, the attacker can run OS commands by using the xp_cmdshell extended stored procedure within the security context of the SQL Server service account. Therefore, using a domain account with minimal privileges works best. However, I've seen account delegation fail unless it's running under an account in the domain administrator's security group. This problem appears to be an AD bug, and it usually disappears after several database-server reboots. To switch between different service accounts, you need to use the SQL Server properties menu in Enterprise Manager, not the Microsoft Management Console (MMC) Computer Management snap-in. Enterprise Manager properly sets permissions on certain files and registry keys to the service account. These permissions (new in SQL Server 2000) improve security over previous SQL Server releases.

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 (i386Adminpak.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.authorsUNIONSELECT '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.

The ASP file, Example1.asp, which Listing 1 shows, resides at the Web root of the Athens IIS server and runs a query against the Pubs database on Byzantium. Jane first logs in to the domain, then accesses the page by pointing her Web browser to the \athensExample1.asp URL. First, the code creates an ADO connection object named cn. The connection object uses a connection string that identifies the SQLOLEDB library, the Byzantium server, and the Pubs login database. The connection string's last parameter makes the connection a trusted connection. Next, the code creates a recordset from a simple SELECT statement that counts the number of records in the Authors table. The server then displays the result to the browser. Finally, the code closes and destroys the objects, releasing memory back to the OS. If the Athens computer account or the SQL Server service account have been trusted for delegation and if Jane has select permissions on Byzantium's Authors table, she will see the results of the query. If she's using Microsoft Internet Explorer (IE), the Web server won't prompt her for login credentials. The user's login credentials are passed transparently to the Web server. (If she isn't using IE, the Web server will prompt her for login credentials.)

Secure, Flexible Authentication

Data security is an important responsibility for all IT professionals. As the collection of personal, business, and national security­related data becomes more common, organizations will expect database developers and administrators to provide tighter security without incurring extra management tasks. By using account delegation, SQL Server professionals finally can phase out SQL Server authentication in favor of the more secure and easily managed Windows authentication.