SQL Server Magazine

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.

   Prev. page   [1] 2 3 4     next page