Authenticating Logins - Security

(Page 5 of 24 )

Standard Security

Standard Security just means that SQL Server manages the accounts and passwords itself. The Master database contains a table named syslogins, which stores account names and passwords for all logins. Table 2-1 presents the schema definition for this table.

Column	Data Type	Description
suid	smallint	Server user ID
status	smallint	Reserved
sccdate	datetime	Reserved
sotcpu	int	Reserved
sotio	int	Reserved
spacelimit	int	Reserved
simelimit	int	Reserved
sesultlimit	int	Reserved
sbname	varchar(30)	Name of user’s default database
name	varchar(30)	Login ID of user
password	varchar(30)	Encrypted password of user (may be NULL)
Language	varchar(30)	User’s default language (NULL for us_english)

Table 2-1. Schema Definition

The process of logging in follows these steps:

1. SQL Server 6.5 receives the account and password in the login request.

2. The MSSQLSERVER service issues a query similar to “SELECT * FROM syslogins WHERE name = @account AND password = @password”.

3. If the query returns a row, the service grants the user’s login request. If not, the service terminates the login process.

If the login request is successful, SQL Server builds in memory an internal structure. This structure holds information about the user’s session, including, among other things, his system user identifier (SUID) from syslogins. The SUID is simply a 16-bit integer assigned during the account creation process. It serves as a primary key for the syslogins table and as a unique identifier for the user. The SUID for the sa account is 1, and each new user gets his SUID by adding 1 to the largest SUID below 16382 currently in the system. For example, on my test system, my first two logins received 10 and 11 as their SUIDs.

Integrated Security

The term “integrated” in the Integrated Security mode comes from the idea that SQL Server logins have been integrated into the Windows NT/2000 authentication scheme. Administrators have a choice of granting server access either to a user’s Windows NT/2000 account or through membership in a Windows NT/2000 group. The following is a complete list of the options, but the rule of thumb is that the account or group may be anything that can be authenticated by the server running SQL Server:

• Local accounts on the server running SQL Server

• Local groups on the server running SQL Server

• NT 4.0 domain accounts in the server’s NT 4.0 domain

• NT 4.0 domain global groups in the server’s NT 4.0 domain

• NT 4.0 domain accounts and global groups in a domain trusted by the server’s NT 4.0 or Windows 2000 domain

• Windows 2000 domain accounts in the server’s Windows 2000 domain

• Windows 2000 domain local and global groups in the server’s Windows 2000 domain

• Windows 2000 domain accounts and global groups in a domain trusted by the server’s NT 4.0 or Windows 2000 domain

• Windows 2000 forest universal groups in the server’s forest, if SQL Server is running on a version of Windows 2000 server

Microsoft’s implementation of Integrated Security in version 6.5 is a little strange, however. What is actually happening is that SQL Server really has two “roles” for Integrated Security logins: user and administrator. It determines which role the user should have by looking at the permissions on the HKLM\Software\ Microsoft\MSSQLServer\MSSQLServer registry key. Read permission on the key indicates the user role, and Full Control permission indicates the administrator role.

NOTE You can look at registry key permissions using the regedt32.exe tool that ships with Windows NT and 2000.

Additionally, if a user is a member of any group that has Full Control permission, that user is automatically elevated to the administrator role. For example, assume Judy Smith’s account JudyS is a member of the Domain Users group and the Administrators local group. If Domain Users has Read permission and Administrators has Full Control permission, then Judy Smith gets administrator privileges in SQL Server.

After determining the role, SQL Server maps the Windows account to a SQL Server account. Administrators all map to the sa account, regardless of the account used to login. For users, the server looks in syslogins for a record that matches the Windows account name using a query similar to "SELECT * FROM syslogins WHERE name = @accountname". If it finds a matching row, it maps the Windows account to that login account. If it doesn’t find a match, it maps the account to the default login, which is the guest account in the standard installation. Once it determines where to map the account, SQL Server builds an in-memory structure to hold session information just as it does for a Standard Security login. In fact, after the login process completes, it will be nearly impossible to tell the difference between a SQL Server Standard login and an Integrated login.

One point does bear mentioning. There is no mechanism for mapping a SQL Server login account to a Windows NT/2000 group. Members of the group can gain access to the server through permissions assigned to the group, but the name of the SQL Server login account they use must match their Windows account name.

If agroup member does not have a login account matching her Windows account, the login account will map to the default account. Remember, this caution only applies to the user role because all administrators map to the sa 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: Tracing Login Network Traffic >>
 

More MS SQL Server Articles
More By Apress Publishing

Comments On: Authenticating Logins

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