Authenticating Logins - SQL Server 6.5 Named Pipes Login Summary
(Page 10 of 24 )
In comparison to the TCP/IP network library, the Named Pipes library is a little more secure because of the way the authentication process works. Although the library still sends the password for Standard logins in clear text, at least the Windows account’s password stays secure, especially if you follow my advice and use Windows 2000 instead of Windows NT for the database server. There is still a potential security problem caused by the fact that the data stream itself is unencrypted. If security is your most important criterion for choosing between network libraries, Named Pipes should rank higher than TCP/IP and lower than the Multiprotocol library, which I discuss next.
Logins Using the Multiprotocol Library The Multiprotocol library gets its name not because it works over multiple network protocols—which it does—but because it can work with multiple IPC protocols at the same time. Clients can choose to connect to the server using Named Pipes, directly via TCP/IP, or via an SPX session using the NetWare network library. Because the sad truth is that NetWare clients are very rare, I focus on Named Pipes and TCP/IP.
The Multiprotocol library was introduced in SQL Server 6.0 to take advantage of a more efficient IPC mechanism known as a remote procedure call (RPC). Essentially, RPCs allow client applications to run subprograms on the server. The benefit is that the client can utilize existing functionality on the server without having to duplicate the code itself. The ability to accept RPCs is a significant part of what makes Windows NT and 2000 good platforms for running applications. They come with a large quantity of subprograms, which client programs can use so that they do not have to duplicate the functionality usually provided by the operating system. You can think of RPCs as a kind of Swiss army knife for programmers.
Because the explanation of RPCs can become esoteric rapidly, I do not step through a network trace for the Multiprotocol library. The overall login process is very similar to what happens with the Named Pipes and TCP/IP network libraries. The main difference is that instead of using the NetBIOS SMB protocol, as is the case for Named Pipes, or sending packets directly to SQL Server, as is the case for TCP/IP, the Multiprotocol client library uses RPCs to send the data. Clients can still choose to use Named Pipes but, instead of opening a file using NetBIOS, they use the file-management functions built into Windows NT and 2000’s RPC library. When you look at the packets, you will see the client opens a pipe named \000000BC.01 instead of \sql\query, but it still sends commands to and receives data from SQL Server by writing to and reading from that file.
What happens at the network level is less significant for the Multiprotocol library because it has little effect on the login process. The one point of interest in a network trace of the login traffic is that no matter which of the three available IPC methods you choose (Named Pipes, TCP/IP, or SPX), the Multiprotocol library will always authenticate the user’s Windows account credentials, just as we saw in the network trace of the Named Pipes library. Once again, it does not matter if the client uses a Standard Security login or an Integrated Security login. The operating system always authenticates the Windows account before SQL Server sees the login request.
The most important feature from a security point of view is that the Multiprotocol network library has the option of encrypting the data stream. Both the SQL Server login request and the data passing between the client and server will be encrypted. The process of authenticating the user at the operating system level stays the same, though. It is only after the client establishes the connection with Windows NT/2000 that the Multiprotocol library starts encrypting the information it sends to SQL Server.
For some, it may help to refer to the Open Systems Interconnection (OSI) network model shown in Figure 2-4. Because the Multiprotocol library is a mechanism for encapsulating tabular data traveling between the client and server, it sits at the bottom of the application layer. (Some purists may want to place it in the presentation layer.) Everything below it is left unencrypted. That means that the Multiprotocol network library’s encryption does not protect against problems such as the weaknesses in the NTLM authentication protocol, which I discussed earlier. Any attacks that depend on weaknesses at the bottom six layers of the OSI model will still be problems even if you enable encryption in the Multiprotocol library.
Figure 2-4. The OSI network model
What encryption at this level does protect is the user’s password and the contents of resultsets returned from the server. You can either enable encryption at the server so that all client connections will be encrypted, or you can enable encryption on a client-by-client basis. The choice will depend on your assessment of where the risks are greatest. If your server contains mostly sensitive data such as accounting information or customer credit card numbers, encrypting all sessions is a good idea. It is also a good idea if you use Standard Security, because it is the easiest way to protect users’ passwords. If all you want to do is protect logins, you should consider using Integrated Security instead of encryption because there is a performance penalty with encryption. If you have only a few users who access sensitive data, or if users authenticate through an application and the application uses its own account to log in, you can choose encryption on a client basis. The only advice I will offer at this point is that configuring the server to require encrypted connections is much easier than configuring each client’s computer.
Now it is time to see how SQL Server 7.0 and 2000 improve on the authentication process and fix some of the flaws you have seen in the way SQL Server 6.5 handles authentication.
 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: Authentication in SQL Server 7.0 and 2000 >>
More MS SQL Server Articles
More By Apress Publishing
/ 17 
