Authenticating Logins - Logins Using Named Pipes
(Page 16 of 24 )
The login process for SQL Server 7.0 and 2000’s version of named pipes is the same as it was for SQL Server 6.5, primarily because the RPCs that implement the named pipes IPC in Windows 2000 are backward compatible with Windows NT. As a result, the following network trace will be very similar to the trace for SQL Server 6.5:
Frame Src Addr Dst Addr Prot Description
15 W2KCLIENT SS7_NT_SRV NBT SS: Session Request, Dest: SS7_NT_SRV
16 SS7_NT_SRV W2KCLIENT NBT SS: Positive Session Response, Len: 0
17 W2KCLIENT SS7_NT_SRV SMB C negotiate, Dialect = NT LM 0.12
18 SS7_NT_SRV W2KCLIENT SMB R negotiate, Dialect # = 5
In frame 15, you can 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 NTLM protocol dialect they will use to communicate. They settle on dialect 5, because that is the highest version Windows NT supports. Remember, W2KCLIENT is running Windows 2000 Professional and SS7_NT_SRV is running Windows NT 4.0 Server. If both computers were running Windows 2000 or XP, you would see them agree to use NTLM dialect 6, which corresponds to NTLM version 2, the standard on Windows 2000.
19 W2KCLIENT SS7_NT_SRV SMB C session setup & X, Username = FrankB
...
22 SS7_NT_SRV NT4DC SMB C NT create & X, File = \NETLOGON
23 NT4DC SS7_NT_SRV SMB R NT create & X, FID = 0x800f
24 SS7_NT_SRV NT4DC MSRPC c/o RPC Bind:UUID 12345678-1234-ABCD-EF0
25 NT4DC SS7_NT_SRV MSRPC c/o RPC Bind Ack: call 0x2 assoc grp
26 SS7_NT_SRV NT4DC R_LOGON RPC ... req: logon:NetrLogonSamLogon(..)
27 NT4DC SS7_NT_SRV R_LOGON RPC ... resp: logon:NetrLogonSamLogon(..)
28 SS7_NT_SRV W2KCLIENT SMB R session setup & X, and R tree connect
29 W2KCLIENT SS7_NT_SRV SMB C NT create & X, File = \sql\query
30 SS7_NT_SRV W2KCLIENT SMB R NT create & X, FID = 0x802
31 W2KCLIENT SS7_NT_SRV TDS Login - , joes, 000002bc, MS ISQL/w
32 SS7_NT_SRV W2KCLIENT SMB R write & X, Wrote 0x200
33 W2KCLIENT SS7_NT_SRV TDS Login - (continued)
34 SS7_NT_SRV W2KCLIENT SMB R write & X, Wrote 0x4c
35 W2KCLIENT SS7_NT_SRV SMB C read & X, FID = 0x802
36 SS7_NT_SRV W2KCLIENT TDS Response to frame 33 - Environment Chg
37 W2KCLIENT SS7_NT_SRV TDS SQL - exec sp_server_info 18
...
39 SS7_NT_SRV W2KCLIENT TDS Response to frame 37 - Done in Procedure
40 W2KCLIENT SS7_NT_SRV TDS SQL - ... select suser_name()
41 SS7_NT_SRV W2KCLIENT TDS Response to frame 40 - Done
42 W2KCLIENT SS7_NT_SRV TDS SQL - select @@microsoftversion
43 SS7_NT_SRV W2KCLIENT TDS Response to frame 42
44 W2KCLIENT SS7_NT_SRV TDS SQL select name from master.dbo.spt_values
45 SS7_NT_SRV W2KCLIENT TDS Response to frame 44
46 W2KCLIENT SS7_NT_SRV TDS SQL - select suser_name()
47 SS7_NT_SRV W2KCLIENT TDS Response to frame 46
48 W2KCLIENT SS7_NT_SRV SMB C close file, FID = 0x802
49 SS7_NT_SRV W2KCLIENT SMB R close file
50 W2KCLIENT SS7_NT_SRV SMB C NT create & X, File = \sql\query
51 SS7_NT_SRV W2KCLIENT SMB R NT create & X, FID = 0x803
52 W2KCLIENT SS7_NT_SRV TDS Login - W2KCLIENT, joes, 000002bc, MS ISQL
53 SS7_NT_SRV W2KCLIENT SMB R write & X, Wrote 0x200
54 W2KCLIENT SS7_NT_SRV TDS Login - (continued)
55 SS7_NT_SRV W2KCLIENT SMB R write & X, Wrote 0x4c
Going back to the network trace, you will see in frame 19 where W2KCLIENT requests a SMB session on SS7_NT_SRV using FrankB’s account. In frame 22, SS7_NT_SRV 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 SS7_NT_SRV asking the domain controller to validate FrankB’s domain account and password. In frame 27, SS7_NT_SRV 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 process 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.
Just as in the SQL Server 6.5 authentication trace, 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. This is one situation in which SQL Server 6.5, 7.0, and 2000 all exhibit the same behavior because Windows NT and 2000 must authenticate the user’s identity before he can use operating system functions to communicate with SQL Server.
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 one that may cause problems is the “Access this computer from the network” user right. 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. Just remember that if the user’s Windows account does not have permission to access Windows across the network, it does not matter whether or not she has a valid login account in SQL Server.
As you can probably guess, Windows authenticated logins are a byproduct of the authentication the operating system does before SQL Server sees the login. Any authenticated connection to Windows NT/2000 causes Windows to build a data structure known as an access token. The access token contains not only the SID for the user’s Windows account but also the SIDs of all the groups, both local and domain, of which the user is a member. It also contains other information, but SQL Server only uses the SID to determine if a user can log in. You will look at how SQL Server 7.0 and 2000 use SIDs in more detail a little later in this chapter.
 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: The Multiprotocol Library in SQL Server 2000 >>
More MS SQL Server Articles
More By Apress Publishing
/ 17 
