A Security Roadmap - Options for Authentication
(Page 2 of 7 )
The place to start is authentication. When I started teaching classes on SQL Server, I discovered that most of my students did not realize that all interaction with SQL Server happens through a client application. As a service, SQL Server runs without a user interface. In fact, the only way you can change the server’s settings without using a client application is by setting command line parameters and/or registry settings.
Client authentication, therefore, is a critical piece of any security plan. Administrators usually do not need to worry about authentication because they are using Windows NT accounts or SQL Server accounts that grant them complete control over the system, but users are not—and should not be—so fortunate. That means your first decision, when designing a security plan, will be how your system’s users will validate their login information.
SQL Server 6.5 and 7.0 have two ways to authenticate logins: Windows NT authentication and SQL Server authentication, and SQL Server 2000 adds Kerberos and Active Directory authentication. Chapter 2 covers the details of how authentication works, so for now, let’s just concentrate on how these authentication modes fit into the picture.
Windows Authentication To understand Windows NT/2000 authentication, you have to understand how Windows NT represents a user’s permissions within the system. When a user logs in, whether she is sitting at the computer itself or is connecting to the system across the network, Windows NT creates an access token, which contains the user’s security identifier (SID) and a list of all the groups (both local and global) of which the user is a member. Each time a user attempts to open a protected resource, Windows NT compares the SID and group memberships in the access token to the access control list (ACL), which lists approved users for that resource.
User rights play a role here as well. For a Windows local login, a user must have the “Log on locally” user right, whereas network users require the “Access this computer from the network” user right. Those rights, of course, can be granted to any group of which the user is a member, including groups that have implicit membership, such as the Everyone local group.
The diagram in Figure 1-2 illustrates how a client authenticates using Windows NT authentication.
Figure 1-2. Windows NT authentication
In the client documentation, this kind of authentication is called a trusted connection, primarily because the only kinds of clients that can use it are the ones that Windows NT trusts—that is, other Windows clients. The main difference between Windows NT authentication and SQL Server authentication is that the Windows-based client knows how to encrypt the login credentials the way Windows expects, instead of sending the account and password across the network in clear text, as is the case with SQL Server authentication.
The process begins with the client application’s attempt to make a connection to the server. I cover how the client finds the server a little later when I discuss the SQL Server network libraries in Chapter 2. The network library used on the client affects both the way the client finds the server and the way the client sends data to the server. For the purposes of the discussion in this section, you can just assume that the client can find the server.
The process of sending data back and forth to the operating system is called interprocess communication, or IPC for short. IPC started as a way for one application to send data to another application on the same Windows NT machine, but it was expanded to allow an application to send data to another application on an entirely different computer across the network. In the process, the architects of Windows NT realized that because all clients must authenticate in Windows NT before they are allowed to do anything on the computer, IPC clients must have a way to authenticate when they attempt to connect across the network. Thus, at startup, Windows NT or 2000 creates a hidden system share named IPC$.
NOTE If you want to know more about IPC mechanisms and how Windows NT manages application and network security, you should consult Inside Windows NT by Helen Custer (Microsoft Press, ISBN: 155615481X), Advanced Windows, Third Edition by Jeffrey Richter (Microsoft Press, ISBN: 1572315482), and Programming Windows, Fifth Edition by Charles Petzold (Microsoft Press, ISBN: 157231995X). The first one is a must-read for all Windows NT administrators, and the latter two are must-reads for all Windows NT programmers. Windows NT administrators who know a little about programming can benefit from reading Advanced Windows too.
All clients wanting to log into Windows NT attempt to connect to IPC$ with a mechanism that is identical to connecting to a shared directory. Because IPC$ is a shared resource, attempting to connect to it triggers a login process on the server. In the process, the client operating system sends its security credentials to the server so that Windows can build an access token.
The account can be either in the server’s local security storage or in the domain’s security storage. If the user does not provide a domain account or if the domain provided is not one recognized by the server, Windows consults its local security storage to see if the account and password can be found there. If the account is a domain account, then Windows makes a connection to a domain controller and asks it to validate the account and password. If the domain controller approves the credentials, it sends back the list of domain global groups of which the account is a member. In both cases, the operating system always checks the local security storage and adds the local groups that have the login account and any domain global groups as a member. In the case of a domain account, this check is in addition to the check of the domain security database.
After authenticating the user’s Windows account, SQL Server receives a complete list of security identifiers for both the user’s Windows NT/2000 account and the local and global groups of which he is a member. The net result is that a user can gain access to SQL Server through one of the following:
- The user’s personal account
- The local operating system’s local groups (in the case of SQL Server running on a member server)
- The domain’s local groups (only in certain, special cases)
- The domain groups, including domain local, global, and universal groups
Once the operating system compiles the list of SIDs, SQL Server takes over the authentication process using a table containing login account information. Chapter 2 goes into detail on how SQL Server determines login privileges for versions 6.5, 7.0, and 2000.
 This chapter is from SQL Server Security Distilled, by Morris Lewis (Apress, 2004, ISBN: 1590592190). Check it out at your favorite bookstore today. Buy this book now.
|
Next: Managing Server Access Using Windows NT Groups >>
More Windows Security Articles
More By Apress Publishing
/ 2 
