Authenticating Logins - User Level vs. Full Control Logins

(Page 9 of 24 )

As you can probably guess, Integrated Security is 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 SIDs to determine if a user can log in.

As I mentioned at the beginning of the section, SQL Server 6.5 uses the rather unusual method of basing login access on the security permissions a user has on a key in the registry. SQL Server compares the list of SIDs in the access token to the list of SIDs on the registry key. If an SID has Read permission on the key, it considers the login to be a user-level login. If an SID has Full Control permission on the key, it considers the login be a system administrator (sa) login. If a user can somehow receive both Read and Full Control permissions, SQL Server grants him the higher level sa login. You can use the system stored procedure xp_logininfo to find out how the user achieved an administrator login.

For user-level logins, SQL Server performs a second check on syslogins to see if it contains a row with the user’s Windows account name. Remember that SQL Server does not allow the \ domain separator character in its login names, so you will have to consult the list of replacement characters to determine what the login name will be. (SQL Server Books Online has the complete list of characters.) The Security Manager tool will also show you the name it created if you used it to grant login privileges to a Windows account or group. If SQL Server does not find a login account in syslogins, it logs in the user with the default account, which is guest.

Disabling the Guest Account

If guest does not have login privileges, the user’s login request is refused. You can use that behavior to your advantage if you want to grant login permissions to individual members of a group without granting access to all members. Granting Read permission on the registry key grants all members of the group the privileges of at least the guest login account. As you will see in the next two chapters, guest logins can gain guest privileges in databases that have the guest user account enabled. Granting access to what is essentially an anonymous user is probably not appropriate for most situations; therefore, you will probably want to disable the guest login account. Doing so allows you to grant user-level login rights to individual members of a group and refuse access to all the other members.

Eliminating Trusted Connections with Standard Security

Configuring SQL Server to use just Standard Security does not eliminate Integrated Security logins if the client requests a trusted connection. Even in Standard Security mode, SQL Server checks the permissions on the registry key if the user uses a trusted connection to log in. To eliminate trusted connections entirely, you must remove all permissions from the registry and then assign permissions to two empty, local groups. Here are the steps you need to follow:

1. Create two local groups named SQLUsers and SQLAdmins that contain no users.

2. Use SQL Security Manager to grant the System Administrator privilege to the SQLAdmins group. Grant the User privilege to the SQLUsers group.

3. Use SQL Security Manager to revoke all privileges from all other groups.

Because there are no users in the groups, no access token will have the groups’ SIDs, and no user will be able to log in using a Windows NT/2000 account. Just be aware that this setup can cause problems in some configurations that require trusted connections, such as replication.

Named Pipes does have one nice little feature that can be useful if your network is down. If you use the name "(local)" instead of the network name to connect to SQL Server, ISQL/W does not send any packets to the network. All communications stay on the local server. This is why it is always a good idea to leave Named Pipes enabled. If something happens to the network, you can still log into SQL Server using the local console. 

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: SQL Server 6.5 Named Pipes Login Summary >>
 

More MS SQL Server Articles
More By Apress Publishing

Comments On: Authenticating Logins

>>> Be the FIRST to comment on this article!