Authenticating Logins - Logins Using Named Pipes
(Page 8 of 24 )
The Named Pipes network library gets its name from the fact that it uses a fairly old interprocess communication (IPC) mechanism known as named pipes. IPC is simply the exchange of data between two programs. In earlier operating systems, it occurred between two programs running on the same machine, but in Windows NT and 2000, the term usually applies to the communication between applications running on two different machines. Named pipes simulate files, and to the applications, the communication process looks exactly like reading and writing to a file on the disk. For SQL Server 6.5, the client sends a query by writing it to the named pipe. The server reads the query from the file and writes the results back to the named pipe. The client receives the results by reading from the named pipe.
Figure 2-3 illustrates how named pipes work.
Figure 2-3. Using named pipes
Internally, the named pipe works like two one-way communications channels, but the key difference between it and other mechanisms is that the operating system can choose to buffer the data either to a temporary file on the disk or to a memory buffer until the receiver has time to read from the pipe. This option works well for SQL Server, because it allows the client and server to send data at their own speed, and it allows SQL Server to handle multiple users without losing any data, even if all clients send data at exactly the same instance in time.
Once again, to see what’s happening in the login process, you’ll look at the contents of the packets:
Frame Src Addr Dst Addr Prot Description
15 W2KCLIENT SS65 NBT SS: Session Request, Dest: SS65
16 SS65 W2KCLIENT NBT SS: Positive Session Response, Len: 0
17 W2KCLIENT SS65 SMB C negotiate, Dialect = NT LM 0.12
18 SS65 W2KCLIENT SMB R negotiate, Dialect # = 5
Windows NT and 2000 implement named pipes as RPCs using the NetBIOS protocol. In frame 15, you will see where the client issues a NetBIOS request (as indicated by the NBT protocol) to start a session on the server on which SQL Server runs. The server accepts the request, and then the two computers negotiate which dialect of NTLM protocols they will use to communicate. They settle on version 5 because that is the highest version SS65 supports. Remember, W2KCLIENT is running Windows 2000 Professional and SS65 is running Windows NT 4.0 Server. If both computers were running Windows 2000 or XP, you would see them agree to use NTLM version 2, which is the standard on Windows 2000.
The reason you should care which version they use is because the NTLM authentication protocol has a serious flaw that can let someone sniffing the network determine a user’s password. The full explanation of the problem is outside the scope of this book, but the main point is that the authentication process is no longer secure against a determined attacker. NTLM version 2, which was introduced in Windows 2000, fixes the security problems found in NTLM and the older LAN Manager used on older versions of Windows.
NOTE Windows 2000 can be configured to require NTLM version 2 and to refuse all requests that attempt to use earlier versions. For this reason alone, you should give serious consideration to running SQL Server on Windows 2000.
19 W2KCLIENT SS65 SMB C session setup & X, Username = FrankB
Going back to the network trace, you will see frame 19 where W2KCLIENT requests an SMB session on SS65 using FrankB’s account. SMB stands for Server Message Block, and it is the NetBIOS protocol used to handle IPC. If you were to look inside the packet, you would see that the pipe opened in frame 19 is \\IPC$. IPC$ is a hidden pipe used for authentication of IPC connection attempts. Trying to open it forces Windows NT/2000 to start authenticating the Windows account that is making the request, which in this case is NTTEST\FrankB.
22 SS65 NT4DC SMB C NT create & X, File = \NETLOGON
23 NT4DC SS65 SMB R NT create & X, FID = 0x800f
24 SS65 NT4DC MSRPC c/o RPC Bind:UUID 12345678-1234-ABCD-EF0
25 NT4DC SS65 MSRPC c/o RPC Bind Ack: call 0x2 assoc grp
26 SS65 NT4DC R_LOGON RPC ... req: logon:NetrLogonSamLogon(..)
The next frame, 22, begins the interesting part of the process. Between frames 19 and 22, SS65 looks for and finds the domain controller, NT4DC. In frame 22, SS65 opens the \NETLOGON named pipe on the domain controller. This is the way a server can check the authentication credentials for a domain account. The next few packets show SS65 asking the domain controller to validate FrankB’s domain account and password.
27 NT4DC SS65 R_LOGON RPC ... resp: logon:NetrLogonSamLogon(..)
28 SS65 W2KCLIENT SMB R session setup & X, and R tree connect
29 W2KCLIENT SS65 SMB C NT create & X, File = \sql\query
30 SS65 W2KCLIENT SMB R NT create & X, FID = 0x802
In frame 27, SS65 gets a positive response from NT4DC, and then in frame 28 it tells W2KCLIENT that it accepts the SMB session request. In frame 29, the client opens the \sql\query named pipe. After the server accepts the request in frame 30, the mechanism is the same as it was for the TCP/IP network library, including the second login, which seems to be an idiosyncrasy of ISQL/W.
31 W2KCLIENT SS65 TDS Login - , joes, 000002bc, MS ISQL/w
32 SS65 W2KCLIENT SMB R write & X, Wrote 0x200
33 W2KCLIENT SS65 TDS Login - (continued)
34 SS65 W2KCLIENT SMB R write & X, Wrote 0x4c
35 W2KCLIENT SS65 SMB C read & X, FID = 0x802
36 SS65 W2KCLIENT TDS Response to frame 33 - Environment Chg
37 W2KCLIENT SS65 TDS SQL - exec sp_server_info 18
...
39 SS65 W2KCLIENT TDS Response to frame 37 - Done in Procedure
40 W2KCLIENT SS65 TDS SQL - ... select suser_name()
41 SS65 W2KCLIENT TDS Response to frame 40 - Done
42 W2KCLIENT SS65 TDS SQL - select @@microsoftversion
43 SS65 W2KCLIENT TDS Response to frame 42
44 W2KCLIENT SS65 TDS SQL select name from master.dbo.spt_values
45 SS65 W2KCLIENT TDS Response to frame 44
46 W2KCLIENT SS65 TDS SQL - select suser_name()
47 SS65 W2KCLIENT TDS Response to frame 46
48 W2KCLIENT SS65 SMB C close file, FID = 0x802
49 SS65 W2KCLIENT SMB R close file
50 W2KCLIENT SS65 SMB C NT create & X, File = \sql\query
51 SS65 W2KCLIENT SMB R NT create & X, FID = 0x803
52 W2KCLIENT SS65 TDS Login - W2KCLIENT, joes, 000002bc, MS ISQL
53 SS65 W2KCLIENT SMB R write & X, Wrote 0x200
54 W2KCLIENT SS65 TDS Login - (continued)
55 SS65 W2KCLIENT SMB R write & X, Wrote 0x4c
Now, let’s pause to consider what has just happened. First of all, notice that the client uses the JoeS standard login account for SQL Server, but Windows uses FrankB’s Windows account credentials to decide if the client is allowed to connect to SQL Server. What is happening is that FrankB is logged into the client computer, and he is logging into SQL Server using the JoeS account. Because Windows NT/2000 requires authentication of the user’s identity before it allows someone to open a named pipe, the authentication process happens at the operating system level before SQL Server even sees the request. There is simply no way for SQL Server to intercept the request or to tell the operating system not to authenticate the user.
What this means is that even for Standard logins, the user’s Windows account and password must first be validated by the operating system. In this case, I used a domain account, but it could have been an account in the local server’s security database. What you would see if you opened the trace in NetMon is that the client computer sent the domain name along with the account name. Windows NT will look in its local security database for an account matching the name sent by the client before it contacts the domain controller; therefore, if FrankB had an account on SS65 with the same password, Windows NT would use that account. If the account does not exist, or if the password is different, the operating system will contact a domain controller for verification. Windows 2000 uses a similar algorithm, but I have found instances in which it always contacts the domain controller.
In addition to authenticating the user’s Windows account, Windows will also check the account against its list of user rights on the local server. The user right that may cause problems is “Access this computer from the network”. If the user’s Windows account does not have this right, Windows refuses the connection. By default, the Everyone local group has the right; however, you should consider replacing that group with the Authenticated Users group because it ensures only authenticated accounts can access SQL Server. You can also use this right to limit access to the server to a specific set of users.
The end result is that if you use the Named Pipes network library, users will have to pass through two checkpoints: one for the operating system and one for SQL Server. If you use Standard Security or Mixed Mode, the user could even authenticate with two different accounts; thus, it is better to use Integrated Security where possible. Just remember that if the user’s Windows account does not have permission to access the database server across the network, it does not matter whether or not she has a valid SQL Server login account.
 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: User Level vs. Full Control Logins >>
More MS SQL Server Articles
More By Apress Publishing
/ 18 
