Authenticating Logins - The TCP/IP Network Library
(Page 14 of 24 )
Let’s now look at the traffic generated when a user logs in using the TCP/IP network library and a SQL Server authenticated account. Just as it is in SQL Server 6.5, the TCP/IP network library is the easiest network trace to follow. The following listing shows an abbreviated trace of the network packets sent when Query Analyzer tries to log into the server using a SQL Server authenticated account.
NOTE The full trace can be downloaded along with the rest of the material accompanying the book from http://www.WinNetSecurity.com .
Frame Src MAC Addr Dst MAC Addr Protocol Description
2 W2kClient SS7_NT_SRV TCP ....S., len: 0, seq:3205348350...
3 SS7_NT_SRV W2kClient TCP .A..S., len: 0, seq: 380335491...
4 W2kClient SS7_NT_SRV TCP .A...., len: 0, seq:3205348351...
5 W2kClient SS7_NT_SRV TDS UNKNOWN EOM Len = 52 Chnl = 0 Pkt = 0...
6 SS7_NT_SRV W2kClient TDS Token Stream - Row continuation ???
Frames 2 through 4 are the TCP three-way handshake, which indicates W2kClient is initiating a TCP session with the database server. The description in frame 5 is not really descriptive, but inside that packet is the SQL Server login account name and password. Apparently, SQL Server accepts the account and password because frame 6 does not have an error message.
7 W2kClient SS7_NT_SRV TDS UNKNOWN EOM Len = 188 Chnl = 0 Pkt = 1...
8 SS7_NT_SRV W2kClient TDS Token Stream - Environment Change,...
9 W2kClient SS7_NT_SRV TDS SQL -
... get environment information, then close the session
25 W2kClient SS7_NT_SRV TCP ....S., len: 0, seq:3205461071...
26 SS7_NT_SRV W2kClient TCP .A..S., len: 0, seq: 380438509...
27 W2kClient SS7_NT_SRV TCP .A...., len: 0, seq:3205461072...
28 W2kClient SS7_NT_SRV TDS UNKNOWN EOM Len = 52 Chnl = 0 Pkt = 0...
29 SS7_NT_SRV W2kClient TDS Token Stream - Row continuation ???
30 W2kClient SS7_NT_SRV TDS UNKNOWN EOM Len = 218 Chnl = 0 Pkt = 1...
31 SS7_NT_SRV W2kClient TDS Token Stream - Environment Change,...
32 W2kClient SS7_NT_SRV TDS SQL -
... session established
Frames 7 through 24 contain queries from Query Analyzer that ask SQL Server for information about the server’s environment. Typically, this is the time that Query Analyzer will build a list of databases and turn on or off various session-specific settings. Like ISQL/W in SQL Server 6.5, Query Analyzer closes the first session when it has everything it needs and starts a new session. The only difference in the login traffic is that frame 28 includes the name of the client’s computer; otherwise, the process is the same.
Once you have a TCP session between the client and server, SQL Server authenticated logins go through a simple process of querying the syslogins system view in the Master database. If SQL Server does not find the account and password in the sysxlogins table that syslogins references, the user will get an error message. If it does find a row in the table, SQL Server then checks to see if the login account has been explicitly denied access to the server by checking the denylogin column (see the definition of the syslogins view in the section “Managing Login Accounts” later in this chapter). If the value is 1, the user receives an error message stating her login is denied.
This process is identical to the one used for SQL Server 6.5, including the fact that Windows does not check the user’s Windows account information before permitting SQL Server to receive the login request. If you have clients that use an operating system other than Windows, they can use the TCP/IP network library to connect to SQL Server with a SQL Server authenticated login. This is the only configuration that bypasses the Windows authentication process, because the client communicates directly with SQL Server via TCP port 1433 and does not use any of Windows RPC functions.
If you were to look at the actual packet contents of frames 5 and 28, you would see that the password is being sent in clear text. This is the main drawback of using the TCP/IP network library.
 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: Windows Authenticated Logins Via TCP/IP >>
More MS SQL Server Articles
More By Apress Publishing
/ 17 
