Authenticating Logins - Logins Using TCP/IP
(Page 7 of 24 )
The TCP/IP network library is the simplest case in terms of network traffic. In this section, you’ll examine the listing of the actual list of network packets. It is avery short process because SQL Server handles all the authentication tasks internally. To help you make sense of what the packets contain, I go through the process in detail, step by step, pointing out potential problems along the way.
Frame Src Addr Dst Addr Protocol Description
5 W2kClient SS65 TCP ....S., len: 0, src: 1300 dst: 1433
6 SS65 W2kClient TCP .A..S., len: 0, src: 1433 dst: 1300
7 W2kClient SS65 TCP .A...., len: 0, src: 1300 dst: 1433
Starting in frames 5 through 7, you see the three-way handshake that starts aTCP session. This set of three packets makes a good reference point when you are looking at network traffic, because they always signal the beginning of TCP sessions. Because all communications with SQL Server happen within a TCP session, a three-way handshake will mark the beginning of every client-server session.
8 W2kClient SS65 TDS Login - , JoeS, 00000194, Microsoft ISQL/w
Frame 8 is the first login packet. Notice that the protocol used is TDS, which stands for Tabular Data Stream. TDS is the protocol used by Open Data Services (ODS), which is a set of functions on Windows NT and 2000 that handle communications with clients. SQL Server does not actually handle network communications itself. It is ODS that handles all sending and receiving between the client and server, which is why you will see all communications between SQL Server 6.5 and the client use TDS as the protocol, whether or not you are using TCP/IP. Figure 2-2 shows how TDS represents a tabular data set as a hierarchical structure.
NOTE SQL Server Books Online has more information on ODS.
Figure 2-2. How TDS represents a tabular data set as a hierarchical structure
TDS looks similar to XML in that it is designed to handle hierarchical data sets. As Figure 2-2 shows, each table is represented as a recordset. Within each recordset is a rowset that contains all rows in the table. Each row in the rowset contains a set of columns that hold the data. To read the data, the client side of the connection must have code that has three nested loops—one for the innermost list of columns, one for the list of rows, and one for the list of recordsets. In most cases, there will be only one recordset, but TDS does allow multiple record-sets in a single response to the client. Both the Open Database Connectivity (ODBC) client library and the ActiveX Data Objects (ADO) client for SQL Server use TDS internally; therefore, you will see similar network traffic if you use those clients instead of ISQL/W.
In the data portion of frame 8, you will see the login name for JoeS, which is a standard login on SQL Server. If you were to open the capture file in NetMon, you would also see the password in clear text (or at least, unencrypted, XORed, and UNICODEed text).
NOTE One of the biggest security problems with the TCP/IP network library is that it does not encrypt users' passwords. SQL Server 7.0 and 2000 have remedies for this problem, but SQL Server 6.5 does not.
10 W2kClient SS65 TDS Login - (continued)
11 SS65 W2kClient TDS Response to frame 10 -Environment Change
12 W2kClient SS65 TDS SQL - exec sp_server_info 18
13 SS65 W2kClient TDS Response to frame 12 - Done in Procedure,...
14 W2kClient SS65 TDS SQL- set textsize 64512 select suser_name()
15 SS65 W2kClient TDS Response to frame 14 -Done, Column Name,
16 W2kClient SS65 TDS SQL - select @@microsoftversion
17 SS65 W2kClient TDS Response to frame 16 - Column Name,
18 W2kClient SS65 TDS SQL - select name from ...
19 SS65 W2kClient TDS Response to frame 18 - Column Name,...
20 W2kClient SS65 TDS SQL - select suser_name()
21 SS65 W2kClient TDS Response to frame 20 - Column Name, ...
Frames 10 through 21 show how ISQL/W queries SQL Server for various pieces of information about the server itself.
26 W2kClient SS65 TCP ....S., len: 0, src: 1301 dst: 1433
27 SS65 W2kClient TCP .A..S., len: 0, src: 1433 dst: 1301
28 W2kClient SS65 TCP .A...., len: 0, src: 1301 dst: 1433
29 W2kClient SS65 TDS Login -W2KCLIENT, JoeS, Microsoft ISQL/w
The next set of frames shows something odd. Frames 26 through 28 show the three-way handshake again, which indicates that ISQL/W has broken the original connection and opened a new one. Frame 29 shows where ISQL/W logs into SQL Server again, but this time it includes the client computer’s name.
31 W2kClient SS65 TDS Login - (continued)
32 SS65 W2kClient TDS Response to frame 31 -Environment Change,
33 W2kClient SS65 TDS SQL - exec sp_server_info 18
34 SS65 W2kClient TDS Response to frame 33 - Done in Procedure
35 W2kClient SS65 TDS SQL -set textsize 64512 use pubs
36 SS65 W2kClient TDS Response to frame 35 - Done
37 W2kClient SS65 TDS SQL - select suser_name()
38 SS65 W2kClient TDS Response to frame 37 - Column Name, ...
39 W2kClient SS65 TDS SQL - select @@microsoftversion
40 SS65 W2kClient TDS Response to frame 39 - Column Name, ...
41 W2kClient SS65 TDS SQL - select name from master..sysdatabases
42 SS65 W2kClient TDS Response to frame 41 - Column Name, ...
The last several frames show ISQL/W once again querying SQL Server for information about the server. What makes this second login odd is that I clicked the Connect button only once. All the packets shown in the preceding listing come from a single login. Unfortunately, I cannot tell you why there are two connections per login.
If you look at a trace of network traffic when a client uses the TCP/IP library, you will see that there is a large amount of human-readable data. The contents of character and text columns will be easily readable, and even 1- and 2-byte integers will be easy to convert. All commands are sent as text; therefore, you will be able to read the contents of SELECT, INSERT, UPDATE, and DELETE statements. That visibility is one of the failings of the TCP/IP network library; not only does it send user account names and passwords in clear text, but also it sends and receives data in moderately easily read format. When you are evaluating network libraries from a security perspective, you must consider this library to be the least secure option available when using SQL Server 6.5. That assessment will change for SQL Server 7.0 and 2000.
NOTE One potential solution would be to move down the OSI model and use IPSec to encrypt the data stream between the client and server. The main impediments to using IPSec will be the operating system and domain architectures. See the previously mentioned TechNet article for more details on implementing IPSec.
Two factors may override the problem with security, however. First, the TCP/IP network library is the fastest of all the libraries in terms of overall throughput. If you need raw speed, it is the best choice. Second, the TCP/IP network library requires the least support services of all the network libraries. If your users use a DNS name to connect to the server, the server needs a host record (aka an A record) in your DNS server. If you intend to use the IP address instead, you will need no other services. If you need the fastest configuration, as for a web site that uses a database to drive its content, the TCP/IP network library with the client using the IP address of the server will be the best choice. Just recognize that there is a sacrifice of security for that speed.
 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 
