Authenticating Logins - Windows Authenticated Logins Via TCP/IP
(Page 15 of 24 )
In contrast to SQL Server 6.5, SQL Server 7.0 and 2000’s version of the TCP/IP network library supports trusted connections using Windows accounts or groups to log in. These connections are called trusted connections because SQL Server trusts Windows to handle the authentication process entirely.
The overall process is similar to what you will see in the network trace of the Named Pipes network library in the next section. The client connects to SQL Server on TCP port 1433 and asks for a trusted connection. At that point, Windows follows its normal process of authenticating the account and password, creates an access token if the account is valid, and then routes the login request to SQL Server.
One benefit of Windows authenticated logins via TCP/IP over SQL Server authenticated logins is that the password does not travel the network in clear text. As you saw earlier, this is a problem with the way SQL Server 6.5’s TCP/IP network library handles authentication. Other than using something such as IPSec to encrypt the network packet at the IP layer of the network architecture, there really is no way to prevent an attacker from discovering the passwords for SQL Server authenticated logins in SQL Server 6.5. If you are using SQL Server 7.0, Windows authenticated logins are a way to protect users’ passwords with the TCP/IP network library.
If you are using SQL Server 2000, you have the option of using the Super Socket network library and SSL to encrypt the account and password for a SQL Server authenticated login. The question, then, is what are the tradeoffs between using SSL and SQL Server authenticated logins and using Windows authenticated logins to protect users’ passwords?
There are two significant differences between Windows authenticated logins and SSL. First, SSL validates the identity of the server but not the client, whereas Windows authentication validates the client and not the server. Technically, the server’s identity is validated to a small extent for domain accounts by the fact that the server knows enough about the domain to permit its accounts to log in. This level of validation is not really worth much, though, because it does not provide the client with definite, verifiable authentication of the server’s identity.
Second, the Windows authentication protocol protects the password for the user’s Windows account but does nothing to protect the data passing between the client and server. SSL, of course, encrypts the entire data stream with SQL Server authenticated logins. The drawback is that SSL decreases the server’s performance in situations in which its clients request large amounts of data.
The conclusion you should draw from these differences is that in situations in which knowing the server’s identity is just as important as knowing the client’s, use SSL. If you need to protect the user’s password but not the data stream, or if SSL will hurt performance too much, use Windows authenticated logins. If you need definite, verifiable validation of both the client and server identities, you can use Windows logins in concert with SSL. Finally, if you need both to secure the authentication process and to encrypt the data as it travels the network, you can use SSL with the Named Pipes, Multiprotocol, and TCP/IP network libraries.
 This is from SQL Server Security Distilled, second edition, by Morris Lewis (Apress, ISBN 1590592190). Check it out at your favorite bookstore today. Buy this book now. |
Next: Logins Using Named Pipes >>
More MS SQL Server Articles
More By Apress Publishing
/ 17 
