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 AUTHORITY\ANONYMOUS 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.