| CATEGORII DOCUMENTE |
| Asp | Autocad | C | Dot net | Excel | Fox pro | Html | Java |
| Linux | Mathcad | Photoshop | Php | Sql | Visual studio | Windows | Xml |
Abstract: This document introduces Microsoft SQL Server administrators and developers to the new security features of Microsoft 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.
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS DOCUMENT.
Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.
2000 Microsoft Corporation. All rights reserved.
Active Directory, Microsoft, Microsoft Press, MDSN, Visual Basic, Visual C++, Win32, Windows, Windows 2000, Windows Me, and Windows NT, and are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.
The names of actual companies and products mentioned herein may be the trademarks of their respective owners.
Microsoft is a
registered trademark of Microsoft Corporation in the
Other trademarks and tradenames mentioned herein are the property of their respective owners.
Contents
Microsoft SQL Server 2000 Security
New Security Features in SQL Server 2000
C2 Security Evaluation Completed
Kerberos and Delegation in Windows 2000 Environments
Elimination of the SQLAgentCmdExec Proxy Account
Network Encryption Using SSL/TLS
Encrypted File System Support on Windows 2000
Server-Based Encryption Enhanced
Service Account Changes Using SQL Server Enterprise Manager
Eliminated the SUID column in all system tables
SQL Server 2000 Security Model
Using Security Identification Numbers Internally
Securing Access to the Database
Granting and Denying Permissions to Users and Roles
Implementation of Server Level Security
Use of Security Identifiers (SIDs)
Elimination of Server User Identification Numbers (SUIDs)
Generation of GUIDs for Non-Trusted Users
Renaming Windows User or Group Accounts
Implementation of Object Level Security
Renaming Windows User or Group Accounts
sysprocedures System Table Removed
Named Pipes and Multiprotocol Permissions
Preparing SQL Server 6.5 Security Environment
Setting Up a Secure SQL Server 2000 Installation
Security of Backup Files and Media
Windows Authentication (Same Domain)
Windows Authentication (Different Domain)
Attaching and Detaching Database Files
This document introduces Microsoft SQL Server administrators and developers to the new security features of Microsoft 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.
This document is not intended to be a walk-through of the user interface; hence, the implementation of security from a user interface perspective will not be outlined.
For those who will be upgrading servers from SQL Server version 7.0 and earlier, information that explains the security part of the upgrade is provided, as well as notes for those who are knowledgeable in the way security was handled in SQL Server 6.5 and earlier.
Many developers and administrators successfully implemented the security architecture in SQL Server version 7.0 and earlier; however, those who took security seriously were aware that the improvements to security could only be applied as patches until a more thorough integration was possible. SQL Server 2000 builds on the security features in SQL Server 7.0 and provides an improved security solution over those offered with SQL Server 6.5 and earlier.
Microsoft SQL Server 2000 security is based on the Microsoft Windows NT 4.0 and Windows 2000 security model; therefore, a fair understanding of Windows NT 4.0 and Windows 2000 security is assumed throughout this document. An understanding of the concept of domains, global groups, local groups, and user accounts as they apply in the context of Windows NT 4.0 security, as well as Microsoft Active Directory in Windows 2000 is also assumed.
An understanding of SQL Server 6.x security, while advantageous, is not required for most of the document; however, an exception to this is the topic 'Upgrading from SQL Server 6.5,' which discusses how security issues in SQL Server version 6.5 are addressed in SQL Server 2000.
Those who are familiar with the system tables in SQL Server 7.0 and earlier will have an advantage in comprehending the various concepts in 'Security Internals
For those interested in the code examples, exposure to Microsoft Visual Basica and Structured Query Language (SQL) will enhance understanding. In addition, experience using SQL Distributed Management Objects (SQL-DMO) provides a significant advantage.
However, it is hoped that even those who have had limited exposure to Windows NT 4.0, Windows 2000, or SQL Server will gain a significant amount of understanding about the workings of security when using these products. This is particularly true of the topics 'New Security Features in SQL Server 2000' and 'SQL Server 2000 Security Model.'
The first part of this paper examines the new security features of SQL Server 2000, and gives you a good overview of how security has been enhanced in this release.
SQL Server 2000 Setup is now secure out of the box. When you run SQL Server Setup in any edition (except Microsoft SQL Server Desktop Engine), the following dialog box appears (Figure 1).
Figure 1: The Authentication Mode dialog box defaults to Windows Authentication Mode during setup of SQL Server 2000.
You must select the authentication mode for SQL Server 2000. The default is Windows Authentication Mode, which 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.
Note: The default authentication mode in SQL Server version 7.0 and earlier was Mixed Mode with a blank sa login password.
When installed on a Windows NT4.0 or Windows 2000 operating system using the NTFS file system, SQL Server Setup will secure the directories that SQL Server installs into to just the service accounts selected for the SQL Server services, and the built-in administrators group. By default, the directory will be C:Program FilesMicrosoft SQL ServerMSSql. Additionally, the SQL Server registry keys (starting at HKLMSoftwareMicrosoftMSSQLServer, or HKLMSoftwareMicrosoftMicrosoft SQL ServerMSSQL$InstanceName for a named instance) will also be secured to the service account(s) selected during SQL Server Setup.
Microsoft 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 Windows 98 or 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 line for setup (or in the .ini file) as documented in 'SQL Server 2000 Desktop Engine Setup' in SQL Server Books Online for SQL Server 2000.
As noted in the readme.txt for SQL Server 2000: SQL Server Books Online topics 'Merging the Desktop Engine into Windows Installer' and 'SQL Server 2000 Desktop Engine Setup' document two parameters that are ignored by the final version of the Desktop Engine Setup: USEDEFAULTSAPWD and SAPASSWORD.
Therefore, the sa login password of a Desktop Engine setup when the Desktop Engine is set up with Mixed Mode authentication will always be blank, and the password should be changed immediately after installation.
SQL Server 2000 has been evaluated and has met the C2 security certification for the evaluated configuration from the United States Government. For information about the configuration for a C2-compliant system, see https://www.microsoft.com/technet/security/sqlc2.asp. For an announcement about the C2 certification see https://www.radium.ncsc.mil/tpep/epl/entries/TTAP-CSC-EPL-00-001.html.
Kerberos is the primary authentication mechanism on Windows 2000 networks. Delegation is the ability to pass security credentials across multiple computers and applications. With each 'hop' between computers, the user's security credentials are preserved. SQL Server 2000 fully supports Kerberos, including the ability to accept delegated Kerberos tickets, as well as delegate these tickets further (when running on the Microsoft 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/guide/server/features/securitysvcs.asp and review the relevant technical papers.
To configure delegation, all servers that you are connecting to must be running Microsoft Windows 2000 with Kerberos support enabled, and you must be using 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 Microsoft 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 documentation.
To create an SPN for SQL Server 2000
Run the following command:
setspn
-A MSSQLSvc/Host:port serviceaccount
For example:
setspn
-A MSSQLSvc/server1.redmond.microsoft.com sqlaccount
You must be using 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.
One of the requirements for the United States 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 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 in to the same mechanism that was 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 rearchitected 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 for SQL Server 2000.
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 for SQL Server 2000.
SQL Profiler
SQL Profiler is the graphical interface utility that allows you to view the audit trace files, and then perform selected actions on those files. You can search through the files, save the files out to a table, and create and configure trace definitions using the user interface. SQL Profiler is a client to SQL Trace, and you do not need to have SQL Profiler running to perform a security audit.
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 for which events are audited (all security events), which data columns are captured (all that might have information from these events), and other settings that are fixed. Each setting is documented in SQL Server Books Online as well as the Trusted Facilities Manual for SQL Server 2000, available at https://www.microsoft.com/Downloads/Release.asp?ReleaseID=24481.
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 that did not have system administrator privileges could get access to 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 that SQL Server was installed on 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 rather than resources that were strictly local to the computer on which you installed SQL Server.
There have been minor enhancements to the fixed server roles that are included with SQL Server 2000. For information about fixed server roles, see the topic 'Predefined Roles' in this document.
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 will have the ability to load data from any file on the network and from any computer the server is running on that the SQL Server service account is allowed to access. Membership should be considered carefully. Members of this role are still required to have the INSERT permission on any table they want as the target of the BULK INSERT command. Only the permission to execute the BULK INSERT statement and the right to access files during the execution of this command is granted by membership in this fixed server role.
SecurityAdmin
The SecurityAdmin role has the right to 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, this would make the SecurityAdmin role more useful for someone in a help-desk job who does not need full system administrator access to SQL Server.
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.
SQL Server 2000 now automatically supports encryption of the data and other network traffic as it travels between the client and server systems on a network. The encryption strength is dependent upon 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 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 such as Verisign), and then install it on the server in the location suggested when you import the certificate.
Login Packet Encryption
During any login attempt, if a certificate is present on the server and is usable (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 no configuration of the server other than installing the certificate 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 (Force Protocol Encryption), and applies to all outbound connections from that computer. The Client-Requested Encryption option also prevents access to SQL Server 7.0 and earlier, and 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 (Force Protocol Encryption). 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.
SQL Server 2000 works correctly on Windows 2000 when protecting the data files with the Encrypted File System support that is built into the operating system. You must encrypt the files using the service account of SQL Server.
To change the service account, you must decrypt the files, change the service account for the SQL Server services, and then re-encrypt the files with the new service account. If you do not do this, 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).
As part of a comprehensive review of our use of encryption all server-based encrypted data (passwords, encrypted stored procedures, and so on.) have been enhanced to use the Windows Crypto API. This ensures more robust and secure storage of protected items within SQL Server.
Data Transformation Services (DTS) packages are now encrypted using the Windows Crypto API.
In SQL Server 7.0, if you specify a password on a DTS package, it is encrypted using the password as a key. If you do not specify a password, the package is not protected. In SQL Server 2000, all packages are encrypted whether or not a password is supplied.
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 that does not honor the Microsoft Tape Format can ignore the password and give you access to the data within the backup. All SQL Server restore mechanisms honor the password.
In SQL Server 2000, passwords for authenticated logons are always encrypted using the Windows Crypto API. In SQL Server 7.0, these passwords were stored in an unprotected format in the client computer's registry.
When you change the service account for the SQL Server services (SQL Server or SQL Server Agent) using SQL Server Enterprise Manager, the utility reset File & Directory permissions (when data is stored in the NTFS file system). 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 administrator's group). The password is reset in the services database (just as if you would reset the account information in Control Panel in Services), and then the appropriate Windows NT 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.
The SUID column was present in the following system tables in earlier releases:
Sysdatabases
Syslogins
Sysremotelogins
Sysusers
Sysprocesses
sysalternates.
This column was not used in SQL Server 7.0 (it was superseded by the SID column) but was preserved for backward compatibility. To reduce confusion, this column has been removed. In the case of the sysalternates table, the table was eliminated (because it contained only relationships between SUIDs).
To implement security in the most practical ways on SQL Server 2000, it is important to understand how the design team expected the security model to be implemented. Those familiar with Windows security will notice the power that Windows users and groups bring to SQL Server 2000.
Security on SQL Server 2000 should be implemented as outlined in the following diagram (Figure 2).
Figure 2: Windows users and groups offer a powerful and flexible security model for SQL Server administrators.
The steps in the diagram are summarized as follows
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.
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.
The Windows local group is assigned permissions on the specific database objects.
The other approach to security is based on the use of roles, and is usually implemented in the following way (Figure 3).
Figure 3: Role-based security is another security option in SQL Server 2000.
When using roles to assign object permissions, individuals must still be granted permissions on the server and the database using the recommended approach.
Steps 1 through 4 are the same for both diagrams, except that multiple Windows global and local groups would probably not be created. Windows 2000 Universal groups are fully supported as well.
Step 5: Individual Windows accounts and Windows groups are assigned to a role.
Step 6: Object permissions are then assigned to the roles.
Roles reduce the requirements of grouping users within Windows, by grouping the users within SQL Server 2000.
Microsoft SQL Server 2000 provides two authentication modes for securing access to the server: Windows Authentication Mode and Mixed Mode.
Note: The Standard mode of authentication in SQL Server 6.5 was discontinued in SQL Server 7.0.
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 the Windows authentication of the user. Windows users or groups are then granted access to the SQL Server.
Windows Authentication Mode allows SQL Server 2000 to rely on Windows to authenticate users in much the same way as other applications. 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 logons. As Windows SIDs are used, the database administrator can grant logon 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.
In SQL Server 2000, Mixed Mode relies on Windows to authenticate users when the client and server are capable of using NTLM , 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 two reasons: backward compatibility and when SQL Server 2000 is installed on the Windows 98 or Windows Me operating systems. Trusted connections are not supported on Windows 98 or Windows Me computers when they are the server.
SQL Server 2000 uses security identification numbers (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 also to access the pubs database. An administrator could grant access to the authors table for Jane by her Windows name, REDMONDJane. 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 my login is domain SALES, user SOMEONE, the login to SQL Server would be SALESSOMEONE, and you could not use the login in the form of SOMEONE@MYCOMPANY.COM as supported by Windows 2000 Active Directory.
Roles are used much like Windows groups are used. Roles allow users to be collected into a single unit against which permissions can be applied. Permissions granted, denied, or revoked from a role also apply to any members of the role. Roles can 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.
There are a number of key concepts that make roles so powerful. First, with the exception of fixed server roles, they are implemented within a database. This means that the database administrator is not reliant 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.
The public role exists in every database, including the system databases master, msdb, tempdb and model. The public role provides the default permissions for users in a database and cannot be deleted. Functionally, it can be compared to the Everyone group in the Windows NT 4.0 environment. Every database user is a member of this role automatically; therefore, users cannot be added or removed from this role.
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 BUILTINAdministrators group (the local administrator's group) are members of the sysadmin role by default.
The following table (Figure 4) lists the fixed server roles found in SQL Server 2000.
|
Fixed Server Role |
Description |
|
Sysadmin |
Performs any activity in SQL Server. |
|
Serveradmin |
Configures server-wide configuration options, shuts down the server. |
|
Setupadmin |
Manages linked servers and startup procedures. |
|
securityadmin |
Manages server-wide security settings, including linked servers, and CREATE DATABASE permissions. Resets passwords for SQL Server authentication logins. |
|
processadmin |
Terminate processes running in SQL Server. |
|
dbcreator |
Creates, alters, drops, and restores any database. |
|
diskadmin |
Manages disk files. |
|
Bulkadmin |
Allows a non-sysadmin user to run the bulkadmin statement. |
Figure 4: SQL Server 2000 fixed server roles.
To add users to the fixed server roles, use the following Transact-SQL statement:
/* Add Bob to the sysadmin server role */
exec sp_addsrvrolemember 'REDMONDBob', 'sysadmin'
Windows users and groups can be added to server roles.
The following code shows how a user is added to a server role using 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 ('REDMONDBob')
For more information about the use of fixed server roles, see SQL Server Books Online for SQL Server 2000.
Fixed Database Roles
Fixed database roles are defined at the database level and exist in each database. Members of the db_owner and db_security admin roles can manage fixed database role membership; however, only the db_owner can add others to the db_owner fixed database role.
The following table (Figure 5) lists the fixed database roles found in SQL Server 2000.
|
Fixed Database Role |
Description |
|
db_owner |
Performs all maintenance and configuration activities in the database. |
|
db_accessadmin |
Adds or removes access for Windows users, groups, and SQL Server logins. |
|
db_datareader |
Reads all data from all user tables. |
|
db_datawriter |
Adds, deletes, or changes data in all user tables. |
|
db_ddladmin |
Runs any Data Definition Language (DDL) command in a database. |
|
db_securityadmin |
Modifies role membership and manages permissions. |
|
db_backupoperator |
Backs 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. |
Figure 5: SQL Server 2000 fixed database roles.
For more information about the use of fixed database roles, see SQL Server Books Online for SQL Server 2000.
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 Microsoft Windows group, or if the database administrator does not have 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 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 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 and reconnect to SQL Server.
Application roles work with both authentication modes, and contain no members. Users cannot be associated with application roles, as the application requests the application role's security context using the sp_setapprole stored procedure.
Similar to user-defined roles, application roles exist only within a database. If, while an application is in the security context of an application role, another database is accessed, the access to the other database is, by virtue of permissions, granted to the guest account in that database. If the guest account has not been specifically granted access to the data, or does not exist, the objects cannot be accessed.
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 permitted to gain access only to the data in SQL Server 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 Windows group 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.
This procedure outlines how an application can make use of application roles. To use application roles, perform the following steps:
Create an application role.
Assign permissions to the application role.
Ensure the client application connects to SQL Server 2000.
Ensure 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 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',
The encryption of the password is optional, but ensures greater security when the password has to pass through a wide area network (WAN).
Here is the code in Visual Basic:
' 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 servers Role collection.
oServer.Databases('pubs').DatabaseRoles.Add oDbRole
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', , 'ODBC''
The encryption style (last parameter) must be set for OLE DB and ODBC data sources. All 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.
Implementing 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.
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 accounts 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.
The global group and local group requirements outlined earlier may seem like a lot for small, single domain networks; however, experience has shown that there is usually 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.
Although the recommendations are strong, the database administrator is able 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 document. This can be achieved by using the ADSI object model from Microsoft Visual Basic, or by interfacing directly to 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 on to the server can be granted through the user interface or implemented programmatically using Microsoft Visual Basic or Transact-SQL.
New stored procedures have been written to allow the granting of access for Windows users and groups. These security-related stored procedures are listed here (Figure 6).
|
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 |
Figure 6: SQL Server 2000 security-related stored procedures.
The following Transact-SQL statement grants login rights to the SALESLG local group:
/* Grant login. */
exec sp_grantlogin
'REDMONDSALESLG'
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 = 'REDMONDSALESLG'
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 the 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.
Alternatively, the above can also be achieved 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
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, we do not differentiate between non-trusted users, Windows users, and Windows groups. When reference is made to Windows user or groups, they can also be 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 (which is a Microsoft Management Console (MMC) snap-in for administering SQL Server 2000) does not allow the creation of users who do not have specific logon permissions. The MMC creates a list of all accounts that have been granted the permission to log on 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 logon, Windows user, or Windows group can be granted the rights to access the database, whether or not a specific logon 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 logon name.
Some examples for the Transact-SQL statements required to grant permission to use a database are:
/* Grant access to Bob. */
exec sp_grantdbaccess 'REDMONDBob'
/* Grant access to Wendy, referring to her by first name within this database. */
exec sp_grantdbaccess 'REDMONDWendyH', '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 = 'REDMONDBob'
' Add the User object to the servers Users collection.
oServer.Databases('pubs').Users.Add oUser
Securing Access to the Database Objects
Permissions can be granted to roles and users, and 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 are dependent 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.
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 installing SQL Server 2000 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 servers Role 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', 'REDMONDBob'
/* Add a Windows
group to the TelephoneOperators role. */
exec sp_addrolemember 'TelephoneOperators', 'REDMONDSales'
/* Add
HelpDeskOperators role to TelephoneOperators role. */
exec sp_addrolemember 'TelephoneOperators',
'HelpDeskOperators'
And again with 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 TelehoneOperators role collection.
.AddMember
('REDMONDBob')
' Add the Windows group to the TelehoneOperator's role collection
.AddMember ('
' Add the HelpDeskOperators role to TelehoneOperators role collection.
.AddMember
('HelpDeskOperators')
End With
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 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.
Permissions within a database are always granted to database users, roles, and Windows users or groups, but never to SQL Server 2000 logons. 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 be fair to remove permissions from all users, as 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 DENY the INSERT, UPDATE, and DELETE operations on all tables for this role. As users misbehave, they are put into 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 prohibits 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 SELECT. */
GRANT SELECT
ON authors
TO Bob, [REDMONDJane]
GO
/* Grant permissions to INSERT. */
GRANT INSERT
ON titles
TO [REDMONDJane]
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, _
'[REDMONDJane]
' Grant Jane permissions to select from the authors table.
oServer.Databases('pubs').Tables('authors').Grant SQLDMOPriv_Select, _
'[REDMONDJane]'
In the previous examples, there is little difference between granting access to a user by fully qualifying their domain name, and granting access to a user who already has permissions to access the database directly. Due to these similarities, the following examples will 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 SELECT. */
DENY SELECT
ON authors
TO Bob
GO
And again 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 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 permissions to select from the authors table.
oServer.Databases('pubs').Tables('authors').Revoke SQLDMOPriv_Select, 'Bob'
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 is 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, there is 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. This is 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.
An example where SQL Server 2000 implements the power of ownership chains is in the implementation of passwords. Users are not allowed to update the system tables directly, especially not those found in the master database. When using SQL Server 2000 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 maintain control over who is authorized to access it. At the same time, performance is increased because permissions do not require checking if the ownership chain is not broken.
SQL Server 2000 checks whether the user's security identifier (SID) or group membership SIDs have been specifically denied access to the server. If so, the user will not be granted 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, the connection to SQL Server 2000 is maintained. The user then proceeds to the appropriate default database (where the user must also have been granted access). The user's access rights are then checked for any objects that are attempted to be accessed. If access has not been granted for a particular set of logon credentials, the connection to the server is terminated.
When a Windows NT 4.0 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.
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 - if you call one of them they will always return NULL.
For non-trusted connections, such as when SQL Server 2000 is installed on the Windows 98 operating system, Windows SIDs are not available. 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.
When a Windows user or group is renamed using the User Manager for Domains tool in Windows NT 4.0 or the Active Directory Users utility, SQL Server 2000 is unaware of that change. SQL Server 2000 maintains the user or group's fully qualified name in the sysxlogins table for performance reasons, as it can be very slow to query the domain controller for this information. This is true when a lot of 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 any security problems. The permissions set for the user or the group continue to function correctly, as 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 it 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.
Another effect of using these functions is that the usernames in system messages may not report an up-to-date name.
The sysxlogins system table contains the login permission (or lack of) for users. . This 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; while at the same time interprets the status column so that it 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 regarding 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 (Figure 7):
|
Purpose |
Notes |
|
|
Denylogin |
||
|
Hasaccess |
||
|
Isntname |
Not 'ISN'T', but 'IS WINDOWS' |
|
|
Isntgroup |
Only if status bit 4 is not set |
|
|
Isntuser |
Must also have status bit 3 set |
|
|
Sysadmin |
Server role |
|
|
Securityadmin |
Server role |
|
|
Serveradmin |
Server role |
|
|
Setupadmin |
Server role |
|
|
Processadmin |
Server role |
|
|
Diskadmin |
Server role |
|
|
Dbcreator |
Server role |
|
|
Bulkadmin |
Server role |
Figure 7: SQL Server 2000 security status values.
dbid and language Columns
An area that is often misunderstood is how a user obtains the default database and default language settings. 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 found, the default database and default language settings are taken from the found row. If one was not found, the server looks for SIDs that belong to the groups of which this user is a member. The default database and default language settings from the first group that is found are used. This implementation is specific to each default.
If the first group that is found (of which the user is a member) contains a default language, but the default database is NULL, SQL Server continues to the next group (of which this user is a member), 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 are not guaranteed.
It is possible to assign default database and language settings specifically to a user without granting specific login rights to that user. The user would be permitted access to SQL Server based on group memberships, but would 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 is used to allow default settings to be configured 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 REDMONDSALES group, and has not been specifically granted permission to log in to SQL Server 2000. There are no entries for Bob in the sysxlogins table. However, the REDMONDSALES 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 SQL 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.
Another situation where the sysxlogins table contains entries that are not specifically granting access to a user or group is when the denylogin flag is set.
denylogin Status
The denylogin status is used to mark a user or group as being explicitly denied access to SQL Server 2000. For example, if a specific user (or group) should have their access to SQL Server prevented, the following Transact-SQL statement can be executed:
Exec sp_denylogin 'REDMONDBob'
This is not the same as:
Exec sp_revokelogin 'REDMONDBob'
The difference between the two statements is that the first statement denies access to SQL Server, the second revokes access using that particular account. If the user is a member of the MARKETING group, which does have access, the second statement would still allow Bob to continue accessing 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 on to the linked server on behalf of the user.
The sp_addlinkedsrvlogin stored procedure is used to add 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.
SQL Server 2000 uses SIDs to identify Windows users and groups. However, due to the length of SIDs (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) structure in memory, which is comprised of 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 PSS structures.
When the user proceeds to access a database, SQL Server checks the sysusers table to ascertain 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, otherwise the sysusers table is checked again, but this time all user IDs are collected for which the user qualifies. After it is established that the user has been granted access to the database, the sysmembers table is scanned, so that all the role memberships of the user can be established. For example, the user may be a member of a role, a member of a Windows group or aliased to another user. The user IDs of all the applicable memberships are established so that the appropriate permissions for this user can be applied. Unlike the PSS structures, this information is not persisted.
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, and if found, the user will not get the requested access to 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 repeated access to the same objects by the same user do not continue to incur the cost of checking the access permissions.
As stated earlier, SQL Server 2000 caches the object permissions on a per-session basis to avoid incurring the cost of checking permissions for repeated access of the same objects. Unlike the PSS, which does not change the security information after it is created, the permissions cache is always up-to-date. This implemented by the versioning method.
When the initial checking of permissions takes place, a version number is established. When the permissions on an object are changed, SQL Server 2000 increases the version counter. 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, providing that the version counter has not changed. If the counter has changed, a small overhead is incurred for that operation.
With SQL Server 2000 it is possible to grant Windows users and groups permissions to access objects in the database directly. In that case, the SID and Windows user or group names are stored in the sysusers table.
When the Windows administrator renames the Windows group or user, the name change is not propagated to SQL Server 2000.
Despite the fact that this seems to be a significant problem, it is actually more of a solution to what would otherwise become chaos.
In SQL Server 2000, as with earlier versions, administrators and developers are writing numerous stored procedures, Transact-SQL scripts, triggers, and so on. Assume that 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 gets married to Bob Taylor, her username is renamed 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. The views, stored procedures, and any code that was written to access this table would break.
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.
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 sysprocedures table and used as the basis for an optimized execution plan, which was then stored in the procedure cache.
At first it may appear that there is no connection between the process outlined earlier and security. However, the relevance of this to security is based on the fact that some software developers , in an attempt to protect their source code, deleted the original SQL text from syscomments. In most cases, the original SQL text really 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.
In SQL Server 7.0 or SQL Server 2000, any administrator who deletes the appropriate entries from the syscomments table will find that the stored procedure no longer executes. This is because the sysprocedures table has been removed from SQL Server 2000, which now obtains the SQL text directly from the syscomments table prior to execution.
The WITH GRANT option is optional syntax that can be used with the GRANT statement. This option applies only to object permissions, and provides the recipient of the GRANT statement with the ability to pass on that permission.
For example, if Bob granted Jane SELECT permissions and used the WITH GRANT OPTION, Jane would be able to grant SELECT permissions to some of her friends.
When Bob revokes SELECT permissions from Jane, he can use the CASCADE option to revoke the SELECT permissions from those friends to whom Jane had granted SELECT permissions.
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 used in a similar way to the hasaccess column in the sysxlogins table. In this case, 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, and are not owned by the group through which the user was granted access to the database. An exception is when a user, who is a member of a role or Windows group, explicitly qualifies the role or group as the object's owner when the object is created. 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 does not get explicit access to the database automatically because the hasaccess flag is set to zero.
Roles, which are also listed in the sysusers table, have the hasdbaccess column set to zero.
The sysmembers system table is one of the smaller tables. It contains two columns, and is used to record 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 member 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.
The syspermissions system table, which exists in every database, was introduced in SQL Server 7.0. In earlier versions, the sysprotects system table was used to maintain all permissions. Now the syspermissions table is used to track permissions that have been granted or denied to users. For more information about backward compatibility with the sysprotects table, see 'sysprotects System Table.'
The syspermissions system table consists of 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 column, grantee and grantor are self-explanatory. The value used here is 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 for SELECT granted permissions, and is a bitmap of the columns that have been granted this permission. As column IDs are never reused, the bitmap approach works very well. The selmod column is for SELECT permissions denied.
The next two columns are implemented in the same way as the previous two, except that they apply to UPDATE permissions.
The last two columns refer to the REFERENCES permissions, and are implemented in the same way as the previous four columns.
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 now stored in the syspermissions table.
In most cases, where the implementation of a system table changed, Microsoft provided a view that provided backward compatibility. One example of this is the implementation of the master..sysxlogins table; the syslogins view is now provided for backward compatibility.
In the case of the sysprotects system table, however, the change in the underlying system table was much more major, and a view was not able to provide backward compatibility efficiently. For this reason, Microsoft opted not to implement a sysprotects view, but to create a special table named sysprotects, which appears as a normal table to the system, but is really created dynamically when required.
The sysprotects table is therefore somewhat like a view, as it does not actually have any persisted data pages. However, because the view-like behavior is implemented in the database engine, the sysprotects object appears as a table in the sysobjects table.
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 of understanding.
The Named Pipes Net-Library is an inter process communications (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. This was mentioned in passing during the discussion of the new security features. 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.
It is important that all users who will be connecting to SQL Server 2000 using the Named Pipes Net-Library have a Windows account and have Windows permissions 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, as these connections are not validated against the Windows NT IPC$ share. Again, note that TCP/IP Sockets is the default network library in SQL Server 2000.
When using the Multiprotocol Net-Library, Windows authentication also takes place before SQL Server 2000 passes the connection. This is because the remote procedure call (RPC) runtime services authenticate the client when the connection is requested. In much the same way as with the Named Pipes Net-Library, the Multiprotocol Net-Library requires a valid Windows account.
Note that the multi-protocol network library does not work to connect to named instances of SQL Server 2000, and is no longer required since 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 as 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, and is only mentioned here as a work-around of last resort.
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 document.
The security model for SQL Server 6.5 changed from SQL Server 6.0, and has changed again in 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, Microsoft recommends that the new security functionality available as part of Windows Authentication Mode be used in the upgraded environment. The best method of insuring that the security settings of SQL Server 6.x are upgraded in the most constructive way possible, is to plan the upgrade thoroughly and prepare the security environment.
The upgrade process can be performed 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 as source and target servers. The source server is expected to have SQL Server 6.0 or SQL Server 6.5 installed, and the target server is required to have SQL Server 2000 installed.
During the version upgrade process, a program is executed that 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 can be from a local domain (if SQL Server 2000 is running on a member server, or they could be accounts from a trusted domain). If SQL Server 2000 is installed on a domain controller, the local groups would be the local groups from the domain controller's domain; otherwise, the local groups would be the local groups of the member server.
Accounts that were given administrative permissions on the source server are ignored in the drill-down and account-mapping processes.
Note: The sp_grantlogin statement will be executed on the computer running SQL Server 2000 for each Windows account that was configured to use integrated security on SQL Server 6.5. The SQL Security Manager that is included with SQL Server 6.5 actually executed xp_grantlogin, so the upgrade process is mimicking what was done in the earlier version.
Most of the security-related upgrade difficulties are based on the fact that in SQL Server 6.5 integrated security was implemented by securing a key in the registry, and only those who had access to that key could gain access to the server. For more information, see 'Use of Security Identifiers (SIDs)' in this document. The permissions on the registry key were linked to user login accounts, which were stored in the syslogins table.
SQL Server 2000 no longer relies on this method for securing access to the server. Instead, it allows granting of access to the server based on Windows user or group SIDs. Therefore, the upgrade process is sometimes unable to identify what the original security requirements were. 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 (Figure 8) has been compiled to explain how logins appear in SQL Server Enterprise Manager after an upgrade.
|
Name |
Type |
Server Access |
Default DB |
User |
Line |
|
user1 |
Standard |
Permit |
Master | ||
|
a#user2 |
Standard |
Permit |
Master | ||
|
BUILTINAdministrators |
Windows Group |
Permit |
Master |
dbo | |
|
DOM3SQLUsers |
Windows Group |
Permit |
Master | ||
|
DOM3_user#3 |
Standard |
Permit |
Master | ||
|
DOM3_Administrator |
Standard |
Permit |
Master | ||
|
REDMONDa user4 |
Windows User |
Via Group |
Master | ||
|
REDMONDuser5 |
Windows User |
Via Group |
Master |
Figure 8: Post-upgrade logins as displayed in SQL Server Enterprise Manager following an upgrade to SQL Server 2000.
The contents of this table are explained in the sections that follow.
User Has Been Deleted
Rows 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 '#' character in row 2 is used to represent the space, as SQL Server 6.5 and earlier did not support special characters.
Administrator Account
The BUILTINAdministrators local group in row 3 of the previous table has been aliased to the dbo user of the master database.
Users of a Trusted Domain
Row 4 in the table refers to the DOM3SQLUsers group, a global group on a trusted domain. The members of this group have been granted login rights. However, the members of this group have also been granted login rights using standard security using their username as they would have appeared in SQL Server 6.5 and earlier. This has been done to provide backward compatibility for standard mode security.
Notice the entry for the Administrator account in row 6; the Windows Administrator account of the DOM3 domain was granted user login rights prior to the upgrade. This has been preserved. All user level logon access will be 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 rows 7 and 8. Notice the type of the account and the existence of the space in row 7. SQL Server 2000 now supports special character in the account names.
Microsoft strongly recommends 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 in sync with SQL Server. If the environment is in order, the upgrade process has the highest possible opportunities to complete properly.
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. The SQL Server Upgrade Wizard allows the process to be stopped after every step. If the option to stop after each step 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 prior to continuing.
Note: Microsoft does not support the editing of loginmap.sid and loginmap.txt files during the upgrade process.
Do Not Upgrade 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.'
Character Mapping Not Required
Character mappings are not required; SQL Server 2000 can deal with spaces and backslashes in account names.
In SQL Server 6.5 and earlier, character mappings 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: '#', '_' and '$'.
Do Not Use sa Account
In SQL Server 6.5 and earlier, administrators logged in to SQL Server using the system administrators (sa) account to perform most administrative tasks. This often required a large number of people to have administrative access.
All Windows NT users who are given sa-type rights on SQL Server 2000 should be assigned to the sysadmin fixed server role.
For more information, see 'Do Not Use the sa Account' in this document.
Do Not Use Aliasing
SQL Server 2000 supports aliasing of user accounts within a database for backward compatibility, and its implementation is no longer recommended. Microsoft now recommends the use of roles. Roles are more powerful, and they provide similar functionality to aliasing. For more information, see 'User-Defined Roles' in this document.
The information discussed in this section applies to SQL Server 2000 installed on Windows NT or Windows 2000 only, as 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 (the default setup).
Do Not Use the sa Account
Microsoft recommends 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, but rather, that a password be assigned; the password should be hard to break, and then be locked in a safe for emergency access only.
If you are running SQL Server 2000 with Windows Authentication Mode (as recommended in this document) you cannot log on using the sa account, as 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 can change the security mode from Authentication Mode to Mixed Mode. If the sa password is blank (as per a default installation), an intruder (or the Windows Administrator) would be able to gain access to the server. For information on ways to reduce the chance of such an attack, see 'Registry'.
SQL Server 2000 runs as three Windows services:
MSSQLServer (or MSSQL$InstanceName for a named instance). The engine that provides the core functionality of SQL Server.
SQLServerAgent (or SQLAgent$InstanceName for a named instance). Provides the capability to schedule regular commands, schedule replication, provide 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.
The 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 kept in sync.
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 changing the service account, the changes must be made to both services, as they are configured separately.
One consideration that can reduce administrative overhead in a multi-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 the 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 properly (setup assigns these permissions automatically):
Full Control on the SQL Server directory (by default Program FilesMicrosoft SQL ServerMSSQL)
Full Control on all .mdf, .ndf, and .ldf database files
Full Control on the registry keys at and under:
HKEY_LOCAL_MACHINESoftware
MicrosoftMSSQLServer
HKEY_LOCAL_MACHINESystem
CurrentControlsetServicesMSSQLServer
Or for a named instance,
HKEY_LOCAL_MACHINESoftware
MicrosoftMicrosoft SQL ServerInstanceName
HKEY_LOCAL_MACHINESystem
CurrentControlsetServicesMSSQL$InstanceName
Local User Account
If SQL Server 2000 is configured to use a Windows local user account, the same restrictions apply as for local system, with the following addition (setup grants this by default):
The user account must be granted the 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 as 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 (Figure 9).
|
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. |
Figure 9: Permissions required for extended functionality in SQL Server 2000.
To provide maximum functionality to SQL Server 2000, it is recommended that the domain user account be a member of the Administrators local group.
Windows provides an excellent security framework for securing operating system objects such as files. Microsoft recommends 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 dynamic link libraries (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 the SQL Server-related files, as well as full control to the local administrators group.
To secure the SQL Server 2000 installation from security attacks by users who have logon 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 HKEY_LOCAL_MACHINESOFTWAREMICROSOFTMSSQLSERVER (for a default instance) or HKEY_LOCAL_MACHINESOFTWAREMICROSOFTMICROSOFT SQL SERVERINSTANCENAME (for a named instance) should be secured. 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 the SQL Server administrators want to stop the Windows administrators from accessing the SQL Server. In this case, the SQL Server administrators should also take ownership of the registry key, and remove permissions from the Administrators group. It is then imperative that the SQL Server service account has Full Control permissions. Although this does not stop 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 regarding Windows administrators gaining access to SQL Server, see 'Do Not Use the sa Account' in this document.
SQL Server 2000 provides the capability to audit logons to the server in the Windows event log. The audit level can be configured using SQL Server Enterprise Manager, or by using the xp_loginconfig extended 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.
SQL Server 2000 provides a very powerful profiler, SQL Profiler, which allows the analysis of many internal events that occur within SQL Server, including full security auditing capabilities.
SQL Profiler works by capturing all the actions performed on the SQL Server, and then analyzing those actions. 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 capturing of virtually all events that take place within SQL Server, including:
End user activity (all SQL commands, Logout/Login, enabling of application roles).
DBA activity (DDL, other than GRANT/REVOKE/DENY and security events, and configuration (DB or server)).
Security events (GRANT/REVOKE/DENY, login user/role add/remove/configure).
Utility events (backup/restore/bulk insert/bcp/DBCC commands).
Server events (shutdown, pause, start).
Audit events (add audit, modify audit, stop audit).
This information can provide excellent support to establish who did what, and when. For more information about how to enable auditing, such as creating an audit stored procedure and marking it at startup, see SQL Server Books Online for SQL Server 2000.
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 is not recommended unless you are setting up SQL Server 2000 in a C2 configuration. For more information, see SQL Server Books Online for SQL Server 2000.
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 backup program to back up the data files to backup media using the password feature. This ensures that only those who know the password can restore the files. SQL Server 2000 now supports setting 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.
Three specific situations will be addressed with regard to restoring the database to another server. The first situation 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 situations apply where Windows Authentication Mode is used; the difference between these is that the second situation addresses where a database is restored to a server in the same domain, while the third scenario applies to a database being restored to a server in another domain.
Mixed Mode
When restoring a database to a server using Mixed Mode Authentication for security, the database security breaks. This is because the logons 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 document.
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.
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 on to the server. The permission to log on 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.
When restoring a database 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, provided that 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
None of the users form the new domain will have access, as their SIDs do not exist in the sysusers table of the database.
The only exception to this are the BUILTIN accounts of Windows. As 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 logon 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/password combination. This behavior is transparent.
Provided that the user is using named pipes to connect to the server, this 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 computer operating system. If a user is denied access when connecting to a file resource from a computer running the Windows NT 4.0 or Windows 2000 operating system (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 logon purposes.
The issues associated with attaching and detaching database files are identical to those discussed in 'Restoring to Another Server' in this document. An exception is the requirement to create the database before restoring the data.
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 perform all authentication of clients. This brings with it the security framework that applies to Windows, both the strengths and the weaknesses. Fortunately, there are not many of the latter. However, one issue that has been adequately documented in many Microsoft and third-party security papers is the use of the Windows Guest account. It is strongly recommended that the Guest account be disabled, if this has not already been done.
For more information, see the TechNet resource listed in Appendix A, 'Web Sites.'
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 by an intruder to start the server from a floppy disk and gain access to the Windows NT file system. Your mission-critical production database servers should be physically secured.
With the release of SQL Server 2000, database administrators have the power to configure and manage secure database servers that tightly integrate with Windows security.
Programmers have also been provided with the means to develop secure applications without increasing the development overhead.
For Microsoft, this release could not have come at a better time; a time when the uptake of databases is exploding in the Internet commerce space.
For the readers of this document, there is no better time to implement a strategic deployment of SQL Server 2000 than now.
Books
Inside Microsoft SQL ServerT 2000 by Kalen Delaney. Copyright 2000, Microsoft Press. ISBN: 0-7356-0998-5
Sams Teach Yourself Microsoft SQL Server 2000 in 21 Days by Richard Waymire and Rick Sawtell. Copyright 2000, Sams Publishing. ISBN 0-672-31969-1.
Web Sites
https://www.microsoft.com/sql -SQL Server home page
https://support.microsoft.com/support/sql -SQL Server support home pages
https://technet.microsoft.com -TechNet resource site
https://msdn.microsoft.com -MSDN resource site
https://www.microsoft.com/security - Microsoft's central site for security information
White Papers
https://msdn.microsoft.com/sqlserver -
https://www.microsoft.com/sql/index.htm#W - Contains a complete list of all white papers available on the SQL Server Web site.
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.
The best way of storing the username and password in an application is to store the information in a registry key. The key should be encrypted, and only the application should have the key to decrypt it.
This is largely the same action as securing a file on the NTFS file system 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.
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 (Transact Structured Query Language) refers to the type of SQL implemented in SQL Server 7.0 and SQL Server 2000.
In SQL Server version 6.5 and earlier, this information was stored in the syslogins system table. syslogins can still be queried, as it is a view over the sysxlogins table for backward compatibility. This view should not be necessary because system tables should not be accessed directly. System tables can change at any time.
For a discussion about the same problem with login names, see 'Renaming Windows User or Group Accounts' in the document.
In fact, this approach was also used for views, defaults, rules, triggers, check constraints and default constraint
|
Politica de confidentialitate | Termeni si conditii de utilizare |
Vizualizari: 4017
Importanta: 
Termeni si conditii de utilizare | Contact
© SCRIGROUP 2024 . All rights reserved
