All security operations in SQL Server depend on the twin processes of authentication and authorization. If the server doesn't have total confidence in the user's identity and, thus, can't be sure of the permissions a user has, all attempts to control access to data fail. Microsoft has long preferred Windows NT authenticated logins over SQL Server authenticated logins because Windows has more effective mechanisms for verifying users' identities than just comparing an account and password combination. Kerberos authentication, the default authentication protocol in Windows 2000, improves on NT's authentication protocol in several ways and offers identification of both the client and the server. Let's look briefly at how Kerberos works, then examine how you can use its features to guard the data on your SQL Server 2000 servers. Note that you have to be running SQL Server 2000 on Win2K to use Kerberos; I cover the requirements in detail later.
Why Use Kerberos?
If your application uses SQL Server logins, the client sends the password to the server in clear text. (With SQL Server 2000, the password is encrypted during the handshake by default.) To protect the password during transmission, you must use either the Multiprotocol Net-Library, Secure Sockets Layer (SSL), or IP Security (IPSec) encryption. Of the three, only IPSec can be managed for the entire enterprise through domain policies. The Multiprotocol Net-Library has two drawbacks: Clients can choose not to use it if the server supports any other Net-Library, and it works only against the default instance. SSL's requirement of a separate certificate for each server can increase management overhead and cost if you have multiple servers. For a comparison of IPSec and Kerberos, see the sidebar "IPSec vs. Kerberos," page 24.
In contrast, Windows-authenticated logins use Windows' authentication protocols, which always encrypt the user's identifying information. The main reason to encrypt the password is so that no one can impersonate any user. The Kerberos authentication protocol uses stronger encryption algorithms than the NT LAN Manager (NTLM) protocol that NT uses, so with Kerberos, stealing passwords is more difficult for someone who intercepts the network communication between client and server.
Kerberos' ability to ascertain the identity of both the client and the server also offers some benefits that other authentication mechanisms don't. If you're not running Win2K, SQL Server can validate the user's identity through either an NT login or a SQL Server login, but the best a client can do to validate a server's identity is to know the server's network address. SSL provides one solution to that problem because the server certificate contains the server's address, but the server certificate identifies only the computer, not the SQL Server service that the client is trying to access. Additionally, without Win2K's locally managed certificate authority, getting certificates from certificate authorities such as Verisign or Thawte is a time-consuming and expensive process. Consequently, many administrators use SSL only on servers where encryption is mandatory. Like SSL, IPSec authenticates the identities of both computers but not the SQL Server service. Only Kerberos authentication ensures that the client is communicating with the correct SQL Server service.
For Web-server access, Kerberos authentication offers a way to prevent a Web application from communicating with other SQL Servers if the Web server becomes infected with a virus such as Code Red. Within Active Directory (AD), you can grant or deny access to a particular service, so you can improve your internal network security by allowing your Web server to communicate only with a specific SQL Server.
Kerberos Authentication
The basic premise of Kerberos authentication is that if two entities share a secret known only to them, they can verify each other's identities by demonstrating that they each know the secret. By default, Win2K's implementation uses a password as the secret because the user and the domain controller always share the password. Alternatively, you can use a public key—private key combination, but the idea is the same—to prove an identity.
To protect the password, Win2K transforms it by using a one-way hashing function, then stores the hashed value instead of the password itself. The hashing function disguises the password so that you can pass the hashed value across the network in place of the password without worrying about someone using it to derive the password.
Although Win2K could safely send the hashed value over the network, it doesn't. Kerberos uses yet another safeguard to keep both the password and the hashed value safe from third parties. During the authentication process, the client-side Kerberos Security Provider in Win2K encrypts a string that contains the current time, using the hashed value of the password as a key. It then sends the resulting encrypted string to the Authentication Service (AS) that's running on a Win2K domain controller. The AS retrieves the hashed value for the specified account from the AD database and decrypts the string the client sent. If the decryption is successful and the time in the string is close enough to the current time on the domain controller, the AS can be sure that the person using the client computer knows the shared secret (the password). For a detailed discussion about how the authentication process works, see Chapter 3 in Understanding Microsoft Windows 2000 Distributed Services by David Chappell (Microsoft Press, 2000).
A shared, secret key has another value besides authentication. If the domain controller and a service are the only two entities that know the key, they can encrypt data so that only they can read it. In this way, Kerberos protects the sensitive data in the tickets it uses to permit entities to talk to each other. All services require a ticket before a user can use those services. To illustrate the ticketing process, let's look at an example.
When user Joe logs on to his computer, the Win2K Local Security Authority and Kerberos Security Provider work together with the domain controller to authenticate his account and password combination. If the process is successful, the Ticket Granting Service (TGS), which runs on the domain controller with the AS, sends a Ticket Granting Ticket (TGT) to Joe's computer, as Figure 1 shows. The TGT contains information about Joe's SID and the SIDs of all the domain groups to which his account belongs. The TGS needs this information so it can create new tickets that let Joe use other services, both on his own computer and on other computers on the network.
The TGS also adds a randomly created session key that both sides can use to encrypt data, both inside the ticket and in the communications channel. To protect the session key, the TGS encrypts it with the hashed value of Joe's password and sends it to Joe's computer along with the TGT. Now, the domain controller and Joe's computer have an encryption key known only to themselves because only they know the shared, secret key that encrypted it—the hashed value of Joe's password.
In addition to the session key, the TGS encrypts parts of every ticket with the hashed value of the password for the service that's the ticket's target. The main benefit of this partial encryption is that only the two entities that share the key can view or change the encrypted data. For example, Joe can't manipulate a ticket to make himself a member of the Domain Admins global group to gain sysadmin privileges in SQL Server. Partial encryption of the ticket also proves to the service that the domain controller created the ticket because only the domain controller and the service know the key. Finally, it indirectly proves that the domain controller has authenticated Joe because there's no other way for Joe to have a ticket encrypted with the service's secret key.
After a successful login, Joe's computer has a hashed value of Joe's password that it can use as
- a shared secret key
- a TGT that lists all Joe's SIDs and other information about Joe and his computer
- a shared session key that can encrypt the data stream between Joe's computer and the domain controller
When Joe receives permission to connect to a SQL Server, his computer receives another ticket that contains information about Joe and his computer. This ticket is encrypted with the hashed value of the SQL Server service account's password. At this point, every entity involved knows that it's really Joe trying to access a SQL Server database.
How does this process affect application performance? Typically, the client computer acts as a broker for the authentication process. The client computer receives tickets from the domain controller, then presents those tickets to the services it wants to use. Because the client computer caches all tickets, which have a default 10-hour lifespan before the client must renew them, the client needs to request a ticket for a given service only twice a day at most. It can then use that ticket to connect to a service as many times as it needs to until the ticket expires. Although the authentication process might seem complicated, it shouldn't degrade the connection times with SQL Server because the tickets are cached in RAM.
Delegation
Authentication between client and server is just one Kerberos option. Delegation is another option that allows one SQL Server to connect to another server, using the security credentials a user used to log in. For example, Joe might issue a query on Server A that needs some data from Server B. Under SQL Server 7.0 and 6.5, Joe can use a SQL Server login account (but not his Win2K/NT account) to connect to Server B from Server A. In SQL Server 6.5, queries to remote machines require you to use remote stored procedures. In SQL Server 7.0, the easiest way to handle this situation is to set up a remote server connection. However, SQL Server 2000 can treat data from a query that's executed on a remote server as if it came from a local table. This integration of remote data sources into the T-SQL syntax requires SQL Server to connect to those data sources without additional input from the user.
Delegation offers you the flexibility of having multiple database servers with multiple sets of permissions for the same user. For example, user Joe might have SELECT, INSERT, UPDATE, and DELETE permissions for a database on Server A but only SELECT permission for a database on Server B. Before SQL Server 2000, a common way to let a query running on Server A retrieve data from Server B was to use a stored procedure that allowed only reading of data. That technique requires programmatic changes to the stored procedure for every server whenever the user's rights on the underlying data change. Delegation, however, lets administrators manage permissions on all servers by using SQL Server's built-in authorization commands. If you have queries that access multiple servers, you'll find that delegation streamlines your management chores far more than alternative solutions such as remote stored procedures.
Delegation is a natural outgrowth of Kerberos authentication. If Joe passes to Server A his TGT and the session key he shares with the domain controller, Server A can request a ticket for Server B on Joe's behalf, as Figure 2 shows. The TGT is already encrypted with the domain controller's hash value, so the domain controller knows that the TGT is valid. Joe's session key that's shared with the domain controller is encrypted by using the session key he shares with Server A. Therefore, if Joe trusts Server A enough to give it the session key, the domain controller knows that Server A is making the request as if Joe were logged on to Server A.
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.
The End Result
After all the effort of authenticating both the client and the SQL Server service, SQL Server receives the same information about the client that it gets for Win2K/NT logins that don't use Kerberos. No matter how the user authenticates, the OS creates an access token that contains the user's SID and the SIDs for all the local and domain groups the user belongs to. SQL Server then checks the list of SIDs to see whether one of them matches an entry in the master database's sysxlogins table. If a match exists, SQL Server permits access to its databases.
Permissions within the databases also operate independently of the authentication protocol the client uses to log in. For Win2K domain accounts, you still assign permissions and role memberships based on the account's SID, so once a user is logged in, nothing changes in SQL Server 2000's internal authorization structure.
The only change from SQL Server 7.0 is that in all cases, an XP/Win2K client connecting to an XP/Win2K server attempts to authenticate by using Kerberos. If that authentication fails, XP/Win2K reverts to the NTLM protocol that NT and Windows Me/9x use. Because SQL Server 2000, 7.0, and 6.5 all leave authenticating domain accounts to the OS, you can gain some of the benefits of using Kerberos just by running SQL Server on Win2K. Remember that by default, all trusted connections will authenticate with the OS before SQL Server sees the login request. Although SQL Server 7.0 and 6.5 users can't use an SPN to authenticate their identities or use delegation to connect to remote servers, they can benefit from the increased security of the Kerberos protocol over NTLM and from the better management tools that Win2K offers. IPSec is also easier to set up with Win2K than NT, so even if you don't move to SQL Server 2000, consider upgrading from NT to Win2K.
Running SQL Server 2000 on Win2K offers great flexibility in how you manage logins, how you secure both the authentication process and the channel between the client and server, and how you limit access to services enterprisewide. Much of what I discussed in this article happens automatically when you have an AD domain and Win2K on both the client and the servers, so SQL Server DBAs will find using Kerberos very straightforward. And you'll breathe a little easier when you know Kerberos is guarding the gates to your data.
