Chapter 10 - Implementing Security

Article
01/28/2010

Introduction

This chapter introduces Microsoft® SQL Server™ administrators and developers to the new security features of SQL Server 2000. New features are outlined, and a detailed discussion is provided about how to best implement security in a Microsoft Windows® 2000 domain environment. Source code examples are included for developers who want to implement the security model immediately.

For those who will be upgrading servers from SQL Server version 7.0 and earlier, this chapter explains the security part of the upgrade, and provides notes for those who know how security was performed in SQL Server 6.5 and earlier.

This chapter does not cover security in Microsoft English Query, SQL Server 2000 Meta Data Services, or SQL Server 2000 Analysis Services. For more information about those components, see SQL Server Books Online.

New Security Features

This section examines the new security features of SQL Server 2000, and gives an overview of how security has been enhanced in this release.

Secure Setup

SQL Server 2000 Setup is secure out of the box. When you run SQL Server Setup in any edition (except Microsoft SQL Server Desktop Engine), you can now select Windows Authentication Mode, which is the default. For more information about authentication modes, see "Authentication Modes" in this chapter.

Windows Authentication Mode is more secure than Mixed Mode authentication. If you select Mixed Mode, you will need to set a password for the system administrator (sa) login to SQL Server. Optionally, you can set a blank password, but this is not recommended because your system will be vulnerable to attack.

When installing SQL Server on a Microsoft Windows NT® 4.0 or Windows 2000 operating system using the NTFS file system, SQL Server Setup limits access to the directories into which SQL Server installs. Only the service account(s) selected for SQL Server services and the built-in Administrators group can access those directories during setup. By default, the directory is C:\Program Files\Microsoft SQL Server\MSSQL. Access to the SQL Server registry keys (starting at HKLM\Software\Microsoft\ MSSQLServer, or HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL$InstanceName for a named instance) is also restricted to the service account(s) selected during SQL Server Setup.

SQL Server 2000 Desktop Engine Setup

The Microsoft SQL Server 2000 Desktop Engine Setup program installs on Windows NT 4.0 and Windows 2000 operating systems in Windows Authentication Mode by default. On Microsoft Windows 98 or Microsoft Windows Millennium (Windows Me) operating systems, Windows Authentication is not available, so Mixed Mode is selected. To change the installation default to Mixed Mode, specify the SECURITYMODE=SQL option at the command prompt for Setup (or in the .ini file). For more information, see SQL Server Books Online.

As noted in the Readme.txt file for SQL Server 2000 and in SQL Server Books Online, two parameters, USEDEFAULTSAPWD and SAPASSWORD, are ignored by the final version of Desktop Engine Setup. The sa login password used when the Desktop Engine is set up with Mixed Mode authentication will always be blank, and it should be changed immediately after installation.

C2 Security Evaluation Completed

SQL Server 2000 meets the C2 security certification for the evaluated configuration from the US government. C2 auditing is necessary if you are running a C2-certified system. A C2-certified system meets a government standard that defines the security level. To have a C2-certified installation of SQL Server, you must configure SQL Server in the evaluated C2 configuration. For more information about the configuration for a C2-compliant system, see https://www.microsoft.com.

Kerberos and Delegation in Windows 2000 Environments

Kerberos is the primary authentication mechanism on Windows 2000 networks. Delegation is the ability to pass security credentials across multiple computers and applications. As users access different computers and applications, the security credentials of users are preserved. SQL Server 2000 fully supports Kerberos, including the ability to accept delegated Kerberos tickets and delegate these tickets further (when running on the Windows 2000 operating system) with Windows 2000 domain controllers and Active Directory™. This affects remote stored procedures as well as distributed queries. For more information about Kerberos and Windows 2000 security, see https://www.microsoft.com/windows2000/.

To configure delegation, all servers that you are connecting to must be running Windows 2000 with Kerberos support enabled, and you must use Active Directory. The following must be set in Active Directory for delegation to work:

Account is sensitive and cannot be delegated. This option must not be selected for the user requesting delegation.
Account is trusted for delegation. This option must be selected for the service account of SQL Server.
Computer is trusted for delegation. This option must be selected for the server running an instance of SQL Server.

To use security account delegation, SQL Server must have a Service Principal Name (SPN) assigned by the Windows 2000 account domain administrator. The SPN must be assigned to the service account of the SQL Server service on that particular computer. Delegation enforces mutual authentication. The SPN is necessary to prove that SQL Server is verified on the particular server, at the particular socket address by the Windows 2000 account domain administrator. Your domain administrator can establish an SPN for SQL Server. For more information about the setspn utility, see the Windows 2000 Resource Kit.

To create an SPN for SQL Server 2000

Run the following command:

setspn -A MSSQLSvc/Host:port serviceaccount

For example: 

<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">setspn -A MSSQLSvc/server1.redmond.microsoft.com sqlaccount

You must use the TCP/IP Sockets Network Library for delegation to work. You cannot use Named Pipes because the SPN targets a particular TCP/IP socket. If you are using multiple ports, you must have a SPN for each port.

You can also enable delegation by running under the LocalSystem account. SQL Server 2000 self-registers at service startup and automatically registers the SPN. This option is easier than enabling delegation using a domain user account; however, when SQL Server 2000 shuts down, the SPNs will be unregistered for the LocalSystem account.

Security Auditing

One of the requirements for the US government C2 certification is a security auditing capability. SQL Server 2000 has a fully functional audit mechanism built into the product. This audit mechanism has several components, each of which is described here. When put together, these components allow you to track any permissions usage of any kind within SQL Server 2000.

SQL Trace

SQL Trace is the name given to the server-side components of the auditing mechanism. Auditing has been added to the same mechanism used in SQL Server 7.0 to provide performance information about SQL Server. Performance information is still returned, as well as audit information, but the interface has been completely redesigned in SQL Server 2000. All SQL Server 7.0 extended stored procedures have been replaced. For information about the new stored procedures used for security auditing, see SQL Server Books Online.

Each time an auditable security event occurs inside the SQL Server relational or storage engine, the event engine (SQL Trace) is notified. If a trace is currently enabled and running that would capture the event that was generated, the event is written to the appropriate trace file.

For information about how to enable traces both for ordinary security audits and C2-specific auditing, see SQL Server Books Online.

SQL Profiler

SQL Profiler is the graphical utility that allows you to view audit trace files and perform selected actions on them. You can search through the files, save the files out to a table, and create and configure trace definitions. SQL Profiler is a client to SQL Trace, and you do not need to have SQL Profiler running to perform a security audit.

C2-Mode Auditing

SQL Server 2000 has been certified as C2 compliant. One of the requirements of being in the C2-evaluated configuration is that C2-style auditing is enabled. C2 auditing has predetermined selections that determine which events are audited (all security events), which data columns are captured (all that might have information from these events), and other fixed settings. Each setting is documented in the Trusted Facilities Manual for SQL Server 2000, available at https://www.microsoft.com/Downloads/.

Elimination of the SQLAgentCmdExec Proxy Account

SQL Server 2000 has eliminated the creation of the SQLAgentCmdExec account. In SQL Server version 7.0 and earlier, SQL Server Agent jobs that were owned by logins without system administrator permissions could access Windows resources, but they did so using a proxy account known as SQLAgentCmdExec. This was a Windows NT 4.0 and Windows 2000 user account created locally on the computer on which SQL Server was installed during setup.

By default, the ability of non-system administrators to access resources outside of SQL Server has been disabled. However, when enabling a proxy account with SQL Server 2000, you can now specify a domain user account. This allows users who are not system administrators to access network resources instead of resources local to the computer on which you installed SQL Server.

Server Role Enhancements

SQL Server 2000 includes minor enhancements to fixed server roles. For information about using fixed server roles, see "Predefined Roles" in this chapter.

bulkadmin

bulkadmin is a new role in SQL Server 2000. Membership in this role allows a login to run the BULK INSERT command. Users who are members of this group can load data from any file on the network and from any computer the server is running on, as long as the SQL Server service account has access to the data. Membership should be considered carefully. Members of this role still must have the INSERT permission on any table they want as the target of the BULK INSERT command. Membership in bulkadmin only grants permission to execute the BULK INSERT command and the right to access files during the execution of this command.

securityadmin

Members of the securityadmin role can change the passwords of SQL Server Authentication mode logins. The exception to this is that the passwords of sysadmin fixed-server role members cannot be reset. For example, people who work in internal technical support, who do not need full system administrator access to SQL Server, might be members of this role.

serveradmin

The serveradmin role has been modified in the area of server-wide messages. Membership in this role now allows a login to execute sp_addmessage, sp_dropmessage, and sp_altermessage.

Encryption

Network Encryption Using SSL/TLS

SQL Server 2000 now automatically supports encryption of data and other network traffic as it travels between the client and server systems on a network. The encryption strength depends on the encryption capabilities authorized by the certificate installed for SQL Server and the cryptographic capabilities of the client and the server.

The certificate selected for SQL Server must be assigned to the name of the server, in the form of the fully qualified Domain Name System (DNS) server name (for example, SQLServer.Redmond.corp.Microsoft.com). The certificate must be valid for server authentication. Log in to SQL Server as the SQL Server service account, obtain the certificate (from either an internal certificate authority or a trusted third-party provider), and then install it on the server in the location suggested when you import the certificate.

During any login attempt, if a certificate is present on the server and is usable (that is, it is valid for server authentication and has the DNS name of the computer as the subject name in the certificate) all login-related packets will be encrypted. This happens automatically, and as long as the certificate is installed, no additional server configuration is required.

Client-Requested Encryption

The client can request encryption of all data traffic to SQL Server. This option is set using the Client Network Utility (using the Force Protocol Encryption option), and it applies to all outbound connections from that computer. The Client-Requested Encryption option also prevents access to SQL Server 7.0 and earlier, as well as any SQL Server 2000 server that does not have a valid certificate.

You can also set this option programmatically with the Encrypt=yes option in the connection string of your OLE DB or ODBC connection to a database server.

Server-Requested Encryption

Encryption can be required on the server if the database administrator requests it. This option is set using the Server Network Utility (using the Force Protocol Encryption option). Setting the Server-Requested Encryption option guarantees that all network traffic to SQL Server will be encrypted. If a client is unable to negotiate encryption with SQL Server, the connection will be terminated.

Encrypted File System Support on Windows 2000

SQL Server 2000 works on Windows 2000 if you use Encrypting File System (EFS), which is part of Windows 2000, to protect data files.

You must encrypt the files using the service account of SQL Server, and if you change to service account, you must re-encrypt the files using the new account. (To do this, unencrypt the files, change the service account for SQL Server services, and then re-encrypt the files with the new service account.) If you do not update the encryption when you change the service account, SQL Server may not be able to start, because it will not be able to decrypt the files that were encrypted with the previous service account's credentials.

Server-Based Encryption Enhanced

All server-based encrypted data (passwords, encrypted stored procedures, and so on) now use CryptoAPI. This ensures more robust and secure storage of protected items within SQL Server.

DTS Package Encryption

Data Transformation Services (DTS) packages are now encrypted using CryptoAPI. All packages are encrypted, regardless of whether or not a password is supplied.

Password Protection

Backups and Backup Media Sets

SQL Server 2000 allows you to specify a password either for an individual backup or for a backup media set. Without this password, you will be unable to restore the backup. This allows you to protect your backups from unauthorized restores.

The data is not encrypted, so a program not using the Microsoft Tape Format can ignore the password and give you access to the data within the backup. All SQL Server restore mechanisms use the password.

SQL Server Enterprise Manager

In SQL Server 2000, passwords for authenticated logins are always encrypted using CryptoAPI.

Service Account Changes Using SQL Server Enterprise Manager

When you change the service account for SQL Server services (SQL Server or SQL Server Agent) using SQL Server Enterprise Manager, the utility resets File and Directory permissions (when data is stored in NTFS). The utility also resets the registry key permissions. The new permissions are added, and the previous service account remains, as well as the built-in administrators group. The password is reset in the services database as if you reset the account information in Control Panel in Services, and then the appropriate Windows NT 4.0 or Windows 2000 security permissions are granted to the newly selected service account. Finally, the new service account is made a sysadmin fixed server role member in SQL Server.

SUID Column

The SUID column has been removed in SQL Server 2000. In SQL Server 7.0, it was superseded by the SID column, but it was preserved for backward compatibility. The sysalternates table has also been removed, because it contained only relationships between SUIDs.

The SUID column was present in the following system tables in earlier releases:

sysdatabases
syslogins
sysremotelogins
sysusers
sysprocesses
sysalternates

Security Model

To implement security in the most practical ways on SQL Server 2000, it is important to understand how the design team expected the users to implement the security model. The design team saw two basic ways to implement security in SQL Server 2000, both taking advantage of the power of users and groups in

Windows 2000. The first uses local groups, and the second uses roles. (Universal groups in Windows 2000 are also supported.)

The local group-based method follows this general outline:

Users in each domain are assigned to Windows global groups.
The Windows global groups from the various domains are placed into a Windows local group.
The Windows local group is granted rights to log in to SQL Server 2000.
The Windows local group is granted access rights to the appropriate databases. This Windows local group may not be the same one as was used for granting login rights in Step 3. Therefore, Steps 1 and 2 are often repeated to group the users by their required access permissions.
The Windows local group is assigned permissions on the specific database objects.

Roles reduce the requirements of grouping users within Windows, by grouping the users within SQL Server 2000. The role-based approach is similar to the local group-based approach, except that multiple Windows global and local groups would probably not be created in this scenario. Also, when roles are used to assign object permissions, individuals must still be granted permissions on the server and the database by following the steps outlined here.

Users in each domain are assigned to Windows global groups.
The Windows global groups from the various domains are placed into a Windows local group.
The Windows local group is granted rights to log in to SQL Server 2000.
The Windows local group is granted access rights to the appropriate databases. This Windows local group may not be the same one as was used for granting login rights in Step 3. Therefore, Steps 1 and 2 are often repeated to group the users by access permissions required.
Individual Windows accounts and Windows groups are assigned to a role.
Object permissions are assigned to the roles.

Authentication Modes

SQL Server 2000 provides two authentication modes for securing access to the server: Windows Authentication Mode and Mixed Mode.

Windows Authentication Mode

Windows Authentication Mode is the default authentication mode in SQL Server 2000. In Windows Authentication Mode, SQL Server 2000 relies solely on Windows to authenticate users. Windows users or groups are then granted access to SQL Server.

Windows Authentication Mode allows SQL Server 2000 to rely on Windows to authenticate users. Connections made to the server using this mode are known as trusted connections.

When Windows Authentication Mode is used, the database administrator allows users to access the computer running SQL Server by granting them the right to log in to SQL Server 2000. Windows security identifiers (SIDs) are used to track Windows authenticated logins. As Windows SIDs are used, the database administrator can grant login access directly to Windows users or groups.

Mixed Mode

In Mixed Mode, users can be authenticated by Windows Authentication or by SQL Server Authentication. Users who are authenticated by SQL Server have their username and password pairs maintained within SQL Server. (The username and password pairs are stored in the sysxlogins system table of the master database.)

In SQL Server 2000, Mixed Mode relies on Windows to authenticate users when the client and server are capable of using NTLM (standard Windows NT 4.0 or Windows 2000 logon using challenge/response) or Kerberos logon authentication protocols. If the client is unable to use a standard Windows logon, SQL Server requires a username and password pair, and compares this pair against those stored in its system tables. Connections that rely on username and password pairs are called non-trusted connections.

Mixed mode is supplied for backward compatibility and when SQL Server 2000 is installed on the Windows 98 or Windows Me operating systems. Trusted connections are not supported if the server is running on Windows 98 or Windows Me.

Using SIDs Internally

SQL Server 2000 uses SIDs internally. Windows users and groups can be granted access to databases or specific database objects directly. For example, Jane is a member of the SALES and MARKETING groups in Windows. The SALES group has been granted permission to log in to SQL Server and access the pubs database. An administrator could grant access to the authors table for Jane by her Windows name, REDMOND\Jane (the Windows account must be referenced by domain and username). In this case, Jane's SID would be stored in the system tables of the pubs database.

SQL Server 2000 does not support User Principal Names (UPNs). For example, if Jane's login is domain SALES, user SOMEONE, the login to SQL Server would be SALES\SOMEONE. A login in the form of SOMEONE@microsoft.com, as supported by Windows 2000 Active Directory, would not work.

Roles

Roles are used in SQL Server 2000 the way groups are used in Windows. Roles allow users to be collected into a single unit against which permissions can be applied. Permissions granted, denied, or revoked from a role apply to members of the role. For example, roles can be used to represent a job performed by a class of workers in an organization. Permissions can then be granted to that role. As workers rotate into the job, they are made members of the role; as they rotate out of the job, they are removed. This removes the requirement to repeatedly grant, deny, and revoke permissions to or from individuals as they accept or leave a job. For more information, see SQL Server Books Online.

Several concepts are important to understand when you are working with roles in SQL Server. First, with the exception of fixed server roles, roles are implemented within a database. This means that the database administrator does not rely on the Windows administrator for the grouping of users. Second, roles can be nested. This nesting is not limited by levels, but for obvious reasons does not allow circular nesting. Third, unlike groups in SQL Server 6.5 and earlier, a database user can be a member of more than one role simultaneously.

Public Role

The public role exists in every database, including the master, msdb, tempdb, and model system databases. The public role provides the default permissions for users in a database. Functionally, it is similar to the Everyone group in the Windows environment. Every database user is a member of this role automatically; users cannot be added or removed from this role, and it cannot be deleted.

Predefined Roles

SQL Server 2000 includes several predefined roles. These roles have predefined implied permissions, which cannot be granted to other user accounts. There are two types of predefined roles: fixed server roles and fixed database roles.

Fixed Server Roles

Fixed server roles are server-wide in their scope. They exist outside of the databases. Each member of a fixed server role is able to add other logins to that same role.

Note All members of the Windows BUILTIN\Administrators group (the local administrator's group) are members of the sysadmin role by default.

Fixed server roles found in SQL Server 2000

Fixed server role	Members can
Sysadmin	Perform any activity in SQL Server.
Serveradmin	Configure server-wide configuration options and shut down the server.
Setupadmin	Manage linked servers and startup procedures
Securityadmin	Manage server-wide security settings, including linked servers, and CREATE DATABASE permissions. Reset passwords for SQL Server authentication logins.
Processadmin	Terminate processes running in SQL Server.
Dbcreator	Create, alter, drop, and restore any database.
Diskadmin	Manage disk files.
Bulkadmin	Run the Bulk Insert command without having to be a member of the sysadmin role.

To add users to the fixed server roles, use the following Transact-SQL statement:

/* Add Bob to the sysadmin server role */
exec sp_addsrvrolemember "REDMOND\Bob", "sysadmin"

Windows users and groups can be added to server roles.

To add a user to a server role using SQL Distributed Management Objects (SQL-DMO)

' Declare variables.
Dim oServer As SQLDMO.SQLServer

' Create a server object and connect.
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.Connect ("SERVERNAME")

' Add Bob to the sysadmin server role.
oServer.ServerRoles("sysadmin").AddMember ("REDMOND\Bob")

For more information about fixed server roles, see SQL Server Books Online.

Fixed Database Roles

Fixed database roles are defined at the database level, and they exist in each database. Members of the db_owner and db_security roles can manage fixed database role membership; however, only members of the db_owner role can add others to the db_owner role.

The following table lists the fixed database roles in SQL Server 2000. Most fixed database roles enable members to perform certain actions; however, some exist to explicitly prevent members from performing certain actions.

SQL Server 2000 fixed database roles

Fixed database roles	Members
db_owner	Can perform all maintenance and configuration activities in the database.
db_accessadmin	Can add or remove access for Windows users, groups, and SQL Server logins.
db_datareader	Can read all data from all user tables.
db_datawriter	Can add, delete, or change data in all user tables.
db_ddladmin	Can run any data definition language (DDL) command in a database.
db_securityadmin	Can modify role membership and manages permissions.
db_backupoperator	Can back up the database.
db_denydatareader	Cannot read any data in user tables within a database.
db_denydatawriter	Cannot add, modify, or delete data in any user tables or views.

For more information about the use of fixed database roles, see SQL Server Books Online.

User-Defined Roles

User-defined roles provide an easy way to manage permissions in a database when a group of users performs a specified set of activities in SQL Server 2000 and there is no applicable Windows group, or if the database administrator does not have sufficient permissions to manage the Windows user accounts. In these situations, user-defined roles provide the database administrator the same flexibility as Windows groups.

User-defined roles apply only at the database level, and are local to the database in which they were created.

Application Roles

Application roles allow the database administrator to restrict user access to data based on the application that the user is using. Application roles allow the application to take over the responsibility of user authentication.

When an application makes a connection to SQL Server 2000, it executes the sp_setapprole stored procedure, which takes two parameters: username and password. These parameters can be encrypted. The password can always be encrypted before being sent to SQL Server. If the Multiprotocol Net-Library is used, the packet containing the password can also be encrypted. Registry keys are the best place to store the username and password in an application. The key should be encrypted, and only the application should have the key to unencrypt it. The existing permissions assigned to the user are dropped, and the security context of the application role is assumed.

After application roles are activated, they cannot be deactivated. The only way to return to the original security context of the user is to disconnect from and then reconnect to SQL Server.

Application roles work with both authentication modes, and contain no members. Users cannot be associated with application roles, because the application requests the application role's security context using the sp_setapprole stored procedure.

Like user-defined roles, application roles exist only within a database. If an application (in the security context of an application role) connects to one database and then attempts to access a second database, access to the other database is granted (using permissions) through the guest account in that database. If the guest account has not been specifically granted access to the data, or if it does not exist, the application cannot access the objects.

Another key concept in the use of application roles is that the user who is running the application is audited within SQL Server 2000. In other words, application roles provide the security context within which the database object permissions are checked, but the identity of the actual user is not lost.

Here is an example of an implementation using application roles. If Jane is a member of the ACCOUNTING group, and the ACCOUNTING group members are given access to the data in SQL Server only through the accounting software package, an application role could be created for the accounting software. The ACCOUNTING application role would be granted access to the data, while the ACCOUNTING group in Windows would be denied access to the data. Thus, when Jane attempts to access the data using SQL Query Analyzer, she will be denied access; but when Jane uses the accounting software, she will be able to access the data.

To use application roles

Create an application role.
Assign permissions to the application role.
Ensure that the client application connects to SQL Server 2000.
Ensure that the client application activates the application role.

The first two steps of this process are usually separated from the last two steps. Therefore, two code fragments will follow for Transact-SQL and Microsoft Visual Basic® respectively.

The Transact-SQL script is as follows:

/* Create the application role. */
EXEC sp_addapprole "AccAppRole", "ABC"

/* Grant permissions to SELECT. */
GRANT SELECT
ON authors
TO AccAppRole
GO

Here is the code to activate the role:

/* Activate the role. */
EXEC sp_setapprole "AccAppRole", {ENCRYPT N "ABC"}

The encryption of the password is optional, but encryption ensures greater security when the password has to pass through a wide area network (WAN).

Here is the Visual Basic code to create the role:

' Declare variables.
Dim oServer As SQLDMO.SQLServer
Dim oDbRole As SQLDMO.DatabaseRole

' Create a server object and connect.
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.Connect ("SERVERNAME")

' Create the Role object.
Set oDbRole = CreateObject("SQLDMO.DatabaseRole")

' Set the appropriate properties.
oDbRole.Name = "AccAppRole"
oDbRole.AppRole = True
oDbRole.Password = "ABC"

' Add the Role object to the server's Role collection.
oServer.Databases("pubs").DatabaseRoles.Add oDbRole

Here is the Visual Basic code to use the role:

' Declare variables.
Dim oConnection As ADODB.Connection

' Create the connection object and connect.
Set oConnection = CreateObject("ADODB.Connection")
oConnection.Provider = "sqloledb"
oConnection.Open "Server=SERVERNAME;Database=pubs;Trusted_Connection=yes"

' Activate the application role. There is no error handling for this sample.
oConnection.Execute "EXEC sp_setapprole 'AccAppRole', {ENCRYPT N 'ABC'}, 'ODBC'"

The encryption style (the last parameter) must be set for OLE DB and ODBC data sources. Other data sources cannot explicitly encrypt the password. In these cases, you must use an encrypted communications protocol with the server.

Application roles are implemented per session. If your application opens multiple sessions and all sessions are required to use the same role, each session must first activate the role.

Application roles can be used to provide much more granular security than ever before. For example, a client application could use the user's security context on some connections, while using an application role on another.

When using application roles, executing SELECT USER returns the name of the application role currently being used. If the identity of the logged-on user is required, use the following SQL statement: SELECT SYSTEM_USER.

Securing Access to the Server

Access to the server is controlled differently by the two authentication modes in SQL Server 2000. However, after a user gains access to the server, the authentication modes are identical. SQL Server 2000 security defaults to Windows Authentication when it is installed.

Windows Level

When securing access at the Windows level, administrators should create a login account for each user who will be accessing SQL Server (if the user does not already have an account).

In each user account's domain, global groups should be created to group users by job requirements. The users should then be placed into the appropriate global groups in their domain.

On the computer running SQL Server 2000, local groups should be created according to the various job requirements for which access to SQL Server needs to be granted. The appropriate global groups from the various trusted domains should then be placed into the respective local groups on the computer running SQL Server.

It may seem time-consuming to follow the procedures outlined earlier for a small single-domain network, but experience has shown great value in doing this.

The base requirement is to get all the users with the same security requirements grouped into one unit, which can then be used by the database administrator to grant access to SQL Server 2000. Granting access to SQL Server by group does not eliminate the ability to identify the individual user from within a database. (This is largely the same action as securing a file on NTFS by giving access to members of the SALES group only. If Bob, a member of the SALES group, tries to access the file, the audit log will contain an entry for Bob, not SALES.)

Although the recommendations are strong, they enable the database administrator to assign permissions to objects for Windows universal groups, global groups, local groups, and individual user accounts.

Note Programmatically creating user accounts and groups in the Windows environment is beyond the scope of this chapter. This can be achieved by using the ADSI object model from Visual Basic, or by interacting directly with the Win32® API from Microsoft Visual C++®.

SQL Server Level

At the SQL Server 2000 level, permissions must be granted for the created Windows local groups to log in to SQL Server. Permission to log in to SQL Server can also be granted to users directly, but is not as practical to administer except for the smallest of environments.

Permissions to log in to the server can be granted through the user interface or implemented programmatically using Visual Basic or Transact-SQL.

Note The following definitions are within the context of this document: Visual Basic refers to writing an application using the Visual Basic environment with the SQL-DMO library or using any Visual Basic for Applications environment with the SQL-DMO library. Transact-SQL is the Microsoft implementation of the SQL standard.

New stored procedures have been written to grant access for Windows users and groups. These security-related stored procedures are listed here.

sp_addalias	sp_droprole
sp_addapprole	sp_droprolemember
sp_addgroup	sp_dropserver
sp_addlinkedsrvlogin	sp_dropsrvrolemember
sp_addlogin	sp_dropuser
sp_addremotelogin	sp_grantdbaccess
sp_addrole	sp_grantlogin
sp_addrolemember	sp_helpdbfixedrole
sp_addserver	sp_helpgroup
sp_addsrvrolemember	sp_helplinkedsrvlogin
sp_adduser	sp_helplogins
sp_approlepassword	sp_helpntgroup
sp_change_users_login	sp_helpremotelogin
sp_changedbowner	sp_helprole
sp_changegroup	sp_helprolemember
sp_changeobjectowner	sp_helprotect
sp_dbfixedrolepermission	sp_helpsrvrole
sp_defaultdb	sp_helpsrvrolemember
sp_defaultlanguage	sp_helpuser
sp_denylogin	sp_password
sp_dropalias	sp_remoteoption
sp_dropapprole	sp_revokedbaccess
sp_dropgroup	sp_revokelogin
sp_droplinkedsrvlogin	sp_setapprole
sp_droplogin	sp_srvrolepermission
sp_dropremotelogin	sp_validatelogins

The following Transact-SQL statement grants login rights to the SALESLG local group:

/* Grant login. */
exec sp_grantlogin 'REDMOND\SALESLG'

Alternatively, login rights can be granted with the following Visual Basic code:

' Declare variables.
Dim oServer As SQLDMO.SQLServer
Dim oLogin As SQLDMO.Login

' Create a server object and connect.
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.Connect ("SERVERNAME")

' Create the Login object.
Set oLogin = CreateObject("SQLDMO.Login")

' Set the appropriate properties.
oLogin.Name = "REDMOND\SALESLG"
oLogin.Type = SQLDMOLogin_NTGroup

' Add the Login object to the server's Logins collection.
oServer.Logins.Add oLogin

To allow a user access to SQL Server 2000 using non-trusted connections, user accounts must be created on SQL Server.

Note When SQL Server 2000 is installed on Windows and configured to use Mixed Mode, capable clients can still make trusted connections.

The following Transact-SQL script creates a login for a non-trusted connection:

/* Add a login. */
exec sp_addlogin 'Bob', 'password', 'pubs'

This statement adds a user called Bob and sets the password to password. The default database becomes pubs. The default database is the database to which the user is switched when attempting to log in. A user must still create a user account in the default database for this to work; sp_addlogin does not add a user account in the referenced database.

The following example illustrates the same procedure using Visual Basic:

' Declare variables.
Dim oServer As SQLDMO.SQLServer
Dim oLogin As SQLDMO.Login

' Create a server object and connect.
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.Connect ("SERVERNAME")

' Create the Login object.
Set oLogin = CreateObject("SQLDMO.Login")

' Set the appropriate properties.
oLogin.Name = "Bob"
oLogin.Type = SQLDMOLogin_Standard
oLogin.SetPassword "","password"

' Add the Login object to the server's Logins collection.
oServer.Logins.Add oLogin

Securing Access to the Database

A successful login does not automatically allow a user access to all databases on SQL Server 2000. Permissions must be granted to allow users to access a database.

In this section, no differentiation is made between non-trusted users, Windows users, and Windows groups. Comments about Windows users or groups also apply to users or global groups in trusted domains, or domains within the same tree or forest.

Within each database, a user is created and is linked to a SQL Server login, a Windows user, or a Windows group.

SQL Server Enterprise Manager does not allow the creation of users who do not have specific login permissions. Microsoft Management Console (MMC), which hosts SQL Server Enterprise Manager, creates a list of all accounts that have been granted the permission to log in to the server, and a selection needs to be made from this list. The same applies to the SQL-DMO object model.

With Transact-SQL, any valid SQL Server login, Windows user, or Windows group can be granted the right to access the database, whether or not a specific login exists in the sysxlogins table in the master database.

Note Although not a technical requirement, if you are using* *trusted connections, it is strongly recommended that you create users with the same username in each database as the login name.

Some examples for the Transact-SQL statements required to grant permission to use a database are listed here:

/* Grant access to Bob. */
exec sp_grantdbaccess 'REDMOND\Bob'

/* Grant access to Wendy, referring to her by first name within this database. */
exec sp_grantdbaccess 'REDMOND\WendyH', 'Wendy'

Only one modification would be required to make this example work with non-trusted clients. Instead of the domain username, use the username that SQL Server 2000 uses to authenticate the user.

Using SQL-DMO, the equivalent functionality would be achieved by the following code:

' Declare variables.
Dim oServer As SQLDMO.SQLServer
Dim oUser As SQLDMO.User

' Create a server object and connect.
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.Connect ("SERVERNAME")

' Create the User object.
Set oUser = CreateObject("SQLDMO.User")

' Set the appropriate properties.
oUser.Name = "Bob"
oUser.Login = "REDMOND\Bob"

' Add the User object to the server's Users collection.
oServer.Databases("pubs").Users.Add oUser

Securing Access to the Database Objects

Permissions can be granted to roles and users, and they can be assigned to allow users to execute certain statements and to access certain database objects. Statement permissions restrict who can execute statements such as CREATE DATABASE, CREATE TABLE, or CREATE FUNCTION. Object permissions restrict access to objects such as tables, views, user-defined functions, or stored procedures. Object permissions depend on the object being referenced. For example, object permission for tables include the SELECT, INSERT, UPDATE, DELETE, and REFERENCES permissions, while the object permissions on a stored procedure include EXECUTE permissions.

User-Defined Database Roles

In an ideal environment, roles would not be necessary. In such an environment, all users would be running SQL Server 2000 on Windows NT 4.0 or Windows 2000 in Windows Authentication Mode. The database administrator could ask the Windows administrator to place all the users with a specific data access requirement (or role) into one Windows group, and the database administrator would then grant permissions to that Windows group as required.

However, as this is not the case in most environments, creating Windows groups is not always possible. For example, when SQL Server 2000 is installed on the Windows 98 operating system, Windows groups are not technically possible. In this case, roles can be used to group users by their permission requirements.

Any Windows user or group can be assigned to a role, which can then be assigned permissions to database objects the same way as database users are assigned permissions.

Note User-defined roles can be created only in a database. Fixed server roles and fixed database roles are predefined and cannot be modified.

Roles can be created with the following Transact-SQL code:

/* Add role for Telephone Operators. */
exec sp_addrole "TelephoneOperators"

Alternatively, roles can be created with the following Visual Basic code:

' Declare variables.
Dim oServer As SQLDMO.SQLServer
Dim oDbRole As SQLDMO.DatabaseRole

' Create a server object and connect.
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.Connect ("SERVERNAME")

' Create the Database Role object.
Set oDbRole = CreateObject("SQLDMO.DatabaseRole")

' Set the appropriate properties.
oDbRole.Name = "TelephoneOperators"

' Add the Role object to the server's Roles collection.
oServer.Databases("pubs").DatabaseRoles.Add oDbRole

After a user-defined database role is created, users, groups, or other roles are added to it. Roles can be nested, although not in a circular manner, as this would not be productive.

This sample Transact-SQL code adds a Windows user, a Windows group, and a database role to the newly created role:

/* Add a Windows user to the TelephoneOperators role. */
exec sp_addrolemember "TelephoneOperators", "REDMOND\Bob"

/* Add a Windows group to the TelephoneOperators role. */
exec sp_addrolemember "TelephoneOperators", "REDMOND\Sales"

/* Add HelpDeskOperators role to TelephoneOperators role. */
exec sp_addrolemember "TelephoneOperators", "HelpDeskOperators"

Here is an example of the same operation using SQL-DMO:

' Declare variables.
Dim oServer As SQLDMO.SQLServer

' Create a server object and connect.
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.Connect ("MSNZBENTHOM")

' Use with statement for code legibility.
With oServer.Databases("pubs").DatabaseRoles("TelephoneOperators")

' Add the Windows user to the TelephoneOperators role collection.
.AddMember ("REDMOND\Bob")

' Add the Windows group to the TelephoneOperators role collection.
.AddMember ("REDMOND\Sales")

' Add the HelpDeskOperators role to the TelephoneOperators role collection.
.AddMember ("HelpDeskOperators")

End With

Permissions System

The permission system in SQL Server 2000 is based on the same additive model that forms the basis of Windows permissions. If a user is a member of the sales, marketing, and research roles (multiple group memberships are now possible), the user gets the sum of the respective permissions of each role. For example, if sales has SELECT permissions on a table, marketing has INSERT permissions, and research has UPDATE permissions, the user would be able to use SELECT, INSERT, and UPDATE. However, as with Windows, if a particular role of which the user is a member has been denied a specific object permission (such as SELECT), the user is unable to exercise that permission. The most restrictive permission, DENY, takes precedence.

Granting and Denying Permissions to Users and Roles

Permissions within a database are always granted to database users, roles, and Windows users or groups, but never to SQL Server 2000 logins. The methods used to set the appropriate permissions for users or roles within a database are: granting permissions, denying permissions, and revoking permissions.

The DENY permission allows an administrator to deny an object or statement permission to a user or role. As with Windows permissions, DENY takes precedence over all other permissions.

For example, if some database users are frivolously changing data, it would not make sense to remove permissions for all users, because the majority of the users are using the data responsibly. It is possible to create a new role with a name like trouble_makers, and then use DENY to prevent INSERT, UPDATE, and DELETE operations on all tables for this role. Users can be added to the trouble_makers role without regard for their other personal, group, or role permissions.

Revoking permissions is not the same as denying permissions. The REVOKE permission deletes a previous GRANT or DENY; the DENY permission prevents access even when access permissions have been granted.

In this section, each of these methods will be applied in a Visual Basic example and in a Transact-SQL example. The following Transact-SQL code grants Bob and Jane permissions to select from the authors table, and grants Jane permissions to insert into the titles table:

/* Grant permissions to use SELECT. */
GRANT SELECT
ON authors
TO Bob, [REDMOND\Jane]
GO 

/* Grant permissions to use INSERT. */
GRANT INSERT
ON titles
TO [REDMOND\Jane]
GO

The previous example shows how the GRANT statement works when permissions are granted to explicit users of the database (Bob) and when permissions are granted to a Windows user (Jane).

Here is the same example in Visual Basic:

' Declare variables.
Dim oServer As SQLDMO.SQLServer

' Create a server object and connect.
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.Connect ("SERVERNAME")

' Grant Jane and Bob permissions to select from the authors table.
oServer.Databases("pubs").Tables("authors").Grant SQLDMOPriv_Select, "Bob"
oServer.Databases("pubs").Tables("authors").Grant SQLDMOPriv_Select, _
"[REDMOND\Jane]
' Grant Jane permissions to insert from the titles table.
oServer.Databases("pubs").Tables("titles").Grant SQLDMOPriv_Insert, _
"[REDMOND\Jane]"

In the previous examples, granting access to a user by fully qualifying their domain name is much like granting access to a user who already has permissions to access the database directly. Due to these similarities, the following examples show only the code for existing database users.

The following Transact-SQL statement shows how a user can be denied SELECT permissions:

/* Deny permissions to use SELECT. */
DENY SELECT
ON authors
TO Bob
GO

Here is the same action using Visual Basic:

' Declare variables.
Dim oServer As SQLDMO.SQLServer

' Create a server object and connect.
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.Connect ("SERVERNAME")

' Deny Bob permissions to select from authors table.
oServer.Databases("pubs").Tables("authors").Deny SQLDMOPriv_Select, "Bob"

Here is a Transact-SQL example that shows how to revoke permissions from a user:

/* Revoke permissions to use SELECT. */
REVOKE SELECT
ON authors
FROM Bob
GO

Here is the Visual Basic code:

' Declare variables.
Dim oServer As SQLDMO.SQLServer

' Create a server object and connect.
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.Connect ("SERVERNAME")

' Revoke Bob's permissions to select from the authors table.
oServer.Databases("pubs").Tables("authors").Revoke SQLDMOPriv_Select, "Bob"

Ownership Chains

A thorough understanding of ownership chains is crucial for the development of a secure SQL Server 2000 environment. The concept of ownership chains is established when permissions on an object are checked. For example, when a user accesses a view, the permissions on the view should be checked, but what about the permissions on the underlying table?

SQL Server 2000 always checks the permissions on objects when there is a broken ownership chain. A broken ownership chain occurs when an object does not have the same owner as its underlying objects. For example, if Bob creates a table, and Mary creates a view based on that table, this creates a broken ownership chain.

In relation to security, broken ownership chains specify where permissions should be checked over and above the original object accessed. This makes for a very practical model.

The concepts of ownership chains are best explained with a detailed example. Assume that Bob owns a table. He secures access to it by granting SELECT permissions on the table to only Mary. Mary creates a view of Bob's table that suits her needs. One day Sue sees Mary using this view, and exclaims how brilliant it is. Mary agrees to give Sue access to the view. It was not Bob's original intention that Sue should see the data in his table. Fortunately, there is a broken ownership chain, as Bob owns the table and Mary owns the view. The owner of the view does not own the underlying objects. In this case, when Sue tries to use the view, SQL Server checks the permissions on the view to ensure that Sue has been granted access. Following this, the permissions on Bob's table are also checked. If Sue has not been granted access to the table, she cannot use the view because of the broken ownership chain. Effectively, a broken ownership chain guards against a user gaining unwanted access to data.

Conversely, if Bob decided to create the view and to deny Sue access to his table, but grant her access to the view, Sue would be able to access the view. This is because the permissions are checked only when Sue accesses the view. There is no broken ownership chain, so permissions for the underlying table are not checked. Because Bob has created both objects, he should understand that giving access to the view requires implicit access to the underlying objects.

SQL Server 2000 also uses the power of ownership chains in the implementation of passwords. Users cannot update system tables directly, especially not those found in the master database. In Mixed Mode authentication, username and password combinations are stored in the sysxlogins system table. Users should be given the opportunity to change their passwords on a regular basis; SQL Server 2000 achieves this by implementing a stored procedure to change the password, which any user can execute. Access to the sysxlogins table is denied, but permission to execute the sp_password stored procedure is granted to all users. Because the sp_password stored procedure and sysxlogins system table have the same owner, there is no broken ownership chain, and permissions are checked only on the stored procedure.

Ownership chains allow SQL Server 2000 to implement a security system that allows the owner of the original data to control all access to it. At the same time, performance is increased because permissions do not require checking as long as the ownership chain is not broken.

Implementation of Server-Level Security

Use of SIDs

SQL Server 2000 checks whether the user's SID or group membership SIDs have been specifically denied access to the server. If the user is not specifically denied access, the server checks whether the user has been granted access directly or by virtue of a group membership. If access has been granted through one of these methods, the connection to SQL Server 2000 is maintained. The user then proceeds to the appropriate default database (to which the user must also have been granted access). Whenever the user attempts to access an object, the user's access rights are checked. If access has not been granted for a particular set of login credentials, the connection to the server is terminated.

When a Windows NT 4.0 or Windows 2000 user or group is either granted or denied access to SQL Server 2000, this information is stored in the sysxlogins system table. Permissions on a registry key no longer control access to the server. SQL Server 2000 identifies users connecting through a trusted connection by their SID and group membership SIDs.

Elimination of SUIDs

The SUID column no longer exists in SQL Server 2000.

In SQL Server 6.5 and earlier, security was tracked using the server user identification number (SUID) value in the sysxlogins system table in the master database. This column also existed in SQL Server 7.0 in several system tables.

The <name> column was dropped from the following system tables:

sysdatabases
syslogins
sysremotelogins
sysusers
sysprocesses

The sysalternates view has been completely removed. The SUSER_ID() and SUSER_NAME() functions have been deprecated. They will always return NULL. For more information, see "SUID Column" in this chapter.

Generation of GUIDs for Non-Trusted Users

Windows SIDS are not available for non-trusted connections, such as connections to and from SQL Server 2000 installed on a Windows 98 operating system. In this case, SQL Server 2000 generates a 16-byte globally unique identifier (GUID). The generated GUID is then used internally in the same way as Windows SIDs are used for Windows users and groups. In this way, security can function identically in a trusted and non-trusted environment.

Renaming Windows User or Group Accounts

If a Windows user or group is renamed using the User Manager for Domains tool in Windows NT 4.0 or the Active Directory utility in Windows 2000, SQL Server 2000 is not aware of the change. SQL Server 2000 maintains the user or group's fully qualified name in the sysxlogins table for performance reasons, because it can be very slow to query the domain controller for this information. This is true when many name lookups are done or the domain controller is connected over a slow WAN link.

The fact that the names of users and groups may be different from SQL Server 2000 to Windows does not cause security problems. The permissions set for the user or the group continue to function correctly, because SQL Server relies only on the SIDs internally.

When the SUSER_SNAME() and SUSER_SID() functions are used to return the username and user's SID respectively, they attempt to resolve these values by first querying the sysxlogins table. The Windows Local Security Authority (LSA) is queried only if the sysxlogins table does not contain the username or SID. For more information, see the Windows documentation.

Another effect of using these functions is that the usernames in system messages may not report an up-to-date name.

sysxlogins System Table

The sysxlogins system table contains the login permission (or lack of such) for users. In SQL Server version 6.5 and earlier, this information was stored in the syslogins system table. For backward compatibility, the syslogins system table can still be queried as it is a view over the sysxlogins table. This view should not be necessary because system tables should not be accessed directly. System tables can change at any time. The sysxlogins system table exists only in the master database.

SQL Server 2000 includes three views that depend on the sysxlogins table.

The syslogins view provides backward compatibility; at the same time it interprets the status column so that the column can be understood more readily.
The sysremotelogins view provides backward compatibility, and allows information regarding remote logins to be accessed more readily.
The sysoledbusers view provides information about remote logins.

xstatus Column

The xstatus column provides a number of status settings, including the server role memberships. The various status values are listed in the following table.

Status settings

Purpose	Bit*	Notes
denylogin	1	None
hasaccess	2	None
Isntname	3	Not "ISN'T" but "IS WINDOWS"
Isntgroup	3	Only if status bit 4 is not set
Isntuser	4	Must also have status bit 3 set
Sysadmin	5	Server role
securityadmin	6	Server role
serveradmin	7	Server role
setupadmin	8	Server role
processadmin	9	Server role
diskadmin	10	Server role
dbcreator	11	Server role
bulkadmin	12	Server role

*****Bit: counting status bits from right to left.

dbid and language Columns

These columns provide default database and default language settings for users. When a user connects to SQL Server 2000, the server looks for a row containing the user's specific SID (or GUID in the case of non-trusted connections) in the sysxlogins table. If that row is found, the server takes the default database and default language settings from it. If that row is not found, the server looks for SIDs for the groups of which this user is a member. The default database and language settings from the first group that is found are used.

If the first group found contains a default language, but the default database is NULL, SQL Server continues to the next group the user is a member of and tries to ascertain the default database from there. For example, if Bob is a member of the SALES and MARKETING groups, and he does not have a default database and default language configured specifically for his account, the system looks for the default settings applied to the SALES and MARKETING groups. The first settings returned are used. Thus, if a user is a member of more than one group, and does not have default database and default language settings assigned, the defaults chosen based on membership in any specific group are not guaranteed if another group's defaults are returned first.

It is possible to assign default database and language settings specifically to a user without granting specific login rights to that user. The user can be granted access to SQL Server based on group memberships, but receive the default settings based on the defaults recorded in the sysxlogins system table specifically for that user. In this case, the hasaccess flag of the sysxlogins table would be set to zero for this user's specific entry in the sysxlogins table.

hasaccess Status

The hasaccess status in the sysxlogins system table allows configuration of default settings for a specific user without implicitly granting that user access. Typically, the sysxlogins table is used to grant login rights to particular users or groups. If the hasaccess status is zero, the user is not granted login access explicitly. However, when the user logs on through a group membership, the defaults are established.

The hasaccess status is also crucial for another reason, which is best explained by an example. Bob is a member of the REDMOND\SALES group, and he has not been specifically granted permission to log in to SQL Server 2000. The sysxlogins table has no entries for Bob. However, the REDMOND\SALES group has been granted login permissions, so Bob is able to log in. When Bob becomes a member of a fixed server role, he should not automatically gain permission to access the server directly; his access should still be through the SALES group. In this case, a new row is added to sysxlogins for Bob, but the hasaccess flag is set to zero so that the relevant server role memberships can be granted without implicitly granting access to the server.

If the denylogin flag is set, the sysxlogins table can contain entries that do not specifically grant access to a user or group.

denylogin Status

The denylogin status is used to mark a user or group as being explicitly denied access to SQL Server 2000. For example, to prevent access to SQL Server for a specific user (or group), the following Transact-SQL statement can be executed:

Exec sp_denylogin 'REDMOND\Bob'

This is not the same as the following statement:

Exec sp_revokelogin 'REDMOND\Bob'

The difference between the two statements is that the first statement denies access to SQL Server and the second revokes access for that particular account only. If Bob is a member of the MARKETING group, which does have access, the second statement would allow him to continue to access the server by virtue of membership in the MARKETING group. The first statement would deny access irrespective of any group memberships that may grant access.

Note On Windows operating systems, one DENY is all that is required to lock a user out of a resource.

sysremotelogins View

The sysremotelogins view is provided for backward compatibility. In SQL Server 6.5 and earlier, a table with the name sysremotelogins provided the mapping used for remote logins.

sysoledbuser View

When a user wants to run a query on a remote server, the local server must log in to the linked server on behalf of the user.

The sp_addlinkedsrvlogin stored procedure adds new linked accounts to remote servers. This information is stored in the sysxlogins table. The stored procedure requires the remote server name, the local username, the remote username, and the remote password as parameters.

Implementation of Object-Level Security

How Permissions Are Checked

SQL Server 2000 uses SIDs to identify Windows users and groups. However, due to the length of SIDs (which can be up to 85 bytes), SQL Server 2000 maps the SIDs to user IDs inside each database. The SIDs are mapped to user IDs in the sysusers table. The user ID is then used in the sysobjects table to denote the owner of a table. It is also used in the syspermissions table to set permissions on objects, and in the systypes table to denote the owner of a user-defined type.

When a user connects to SQL Server 2000, the server creates a process status structure (PSS) (referred to in several error messages produced by SQL Server 7.0) in memory, which contains the user's SID, group SIDs, and other security and state information. This structure is a snapshot taken when the user connects, and the snapshot is not refreshed. This structure exists per session connecting to the server; a single user establishing multiple sessions with SQL Server 2000 will have multiple PSSs.

When the user accesses a database, SQL Server checks the sysusers table to determine whether the user has been denied access directly or by virtue of being a member of a group that has been denied access. If the user is denied access, this is enforced. If not, the sysusers table is checked again, but this time all user IDs the user qualifies for are collected. After it is established that the user has been granted access to the database, the sysmembers table is scanned to establish all the role memberships of the user. For example, the user may be a member of a role, a member of a Windows group, or aliased to another user. User IDs of all the applicable memberships are established so that the appropriate permissions for this user can be applied. Unlike the PSSs, this information is not stored permanently.

When the user starts accessing objects in the database, the applicable permissions are determined by checking the syspermissions table for entries with matching user IDs (as identified earlier). The system checks DENY permissions first. If DENY permissions are found, the user cannot access the object. However, if no DENY permissions are found, and entries that give the user the required access exist, access is granted. The effective access permissions are then cached, so that permissions do not have to be checked every time the user attempts to access the object.

Cost of Changing Permissions

Checking permissions can be expensive. For this reason, SQL Server 2000 caches the object permissions on a per-session basis (as stated earlier). Unlike the PSS, which does not change the security information after it is created, the permissions cache is always up-to-date. This is implemented by the versioning method.

When the initial checking of permissions takes place, a version number is established.SQL Server 2000 increases the version counter when the permissions on an object are changed. Whenever an object is accessed, the version of the permission counter is checked, and if it differs from the cached counter, the content of the cache is discarded and the effective permissions are re-established.

The cached security is used whenever an object is accessed, as long as the version counter has not changed. If the counter has changed, a small overhead is incurred for that operation.

Changes to Windows User or Group Account Names

In SQL Server 2000, it is possible to grant Windows users and groups access to objects in the database directly. In that case, the SID and Windows user or group names are stored in the sysusers table. For information about this issue with login names, see "Renaming Windows User or Group Accounts" in this chapter.

When the Windows administrator renames the Windows group or user, the name change is not propagated to SQL Server 2000. This avoids breaking existing stored procedures, views, and so on that use the group or user name. For example, Susie Jones is a user who creates a table in the database. Susie's login name is SUSIEJ, and her table is named SUSIEJ.SALESDEMO. Susie grants permissions for others to access her table, and several of her colleagues create views and stored procedures based on her table. When Susie marries Bob Taylor, her username is changed to SUSIET. If SQL Server 2000 were to pick up the change, Susie's table would suddenly be SUSIET.SALESDEMO, which is a completely different object. Any views, stored procedures, and any code written to access this table would no longer work.

In the interest of stability, SQL Server 2000 does not automatically rename user accounts when the real account in the Windows User Directory is renamed.

sysprocedures System Table Removed

In SQL Server 6.5 and earlier, when a stored procedure was created, the query text was stored in the syscomments system table, and a normalized query tree was saved in the sysprocedures system table. The normalization process parsed the SQL statements into more efficient formats and resolved all referenced objects into their internal representations. When the procedure was subsequently executed, the tree was retrieved from the sysprocedures table and used as the basis for an optimized execution plan, which was then stored in the procedure cache. (This approach was also used for views, defaults, rules, triggers, check constraints, and default constraints.)

At first, it may appear that there is no link between the process outlined earlier and security. However, there is a connection: In an attempt to protect their source code, some software developers deleted the original SQL text from syscomments, particularly if they were working with versions earlier than SQL Server 6.0. In most cases, the original SQL text was not used again until the server was upgraded with a later version of SQL Server, or when a service pack was applied. Microsoft provided a better mechanism for hiding the original SQL text from anyone who should not have access with the introduction of the WITH ENCRYPTION option in SQL Server version 6.0. This option encrypted the original SQL text upon the creation of the stored procedure.

If the appropriate entries from the syscomments table are deleted in SQL Server 7.0 and SQL Server 2000, the stored procedure no longer executes. This is because the sysprocedures table has been removed. The SQL text is now obtained directly from the syscomments table prior to execution.

WITH GRANT OPTION

The WITH GRANT OPTION is optional syntax that can be used with the GRANT statement. This option applies only to object permissions, and allows the recipient of the GRANT statement to pass on any permissions the statement grants.

For example, if Jane grants Bob SELECT permissions and uses the WITH GRANT OPTION, Bob can grant SELECT permissions. (If Jane revokes SELECT permissions from Bob, she can use the CASCADE option to revoke the SELECT permissions from users to whom Bob granted SELECT permissions.)

sysusers System Table

In some ways, the sysusers table is to the database what the sysxlogins table is to the server. The sysusers table exists in each database, and contains information about who is granted or denied access to the database.

hasdbaccess Column

The hasdbaccess column is similar to the hasaccess column in the sysxlogins table. In hasdbaccess, entries with this flag set to zero are created when a user has not been granted rights to access the database explicitly but creates objects, is explicitly granted permissions, or is added explicitly to a role. Objects created by a user are always owned by the user, not by the group through which the user was granted database access. The only exception is a member of a role or Windows group who explicitly qualifies the role or group as the object's owner when the object is created. The following syntax should be used: CREATE TABLE [BUILTIN\administrators].test_table(acolumn VARCHAR(2)). In this situation, an entry for the user must exist in the sysusers table so that the object can have the appropriate owner. The entry is created automatically, but the user is not granted explicit access to the database automatically, because the hasaccess flag is set to zero.

The hasdbaccess column is set to zero for roles, which are also listed in the sysusers table.

sysmembers System Table

The sysmembers system table records the membership of users in database roles. It contains one row for each member of a database role.

SQL Server 2000 improves the performance related to roles by placing a user's first membership of a role into the gid column of the sysusers table. Thus, when SQL Server 2000 tries to identify all the roles to which a user belongs, it does not have to query the sysmembers table if the gid column of the sysusers table contains zero. If the entry in that column is not zero, the entry specifies one of the roles, and the sysmembers table must be queried for a complete list of all the roles to which the user belongs.

syspermissions System Table

The syspermissions system table, which exists in every database, was introduced in SQL Server 7.0. It tracks permissions that have been granted or denied to users. For more information about backward compatibility with the sysprotects table, see "sysprotects System Table" in this chapter.

The syspermissions system table contains very few columns. The id column references the object ID for which the permissions are being granted or denied. For statement permissions, this column is set to zero.

The grantee and grantor columns are self-explanatory. They contain the ID of the role, Windows user, or Windows group as it is found in the sysusers table.

The actadd column refers the positive permission (or permissions granted) on all columns (in the case of a table) of the object, while the actmod column refers to the negative (or permissions denied) permissions on all columns (in the case of a table) of the object.

The remaining columns are used only when column-level permissions are implemented. The seladd column is a bitmap of the columns that have been granted SELECT permissions. Because column IDs are never reused, the bitmap approach works very well. The selmod column is for SELECT permissions denied.

sysprotects System Table

The implementation of the sysprotects system table has changed from earlier releases. In SQL Server 6.5 and earlier, the sysprotects table stored the object permissions. In SQL Server 7.0 and SQL Server 2000, this information is stored in the syspermissions table.

Named Pipes and Multiprotocol Permissions

When discussing the internal security of SQL Server 2000, it is important to point out a key concept that is often overlooked. This is not new for SQL Server 2000, but is mentioned here for completeness.

The Named Pipes Net-Library is an interprocess communication (IPC) mechanism, which is implemented over the IPC$ share on Windows. Thus, when a client connects to SQL Server using the Named Pipes Net-Library, the connection is made to the IPC$ share, at which point authentication takes place. After Windows has authenticated the client (in the same way as it would for access to any other resource), the Named Pipes session is established over the IPC$ share. This takes place before any attempt is made to pass the connection to SQL Server.

All users who will be connecting to SQL Server 2000 using the Named Pipes Net-Library must have a Windows account and have permissions in Windows to access the IPC$ share. If you do not want this authentication to take place, switch to another network library such as TCP/IP Sockets or Multiprotocol; these connections are not validated against the Windows NT 4.0 IPC$ share. TCP/IP Sockets is the default network library in SQL Server 2000.

With the Multiprotocol Net-Library, Windows authentication also takes place before SQL Server 2000 passes the connection. This is because the remote procedure call (RPC) run-time services authenticate the client when the connection is requested. Like the Named Pipes Net-Library, the Multiprotocol Net-Library requires a valid Windows account.

The Multiprotocol Net-Library does not work to connect to named instances of SQL Server 2000. It is no longer required, because all network libraries support encryption.

Enabling the Windows guest account is one way of dealing with users who do not have a Windows account, but who want to connect using the Named Pipes or Multiprotocol Net-Libraries. When these users request a session, they can connect to Windows using the guest user account, and then attempt to log in to SQL Server. Because enabling the guest account makes your entire Windows environment less secure, this option is not usually recommended. It is mentioned here only as a workaround of last resort.

Upgrading from SQL Server 7.0

There are no architectural changes in security from SQL Server 7.0 to SQL Server 2000. For information about new security features in SQL Server 2000, see "New Security Features in SQL Server 2000" in this chapter.

Upgrading from SQL Server 6.5

The security model for SQL Server 6.5 is different from that of SQL Server 2000. These changes were necessary to provide a practical working environment for SQL Server. Because of this change, permissions need to be carefully considered when performing an upgrade.

Upgrade Considerations

The information covered in this section applies only to upgrading from SQL Server 6.5 Integrated Mode or Mixed Mode. If the upgrade is performed from a computer running SQL Server 6.5 that is configured in standard mode, no security problems will be encountered. However, it is recommended that the new security functionality available as part of Windows Authentication Mode be used in the upgraded environment. The best method of ensuring that the security settings of SQL Server 6.5 are upgraded in the most constructive way possible is to plan the upgrade thoroughly and prepare the security environment.

Upgrade Process

The upgrade can be performed either on one computer or remotely from one computer to another. Logically, a single computer upgrade is the same as a two-computer upgrade where the source and target computers are the same. The two computers will be referred to in this document as source and target servers. The source server is expected to have SQL Server 6.5 installed, and the target server must have SQL Server 2000 installed.

During the version upgrade process, a program opens the SQL Server 6.5 integrated registry key on the source computer, and reads the SIDs of all accounts that have integrated logins granted to them. The accounts that have integrated security configured on the source server may be Windows global groups, Windows local groups, or Windows users. In the case of the global groups and users, these accounts can be from a local domain (if SQL Server 2000 is running on a member server) or a trusted domain. If SQL Server 2000 is installed on a domain controller, the local groups are the local groups from the domain controller's domain; otherwise, the local groups are the local groups of the member server.

Accounts that were given administrative permissions on the source server are ignored in the drilldown and account-mapping processes.

Note The sp_grantlogin statement is executed on the computer running SQL Server 2000 for each Windows account that was configured to use integrated security on SQL Server 6.5.

Analyzing the Upgrade Output

Most of the security-related upgrade difficulties occur because in SQL Server 6.5, integrated security was implemented by securing a key in the registry, and only users who had access to that key could gain access to the server. The permissions on the registry key were linked to user login accounts, which were stored in the syslogins table.

SQL Server 2000 does not use this method for securing access to the server. Instead, it allows access to the server to be granted based on Windows user or group SIDs. Therefore, the upgrade process is sometimes unable to identify the original security requirements. This is usually because the SQL Server security environment was not up-to-date, or because the upgrade is going into a different environment.

The following table shows how logins appear in SQL Server Enterprise Manager after an upgrade. The contents of this table are explained in the sections that follow.

SQL Server Enterprise Manager logins

Name	Type	Server access	Default database	User	Line
user1	Standard	Permit	Master		1
a#user2	Standard	Permit	Master		2
BUILTIN\Administrators	Windows group	Permit	Master	dbo	3
DOM3\SQLUsers	Windows group	Permit	Master		4
DOM3_user#3	Standard	Permit	Master		5
DOM3_Administrator	Standard	Permit	Master		6
REDMOND\a user4	Windows user	Through group	Master		7
REDMOND\user5	Windows user	Through group	Master		8

User Has Been Deleted

Lines 1 and 2 in the previous table are produced when the users are not found in the Windows User Directory. Specifically, if the xp_logininfo system stored procedure does not return the username, it is converted as a standard login as in these two lines. The number sign (#) character in Line 2 is used to represent the space, because SQL Server 6.5 and earlier did not support special characters.

Administrator Account

The BUILTIN\Administrators local group in Line 3 of the previous table has been aliased to the dbo user of the master database.

Users of a Trusted Domain

Line 4 in the table refers to the DOM3\SQLUsers group, a global group on a trusted domain. The members of this group have been granted login rights. The members of this group have also been granted login rights with standard security using their username, as they would have appeared in SQL Server 6.5 and earlier. This provides backward compatibility for standard mode security.

Notice the entry for the Administrator account in Line 6; the Windows administrator account of the DOM3 domain was granted user login rights prior to the upgrade. These rights have been preserved. All user-level login access is processed in this way.

Users of the Current Default Domain

Users of the current default domain (as configured in SQL Server 6.x prior to the upgrade) are upgraded as per Lines 7 and 8. Notice the type of the account and the existence of the space in Line 7. SQL Server 2000 supports special characters in the account names.

Preparing the SQL Server 6.5 Security Environment

It is strongly recommended that all security settings be cleaned up thoroughly prior to the upgrade. SQL Security Manager should be run to ensure that all Windows accounts are synchronized with SQL Server. If the environment is in order, the upgrade process is more likely to proceed seamlessly.

Step Through the Upgrade

It is relatively easy to monitor the upgrade process and identify how the upgrading of user accounts and groups is likely to go. A SQL Server Upgrade Wizard option allows the process to be stopped after every step. If this is selected, the user can analyze the output created by the early stages of the security upgrade. The specific files to analyze are Loginmap.sid and Loginmap.txt. If the content does not appear correctly, these text files can be edited before the upgrade continues.

Note Microsoft does not support the editing of Loginmap.sid and Loginmap.txt files during the upgrade process.

Upgrading to a New Domain

When upgrading from SQL Server 6.x using the tape method, do not back up the database in one domain and then upgrade it into another domain. When the xp_logininfo stored procedure is executed, it will probably not find any of the accounts that existed in the original domain (and if it does, they are probably not the correct accounts, but are just identical in name). The login rights will be treated as if the accounts have been deleted. For more information, see "User Has Been Deleted" in this chapter.

Character Mapping

Character mapping is not required; SQL Server 2000 can handle spaces and backslashes (\) in account names.

In SQL Server 6.5 and earlier, character mapping had to be configured to deal with Windows NT account names, which contained special characters such as the backslash character. Therefore, SQL Server 6.5 and earlier provided three mapping characters: the number sign (#), underscore (_), and dollar sign ($).

sa Account

In SQL Server 6.5 and earlier, administrators logged in to SQL Server using the sa account to perform most administrative tasks. This often required a large number of people to have administrative access.

All Windows NT 4.0 and Windows 2000 users who are given sa-type rights on SQL Server 2000 should be assigned to the sysadmin fixed server role.

For more information, see "sa Account" in this chapter.

Aliasing

It is recommended that you use roles instead of aliasing. Although SQL Server 2000 supports aliasing of user accounts within a database for backward compatibility, using aliasing for this purpose is no longer recommended. Roles are more powerful, and they provide similar functionality to aliasing. For more information, see "User-Defined Roles" in this chapter.

Setting Up a Secure SQL Server 2000 Installation

The information discussed in this section applies to SQL Server 2000 installed on Windows NT 4.0 or Windows 2000 only. The Windows 98 and Windows Me environments do not provide the security features discussed.

This section assumes that SQL Server 2000 has been configured with Windows Authentication Mode to provide the highest level of security.

sa Account

It is recommended that all administrators of SQL Server be granted access to SQL Server through Windows group membership, and that this same group be made a member of the sysadmin server role. This approach has one minor drawback: Windows administrators can give anyone sysadmin permissions on SQL Server 2000, as they are able to add any user to the appropriate Windows group.

If a site does not want to give Windows administrators the ability to give others (or themselves) sysadmin access to SQL Server, only individual Windows accounts should be assigned to the role of sysadmin.

In each case, it is strongly recommended that the sa account not be used for day-to-day administration. Instead, a password should be assigned that is hard to break, and it should be locked in a safe for emergency access only.

If you are running SQL Server 2000 with Windows Authentication Mode (as recommended in this chapter), you cannot log in using the sa account. Only trusted connections are allowed.

Note Even thought the sa account cannot be used to log in to SQL Server 2000 when it is running in mixed Authentication Mode, it is still important to assign an sa password. This is because a small change in the registry (HKLM\Software\Microsoft\MSSQLServer\LoginMode: a value of 0 indicates Mixed Mode and a value of 1 indicates Windows Authentication Mode) can change the security mode from Authentication Mode to Mixed Mode. If the sa password is blank (this is the default at installation), an intruder or the Windows administrator can to gain access to the server. For information about ways to reduce the chance of such an attack, see "Registry" in this chapter.

Service Accounts

SQL Server 2000 runs as four Windows services:

MSSQLServer (or MSSQL$InstanceName for a named instance). Provides the core relational functionality of SQL Server.
SQLServerAgent (or SQLAgent$InstanceName for a named instance). Provides the capability to schedule regular commands, schedule replication, supply a method for dealing with errors, contact SQL Server operators when errors occur, as well as other support functions.
Microsoft Search service. Provides the full-text search capability. This service must always be configured to use the local system account.
MSSQLServerOLAPService. The Analysis Services engine, which provides the OLAP and data mining functionality for SQL Server.

SQL Server and SQL Server Agent services can be configured to use one of the following types of Windows accounts:

Local service account
Local user account
Domain user account

The selection depends on the functionality that is required for SQL Server 2000. Both services can be configured to use the same Windows user account.

If the service account needs to be changed after the server has been installed, SQL Server Enterprise Manager should be used. While it is also possible to change the service account for the SQL Server and SQL Server Agent services in Control Panel, this is not recommended because the configuration details for the Microsoft Search service are not synchronized.

The changes to account information take effect the next time the service is started. The SQL Server and SQL Server Agent services can be configured to use different Windows user accounts, although this is not usually recommended. When the service account is changed, the changes must be made to both services, because they are configured separately.

One consideration that can reduce administrative overhead in a multiple-server environment is the use of one domain user account for all SQL Server 2000 servers in the enterprise.

Local System Account

SQL Server 2000 can be run using the local system account if SQL Server is not configured for replication and does not require access to network resources.

The following permissions must be set for the local system account for SQL Server 2000 to perform its tasks correctly (Setup assigns these permissions automatically):

Full control on the SQL Server directory (by default C:\Program Files\Microsoft SQL Server\MSSQL)
Full control on all .mdf, .ndf, and .ldf database files

For installations other than named instances, full control on the registry keys at and under:
- HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft \MSSQLServer
- HKEY_LOCAL_MACHINE \SYSTEM \CurrentControlset \Services \MSSQLServer
For named instances, full control on the registry keys at and under the following:
- HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft \Microsoft SQL Server\InstanceName
- HKEY_LOCAL_MACHINE \SYSTEM \CurrentControlset \Services \MSSQL$InstanceName

Local User Account

If SQL Server 2000 is configured to use a Windows local user account, the same restrictions apply as for a local system, with the following addition (Setup grants this by default):

The user account must be granted Log On As A Service permission.

Domain User Account

Configuring SQL Server 2000 with a domain user account provides the greatest level of flexibility. Some examples of functionality available only when a domain user account is used, include:

Replication
Backing up to and restoring from network drives
Performing heterogeneous joins that involve remote data sources
SQL Server Agent mail features and SQL Mail

For SQL Server 2000 to perform its tasks, the domain user account must be configured like the local user account discussed earlier. However, some extended functionality is available only if further permissions are considered. This is best outlined in the following table.

Configuring local user accounts

Service	Permission	Functionality
SQL Server	Network write permissions	Ability to read/write to remote backups, data loads, and so on
SQL Server	Act as part of the operating system and replace process level token	Run xp_cmdshell for a user other than a SQL Server administrator
SQL Server Agent	Member of the Administrators local group	Create CmdExec and ActiveScript jobs belonging to someone other than a SQL Server administrator
SQL Server Agent	Member of the Administrators local group	Use the autorestart feature
SQL Server Agent	Member of the Administrators local group	User run-when-idle jobs

To provide maximum functionality to SQL Server 2000, it is recommended that the domain user account be a member of the Administrators local group.

File System

Windows provides an excellent security framework for securing operating system objects such as files. It is recommended that NTFS file permission be applied to the data and log files of all databases. The user account that SQL Server 2000 is configured to use must be given full control permissions on the database files.

All SQL Server 2000 files, including executables and DLLs, should be configured so that users cannot manipulate them. Permissions on these files should be set to allow the user account that SQL Server uses, the Administrators group, and local system accounts full control permissions. No other permissions should be set.

SQL Server 2000 Setup automatically grants the service account(s) full control permissions to SQL Server-related files, as well as full control to the local administrators group.

Registry

To secure SQL Server 2000 installation from security attacks by users who have login rights on the physical server, it is prudent to set Windows permissions on the registry keys that are used to configure SQL Server 2000.

Specifically, all the keys under the following should be secured:

HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft \MSSQLServer (for a default instance) or
HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft \Microsoft SQL Server\InstanceName (for a named instance)

The Everyone group permissions on this key should be removed, and full control permissions added for the Administrators group, the local system account, or the SQL Server service account. Setup does this automatically for the service accounts selected during the setup process.

Setting permissions on the registry keys is particularly important if SQL Server administrators want to stop the Windows administrators from accessing SQL Server. In this case, SQL Server administrators should also take ownership of the registry key and remove permissions from the Administrators group. In this case, the SQL Server service account must have full control permissions. Although this does not prevent Windows administrators from gaining access, it allows SQL Server administrators to know when the Windows administrators have compromised security. Administrators can always take ownership, but they cannot give it. For more information about Windows administrators gaining access to SQL Server, see "sa Account" in this chapter.

Auditing

SQL Server 2000 provides auditing as a way to trace and record activity that has happened on each instance of SQL Server. This allows audits of logins to the server in the Windows event log. The audit level can be configured using SQL Server Enterprise Manager, or by using the sp_loginconfig stored procedure.

Possible auditing settings are:

None. Logs no auditing information.
Success. Causes only successful logins to be logged.
Failure. Causes only failed logins to be logged.
All. Causes successful and failed logins to be logged.

The auditing information is written to the SQL Server 2000 error log.

Profiling for Auditing

SQL Server 2000 provides a powerful profiler, SQL Profiler, which allows analysis of many internal events within SQL Server, including full security auditing capabilities.

SQL Profiler works by capturing and analyzing all the actions performed on SQL Server. The capture can be viewed real-time on the screen, saved to a text file, or inserted into a SQL Server table.

SQL Profiler allows the capture of virtually all events that take place within SQL Server, including:

End user activity, including all SQL commands, logout/login, and enabling of application roles
Database administrator activity, including DDL, other than GRANT, REVOKE, and DENY; security events, and configuration (database or server)
Security events, including GRANT, REVOKE, and DENY; login user/role, and add/remove/configure
Utility events, including back up, restore, bulk insert, bcp, and database consistency checker (DBCC) commands
Server events, including shutdown, pause, and start
Audit events, including add audit, modify audit, and stop audit

This information can help establish who did what, and when. For more information about how to enable auditing, including how to create an audit stored procedure that runs when SQL Server starts, see SQL Server Books Online.

Another mode of auditing is known as C2 audit mode. C2 audit mode captures all audit-related events and all data columns for those events. This can produce a very large amount of data in a short amount of time, so it is not recommended unless you are setting up SQL Server 2000 in a C2 configuration. For more information, see SQL Server Books Online.

Backup and Restore

Security of Backup Files and Media

The most secure method for backups is to use SQL Server 2000 to back up to data files and then to use the Windows NT 4.0 or Windows 2000 backup program to back up the data files to backup media using the password feature. This ensures that only users who know the password can restore the files. In SQL Server 2000, you can set a password directly on a backup set.

The backup data files should be on an NTFS partition with directory permissions set to prevent the ordinary user from gaining access to the files.

If backup media can be physically secured, the standard SQL Server 2000 backups will not pose any security risks. However, even when the media has a password, the data itself is not encrypted and can be read if the media has no physical protection.

Restoring to Another Server

Three specific scenarios come up when restoring the database to another server. The first scenario applies where the old server (where the database originated from) and the new server (where the database is going) are using Mixed Mode for authentication.

The second and third scenarios apply where Windows Authentication Mode is used; the difference between these is that the second scenario addresses where a database is restored to a server in the same domain, while the third applies to a database being restored to a server in another domain.

Mixed Mode

When a database is restored to a server using Mixed Mode authentication for security, the database security breaks. This is because the logins are maintained in the sysxlogins table in the master database, and the user's rights to access a database are stored in the sysusers table of the respective database; a logical link is maintained between the user's entry in the sysxlogins table and the user's entry in the sysusers table. This link is a generated 16-byte GUID. For more information, see "Generation of GUIDs for Non-Trusted Users" in this chapter.

The net effect of the GUID implementation for Mixed Mode authentication is that when a database is restored to a computer running SQL Server 2000, other than the one where the database access was granted, the link between the sysxlogins table and the sysusers table breaks, thereby effectively granting access to the database to no one. Members of the sysadmin group are an exception to this. All role memberships and user permissions would have to be re-created.

Windows Authentication (Same Domain)

If the database is restored to another computer running SQL Server 2000 in the same domain, the permissions in the database remain intact. The only consideration here is whether users are granted permission to log in to the server. The permission to log in to the server is implemented at each instance of SQL Server.

For example, Bob is a member of the SALES group, and the SALES group is granted login permissions at SQLSERVER1. Bob is granted database access rights to the sales database. When the sales database is restored to SQLSERVER2, Bob's permissions still exist in the sales database. However, because the SALES group is not granted login rights to the server, Bob cannot use the database. If the administrator grants the Everyone group login rights to the server, Bob can use the database. This is because the only restriction stopping Bob from using the sales database was logging in to the server.

When restoring a database to another server in the same domain, the permissions within the database remain intact, but the permissions to log in to this specific server may need to be granted.

Windows Authentication (Different Domain)

When a database is being restored to another domain, some scenarios should be considered. These scenarios apply to users who want to access the database.

Users from a Trusted Domain

If a Windows trust relationship has been established between the old and the new domain, such that the new domain trusts the old domain, the users from the old domain may use the database with all permissions intact, if they have been granted the right to log in to SQL Server.

Users from other trusted domains would not have rights to access the database, much like the users from the new domain.

Users from the New Domain

No users from the new domain will have access, because their SIDs do not exist in the sysusers table of the database.

The only exception to this are the BUILTIN accounts of Windows. Because these accounts always have the same SIDs on all servers, any permissions granted to a BUILTIN account, such as the local Administrators group, remain intact. This assumes that the BUILTIN accounts have login rights, and that SQL Server is installed on a domain controller.

Users from Any Domain with Same Username and Password

In most Windows security implementations, when access is required to a resource that is not in the user's own domain, the user is able to access the resource providing that a user account exists with the same username and password combination. This behavior is transparent.

If the user is using named pipes to connect to the server, this method will work if the user establishes a connection to a file share first. This method also works if a user wants to use an account of another name, providing that the user is running Windows as the operating system. If a user is denied access when connecting to a file resource from a computer running Windows NT 4.0 or Windows 2000 (and the user is not currently using any other credentials on the computer being connected to), the opportunity is given to provide a username and password for login purposes.

Attaching and Detaching Database Files

The issues associated with attaching and detaching database files are identical to those discussed in "Restoring to Another Server" in this chapter. An exception is the requirement to create the database before restoring the data.

General Windows Security Configurations

SQL Server 2000 relies on the Windows security architecture; therefore, all security principles that apply to Windows also apply in some way to Windows-based servers running SQL Server 2000.

Disable Windows Guest Account

When running SQL Server 2000 in Windows Authentication Mode, the server relies on Windows to authenticate all clients. This brings with it the security framework that applies to Windows. To ensure the strength of your security, it is strongly recommended that the Guest account be disabled, if this has not already been done.

Restrict Physical Access

As with any Windows-based server, it is recommended to restrict physical access wherever possible. One of the risks of unauthorized physical access is the ability of an intruder to start the server from a floppy disk and gain access to the Windows file system. Your mission-critical production database servers should be physically secured.

Additional Resources

For more information about SQL Server security, see the following:

Books

SQL Server Books Online (installed as part of SQL Server 2000)

Web Sites

https://www.microsoft.com/sql/default.asp – SQL Server product site

https://support.microsoft.com/default.aspx?scid=fh;[ln];sql – SQL Server Support Center

https://www.microsoft.com/technet/prodtechnol/sql/default.mspx – SQL Server on TechNet

https://msdn2.microsoft.com/sqlserver/default.aspx – SQL Server Developer Center on MSDN®

https://www.microsoft.com/security/ – Microsoft's central site for security information

White Papers

https://msdn2.microsoft.com/sqlserver/default.aspx – SQL Server Developer Center, contains recent technical white papers and downloads

Chapter 10 - Implementing Security

Introduction

New Security Features

Secure Setup

SQL Server 2000 Desktop Engine Setup

C2 Security Evaluation Completed

Kerberos and Delegation in Windows 2000 Environments

Security Auditing

SQL Trace

SQL Profiler

C2-Mode Auditing

Elimination of the SQLAgentCmdExec Proxy Account

Server Role Enhancements

bulkadmin

securityadmin

serveradmin

Encryption

Network Encryption Using SSL/TLS

Login Packet Encryption

Client-Requested Encryption

Server-Requested Encryption

Encrypted File System Support on Windows 2000

Server-Based Encryption Enhanced

DTS Package Encryption

Password Protection

Backups and Backup Media Sets

SQL Server Enterprise Manager

Service Account Changes Using SQL Server Enterprise Manager

SUID Column

Security Model

Authentication Modes

Windows Authentication Mode

Mixed Mode

Using SIDs Internally

Roles

Public Role

Predefined Roles

Fixed Server Roles

Fixed Database Roles

User-Defined Roles

Application Roles

Securing Access to the Server

Windows Level

SQL Server Level

Securing Access to the Database

Securing Access to the Database Objects

User-Defined Database Roles

Permissions System

Granting and Denying Permissions to Users and Roles

Ownership Chains

Implementation of Server-Level Security

Use of SIDs

Elimination of SUIDs

Generation of GUIDs for Non-Trusted Users

Renaming Windows User or Group Accounts

sysxlogins System Table

xstatus Column

dbid and language Columns

hasaccess Status

denylogin Status

sysremotelogins View

sysoledbuser View

Implementation of Object-Level Security

How Permissions Are Checked

Cost of Changing Permissions

Changes to Windows User or Group Account Names

sysprocedures System Table Removed

WITH GRANT OPTION

sysusers System Table

hasdbaccess Column

sysmembers System Table

syspermissions System Table

sysprotects System Table

Named Pipes and Multiprotocol Permissions

Upgrading from SQL Server 7.0

Upgrading from SQL Server 6.5

Upgrade Considerations

Upgrade Process

Analyzing the Upgrade Output

User Has Been Deleted