Authenticating Logins - The Multiprotocol Library in SQL Server 2000
(Page 17 of 24 )
The Multiprotocol network libraries in SQL Server 6.5, 7.0, and 2000 all follow the same process to authenticate users. Because the network library is using RPCs, Windows will always authenticate the client’s Windows account and password before it permits SQL Server to see the login request, just as you saw in the trace for the Named Pipes network library. The main point of discussion for SQL Server 2000, therefore, is how the Multiprotocol network library’s encryption compares to SSL.
NOTE Knowledge Base article Q271242 states that Microsoft made a change in the Multiprotocol client driver that comes with Microsoft Data Access Components (MDAC) version 2.1. If the client application chooses a TCP/IP connection within the Multiprotocol network library, the MDAC 2.1 and later versions include a version dbmsrpcn.dll (which is the .dll that implements the Multiprotocol library) that does not force a check of the user's Windows account for SQL Server authenticated logins. In my testing, the versions of the library that come with SQL Server 7.0 Service Pack 4 and SQL Server 2000 Service Pack 2 both still check the user's Windows account when connecting with Query Analyzer. When you use MDAC in a client application, be sure to check the behavior of the Multiprotocol network library.
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.
In comparison, the encryption provided by SSL and the encryption offered by the Multiprotocol network library are very similar. SSL is part of the session layer or the presentation layer, depending on whom you ask, and it does not offer protection for any of the information in the layers below the session layer. In terms of what they encrypt and the overall security they offer, SSL and Multiprotocol encryption are similar enough to be interchangeable, at least where SQL Server 2000 is concerned. Servers running SQL Server 7.0 will not have the option of using SSL, so the Multiprotocol network library is the only choice if you want to secure your data on the network.
Given that SSL does not offer a clearly superior mechanism for securing data, the fact that SQL Server 2000 offers it is probably not sufficient reason by itself to upgrade from SQL Server 7.0. If you need to decide whether to upgrade to SQL Server 2000 in order to use SSL, there are two factors to consider:
- First, the Multiprotocol library requires that a client have a Windows account. If you have users who do not have a Windows account or do not have an account that can be authenticated by your domain controllers, the Super Socket network library in conjunction with the TCP/IP network library will be the only choice for encrypting the password and data stream offered by SQL Server.
- Second, the protocols used by the Multiprotocol library to implement encryption are proprietary to Microsoft, and the library is only available for Windows. SSL, on the other hand, is a protocol in the public domain, which means it is widely used by vendors other than Microsoft. If you need secure data transmission between SQL Server and something other than a Windows client, SSL would be the only option.
 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: Managing Login Accounts >>
More MS SQL Server Articles
More By Apress Publishing
/ 17 
