A Security Roadmap - Managing Server Access Using Windows NT Groups
(Page 3 of 7 )
This is a good point at which to mention that Windows authentication is not limited to just user accounts in SQL Server 7.0 and 2000; both local and global groups can be granted login permission as well. In this case, instead of storing the SID for an individual account, SQL Server stores the SID for the group.
The effect of this approach is that you can manage access to your server through Windows NT or 2000 domain global groups, or by adding Windows users to local groups on the SQL Server itself. If the Windows NT or 2000 account administrators are also the SQL Server administrators, and if it makes sense to manage your SQL Server users’ access at the domain level, then this method greatly simplifies server access management. Rather than creating tens or even hundreds of login accounts, you can create several groups that represent the different groups of people who will be using the system and place members in the groups at the domain server.
If you need to deny access to a specific member of a Windows NT group, you have two options. First, you can deny access to the user’s Windows NT account explicitly. Second, you can create a single domain group, deny that group access to SQL Server, and place any users who may not access the server in that group. Because having only one of her access token SIDs denied means the user cannot log in, you need only one group for the entire organization.
From this point on, normal SQL Server permissions checking takes over. In Chapter 4, you will see that SQL Server 7.0 and 2000 use the contents of the access token when checking permissions at the server and database level, but other than that, Windows is out of the picture. Now, let’s turn our attention to what happens when the client logs in with an account maintained by SQL Server.
SQL Server Authentication You can think of SQL Server authentication as the lowest common denominator for authentication, because it supports logins from all clients, no matter what operating system they use. SQL Server authentication supports connections from clients that are
- Running all versions of Windows
- Using the TCP/IP network protocol (for example, Unix or Novell NetWare clients)
- Using the AppleTalk network protocol (for example, the iMac)
- Using the Banyan Vines network protocol
The differences between SQL Server authentication and Windows NT authentication are as follows: :
- The request for login comes directly to SQL Server.
- SQL Server maintains the internal list of permitted logins, and the login request does not use Windows NT password encryption.
Once logged in, granting permissions is generally the same for both SQL Server and Windows authenticated logins. In SQL Server 6.5, there are no differences in the way you assign permissions to either type of login. (I cover all the options for assigning permissions in SQL Server 6.5 in Chapter 3.) In SQL Server 7.0 and 2000, the only difference between SQL Server and Windows authenticated logins is that the SQL Server login does not carry any Windows NT group or account information with it, which means that it cannot gain any additional permissions granted to Windows groups. Instead, it will gain its permissions from server and database roles, as I discuss in Chapter 4.
 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: Kerberos and Active Directory Authentication >>
More Windows Security Articles
More By Apress Publishing
/ 2 
