Security Administration

Abstract

This chapter describes the critical security and operational issues that database administrators and Microsoft® SQL Server™ administrators should address, including user and account management issues, password practices, usage of permissions and roles, and management in various settings (such as linked servers and Data Transformation Services [DTS]). In addition, generic issues regarding registry security and physical security are briefly discussed. Throughout this chapter, best practices are examined and explained. After implementing these procedures the DBA should have an environment with good, strong, basic security.

On This Page

Introduction
Basic security issues
Roles, Permissions, and Passwords
Standard Security Design
User and Account Management Issues
Managing a Secured Environment
Summary

Introduction

A database server acts as a central repository for structured data within a company. The database administrator (DBA) is responsible for configuring and managing the data in a secure manner. Application architects and developers strive to develop secure applications that take advantage of the features of Microsoft® SQL Server™.

A server with a weak security foundation will eventually experience a security breach such as data loss, data disclosure, loss of system availability, corruption of data, and so forth. Depending on the information system and the severity of the breach, the results could vary from embarrassment to loss of revenue, or even to loss of life.

The primary goals of security are to ensure:

• Data confidentiality. Only authorized individuals should be able to view data.

• Data integrity. All authorized users should feel confident that the data presented to them is accurate and not improperly modified.

• Data availability. Authorized users should be able to access the data they need, when they need it.

It is essential to be proactive, and focus should be placed on designing a strong SQL Server security architecture, introducing the application into that architecture, implementing it properly, and preparing the operations environment to support it properly.

Security is a very vital component of the success of any organization. Yet many organizations do not have in place a security policy that dictates the security requirements for their networking infrastructure. Is the SQL Server environment secure? How can you be sure?

This chapter discusses some security best practices and addresses some commonly overlooked security holes that both experienced and inexperienced DBAs overlook. There are links to technical security resources at the end of this chapter.

Assumptions

This chapter is written for the DBA or equivalent administering SQL Server 2000 servers in a Microsoft Windows® 2000 Advanced Server environment.

Chapter Sections

This chapter covers the following topics:

• Basic security issues

• Roles, permissions, and passwords

• User and account management issues

• Managing a secured environment

Basic security issues

Basic security issues for SQL Server can be grouped into two categories: Security Policy and Authentication.

Security Policy

A security policy is a set of protocols and procedures for implementing security in an Information Technology (IT) environment. Security should be one of the fundamental components of any IT infrastructure and should be heavily addressed and integrated in the design phase of any project prior to implementation. DBAs, network administrators, developers, and other members of the IT infrastructure should work closely together to ensure a corporate security solution. The protection of data in storage (such as databases, backups, and files) or in transit (such as queries, DTS, and replication) is the primary emphasis of security. International laws, health regulations, and other possible legal restrictions and regulations concerning data storage and movement should be addressed. A security policy is driven by the business requirements and is a work-in-progress document. Risk assessments and risk analyses are used to determine possible and probable breaches in the security policy.

All possible points-of-entry (holes) and types of security attacks should be thoroughly analyzed tested and documented. The default behavior should be to deny or prevent access at all levels and to implement access only on an as needed basis. The Service Level Agreement (SLA) dictates the level of security implemented and service levels for the security policy. Security controls are implemented to protect, detect, and correct "open doors." A recommended practice is to have two versions of the security policy. One version for technically minded IT staff and another version for the remaining personnel in the company. Both versions should document all aspects of the implemented security infrastructure with the latter using non-technical jargon and concepts. This approach ensures that both IT and non-IT employees have a solid understanding of the security policy, the reasons for the security policy, and the importance of the security policy. The security policy should be actively enforced, periodically reviewed, refined, and updated as necessary.

The security policy should contain the following:

• Internal/external policies and legal requirements

• Security topology

• Security controls

• Security protocols

• Security processes

• Escalation procedures

• Time frames

The entire IT staff should be thoroughly trained in all aspects of the security policy. Security checkpoints should be reinforced to prevent unauthorized access and security vulnerabilities. Data should be protected to enforce confidentiality, integrity, accuracy, and availability.

Security issues should be recorded and logged as they occur. Alerts and notifications should be integrated for prompt resolution. Issues should be thoroughly investigated, documented, and resolved in the most thorough manner possible. Prompt recovery is a must, but not at the expense of the full issue resolution process itself. DBAs need the necessary authorization and control to resolve known security issues as they occur.

Note: Security is vitally important to the success of any corporation. However, as security measures increase, performance falls. Measures should be taken to enforce the appropriate level of security without compromising performance. Security redundancy should be eliminated.

Authentication

SQL Server 2000 operates in one of two authentication modes: Windows Authentication Mode (the default) or Mixed Mode (with both Windows authentication, and SQL Server authentication). The type of client connections determine the choice of security mode. If all of the incoming clients support trusted connections, use Windows Authentication Mode. If some clients do not support trusted connections, use Mixed Mode.

Windows Authentication offers advanced security features over SQL Server Authentication. These features are a facet of the Windows 2000 Security Policies. Features include: password expirations, password attributes, auditing, and account lockouts, and mutual authentication using Kerberos. To ensure a more secure environment, the DBA or the network administrator should set user passwords to expire after a certain duration of time, passwords should be required to be a minimum number of characters, prior passwords should not be allowed to be re-used until, for example, 12 new passwords have been used, and account lockouts should take place after a specified number of unsuccessful logon attempts.

Note: Changes to domain user accounts and passwords for the SQL Server services can lead to availability issues. Processes should be implemented to address these issues prior to implementation.

An additional benefit of Windows Authentication is that users do not need to re-enter their password when connecting to an instance of SQL Server. A token is created for the user when the user logs in to a Windows 2000 domain. The token contains a user security ID (SID) and related group SIDs. The user SID is a unique identifier that identifies the user is on the network. The group SIDs indicate the Windows groups to which the network user belongs. When the user logs on to SQL Server 2000 using Windows Authentication, the token is requested, and then passed to SQL Server using the Security Support Provider Interface (SSPI). All SIDs that are returned are checked against the sysxlogins table in the master database. The first check is for a DENY. If a Windows user or a Windows group to which the user belongs has been denied access to SQL Server, no access is permitted. If no DENY is present, the process then proceeds to check whether or not a valid and matching SID exists for the user's Windows account. If a match is found, the user is granted access. If a match is not found, the process proceeds to search sysxlogins for a SID that matches any of the Windows groups to which the user belongs. If a match is found in the sysxlogins table, the user is granted access; if not, access is denied.

Windows Authentication has several disadvantages, however. If no domain controller is available for any reason, the user cannot log on to the domain and the user will not be able to connect to SQL Server. If the user's group memberships change after successfully logging on to Windows 2000 and SQL Server, those changes take effect only after the user logs off the network and then logs on again. From the standpoint of making changes to groups, or group permissions at the domain level, the DBA will need to have been assigned the necessary administration permissions to make these changes, or the DBA will need to request that someone with those administrative permissions make the changes.

Note: In either authentication mode, if anyone changes the password for any SQL Server service's domain user account, that change is not propagated to SQL Server and that password must be changed manually through the Services snap-in and in SQL Server 2000 Enterprise Manager.

If the DBA needs to access SQL Server but cannot connect because of an issue with the SQL Server services' domain user account or validation, the DBA can stop the server using Enterprise Manager and change the startup account to the Local System account, which will allow the DBA to access the data in SQL Server. However, production accessibility and any access to other servers (for example, for replication purposes), e-mail and access to network shares will temporarily be unavailable until the account is changed back to its original domain user account.

Note: This is not a good practice unless there is absolutely no other way to get into the server. If there is no other option, register the planned interruption of service.

Despite these shortcomings, Windows Authentication Mode is strongly recommended as the default authentication mode for SQL Server 2000. Be cautious when changing authentication modes because currently working applications may fail if SQL Server login accounts are no longer accepted. Development and testing environments can be set up to emulate and reveal potential application and server availability issues.

Roles, Permissions, and Passwords

An important aspect of SQL Server security that is too often overlooked, are roles, permissions, and passwords

Roles

SQL Server 2000 uses four types of roles: fixed server roles, fixed database roles, user-defined roles, and application roles. Each type of role has a specific functionality, which provides DBAs with a very flexible approach to managing security.

Fixed Server Roles

Fixed server roles allow the DBA to assign certain groupings of administrative permissions. Instead of giving a user full system administrator functionality, fixed server roles allow the DBA to assign only the server-level permissions that each user requires.

For example, if the DBA wanted to give a particular user the ability to create linked servers without adding them to the sysadmin fixed server role, the DBA could just add the user's login account to the setupadmin fixed server role.

For the DBAs in the environment, create a Windows 2000 group for the DBAs, then add the login account for this group to the sysadmin fixed server role. This approach ensures the highest level of security while allowing the DBAs to better track the members and their actions.

Table 3.1 identifies and describes the abilities of the various SQL Server 2000 fixed server roles.

Table 3.1 SQL Server Fixed Server Roles

Fixed Server Role	Description
sysadmin	Can perform any activity in SQL Server.
serveradmin	Can set server-wide configuration options, shut down the server.
setupadmin	Can manage linked servers and startup procedures
securityadmin	Can manage login accounts and CREATE DATABASE permissions, also read error logs and change passwords.
processadmin	Can manage processes running in SQL Server.
dbcreator	Can create, alter, and drop databases.
diskadmin	Can manage disk files.
bulkadmin	Can execute BULK INSERT statements.

Caution: Be very selective when adding users to these roles. For example, sysadmin maps out to dbo in every database and is the equivalent of logging in using the sa account. Bulkadmin allows users to insert the contents of any local file into a table. Also, anyone who is added to a fixed server role automatically inherits the ability to add other users to that role as well.

Fixed Database Roles

Fixed database roles allow the DBA and/or the database owner to assign certain groupings of database administrative permissions. Instead of giving a user full database owner functionality, fixed database roles allow the DBA to assign only the database-level permissions to be granted to the user.

For example, if the DBA wants to give a particular user the ability to create objects in the database without adding them to the db_owner fixed database role, the DBA could just add their database user account to the db_ddladmin fixed database role.

Table 3.2 identifies and describes the abilities of the various SQL Server 2000 fixed database roles.

Table 3.2 SQL Server Fixed Database Roles

Fixed Database Role	Description
db_owner	Has all permissions in the database.
db_accessadmin	Can add or remove user IDs.
db_securityadmin	Can manage all permissions, object ownerships, roles and role memberships.
db_ddladmin	Can issue ALL DDL, but cannot issue GRANT, REVOKE, or DENY statements.
db_backupoperator	Can issue DBCC, CHECKPOINT, and BACKUP statements.
db_datareader	Can select all data from any user table in the database.
db_datawriter	Can modify any data in any user table in the database.
db_denydatareader	Cannot select any data from any user table in the database.
db_denydatawriter	Cannot modify any data in any user table in the database.

Caution: Be very selective which users are added to these roles. For example, db_owner maps out to dbo in the database and is the equivalent of being the database owner. Also, anyone who is added to a fixed database role automatically inherits the ability to add other users to that role as well.

User-defined Database Roles

User-defined database roles allow the DBA and/or the database owner to more easily manage groupings of permissions.

For example, if the DBA wants to grant several users the same object permission(s), the DBA could either grant that permission(s) to each user individually or, preferably, add the users to a user-defined role within the database, and then grant the permission(s) once to the role.

User-defined roles can be nested, but this feature should be used sparingly to prevent security conflicts and to prevent troubleshooting problems.

Application Roles

Application roles are different from other roles because they do not contain users and required a password to be activated. Application roles are database-specific and after activated, users cannot access objects in other databases. The exception is when a guest user account has been created in the other database, the guest account or the public role has been granted access to the object and user qualifies the object using the syntax database.owner.object [the Transact-SQL (T-SQL) USE DATABASE statement is not supported].

Permissions

When working with permissions, there are three areas you must consider: the types of permissions, granting/revoking/denying permissions, and effective permissions.

Types of Permissions

There are several types of permissions in SQL Server 2000 including object permissions, statement permissions, predefined permissions, and implicit or derived permissions. With the exception of system-stored procedures, permissions cannot be granted across multiple databases. A user account must be created in every database that a login account will access.

Object permissions are granted to a user on an existing database object, such as a table, view, stored procedure, or function. These permissions include SELECT, INSERT, UPDATE, DELETE, REFERENCES, and EXECUTE.

Statement permissions are granted to a user to enable the user to create objects in the database. These permissions include CREATE DATABASE, CREATE DEFAULT, CREATE FUNCTION, CREATE INDEX, CREATE PROCEDURE, CREATE RULE, CREATE SCHEMA, CREATE TABLE, CREATE TRIGGER, CREATE VIEW, BACKUP DATABASE, and BACKUP LOG.

Predefined permissions occur with membership in a fixed server role or a fixed database role. These permissions are a part of the role definition and cannot be altered.

Implicit or derived permissions occur when the user is the owner of the object. For example, a table owner can create a trigger, grant permissions, or alter a table that the user owns.

Permissions should be granted with T-SQL scripts and it should be done in such a way that auditing can be performed. For example, a DBA receives a request from a department head to enable a new employee to access the department's database for a specific application. All operations from the creation of the login account through the granting of permissions should be documented in audit tables that records who added which login account or user account as well as when, where, and why each login was created. The T-SQL scripts for all operations should be maintained in a secure location. Standardizing this and all other administrative operations is key. Consider building a Web-based front-end application to make these and other changes. Use triggers and audit tables to record the details of the operations.

Granting/Revoking/Denying Permissions

Permissions can be granted/revoked/denied using either Enterprise Manager or the Grant/Revoke/Deny T-SQL commands. As has already been mentioned, permissions should be granted to roles using scripts whenever possible rather than directly to the individual database user accounts.

Permissions information is stored in the syspermissions table in each database. DBAs can more easily track additional permission information by querying the dynamically created sysprotects pseudo-table. A GRANT permission shows a positive entry in the protecttype column of sysprotects of either 204 for a grant_w_grant (allows granted users to grant the granted permission; requires the use of the grant T-SQL command) or a 205 for a standard grant. A DENY permission shows a negative entry of 206. A REVOKE permission either removes a granted or denies permission and no entry appears in sysprotects. This means that the user or group that had the permission revoked will no longer have any access. The exception again would be granted permissions through other role memberships.

Permissions are cumulative so that a database user's effective permissions are the most permissive of those that have been granted to the database user account or to any roles within the database that the user belongs. This does not include application roles because these roles do not contain any users or roles.

A denied permission at a user or role level supercedes all other granted permissions. For example, John is a member of the Sales_Role in the Sales database. John has been granted permissions to select from the tblTeleSales table. The Sales_Role has also been granted permission to select from the same table. If John's individual permissions are revoked, John will still have the permission as a result of his role membership. On the other hand, if only the Sales_Role has its permission revoked, John will still have his own access to tbl_TeleSales. All other users of the role will lose permissions as granted through the Sales_Role. Users of this role can still select from tbl_TeleSales as long as they have been explicitly granted the permission or belong to another group that has that permission and they have not been denied access at any level.

If John's permissions are denied, however, John will not have access, regardless of the permissions for roles to which he belongs. Users in Sales_Role will still have access. If the Sales_Role has its permission denied, John and any members of the role will no longer have access regardless of any other role permissions that have been granted select on tbl_TeleSales.

Effective Permissions

Permissions that are granted to users and roles and are database specific. All permissions are cumulative with the exception of a DENY. A denied permission at either a user level or at a role level overrides the same permission granted via other role memberships with the exception of the sysadmin fixed server role. (A sysadmin retains all permissions, even if a role they are a member of has a DENY permission.) If a DENY is applied to a role, all members of that role are denied. In general, a REVOKE of a granted permission should be used instead of a DENY to remove permissions. A REVOKE removes either a GRANT or a DENY. A DENY can cause permissions problems that can be very difficult to track. Be especially careful in applying a DENY to a single user. By using REVOKE and roles, administration of permissions and documentation can be simplified.

Passwords

When working with passwords, there are two areas to consider, application role passwords and resetting passwords.

Application Role Passwords

Application roles allow SQL Server administrators to restrict access to a database for a particular application. Users who activate the application role lose their own current permissions for the session and obtain the permissions granted to the application role. The application role is activated using sp_setapprole. When activating an application role, the password can be secured in several ways. The password can be placed securely in the registry allowing only the application to retrieve it. Alternatively, the password can be encrypted at the time the application role is activated using either the OLE-DB (Object Linking and Embedding-DataBase) or ODBC (Open Data Base Connectivity) encrypt function. The password can also be encrypted as part of the communication process between the server and the client using either SSL/TLS (Secure Sockets Layer/ Transport Layer Security) or protocol encryption.

Resetting Passwords

Passwords can be changed in one of two ways depending on the type of login account. For Windows user accounts, the password can be changed by any administrator with the appropriate reset password right, or by the network user. No change needs to take place in SQL Server 2000 to reflect this change because the user is validated by Windows 2000. For SQL Server login accounts, a user's password can be changed by members of the sysadmin and securityadmin fixed server roles or by the individual user by executing sp_password. Members of sysadmin, db_owner, and db_securityadmin roles can change application role passwords that are stored in the database's sysusers system table.

The domain user password(s) for the SQL Server service(s) should be set to not expire. In the event the domain user account (not the password) associated with a SQL Service account is changed, the account should be changed in the Services snap–in, and then it should also be changed in Enterprise Manager to ensure the appropriate rights are assigned to that account, and to synchronize the account with the Full Text service.

Logging Changes

For information on logging and tracking changes, see Chapter 6, "Change and Configuration Management."

Standard Security Design

A standard security design should be implemented across all servers. This design should employ certain security attributes. This section presents several recommendations for basic security.

Sa Account

The sa account in a production environment should be given a complex password, made up of uppercase and lowercase letters, symbols, spaces, and numbers. The sa account should have a complex password, even if the SQL Server is running in only Windows Authentication Mode. A complex password protects SQL Server from someone easily getting administrative access to SQL Server. This also protects the server in the event that an administrator changes the security authentication mode to Mixed Mode.

Do not use the sa login account in a production environment. Instead, place each DBA's network user account into a Windows group, create a single SQL Server login account for the group then add the login account to the sysadmin fixed server role. This way DBA administration can be better audited and documented.

Passwords

SQL Server login accounts for mixed environments should not be given a blank (NULL) password. Nor should login accounts be assigned a password the same as their login name, or a standard word such as "password." DBAs should follow this rule and convey it to end-users as well. Instead, provide a unique password for each login account or user, and convey the information to the user via encrypted e-mail or a voice mail account, which is accessible to only that user and has the user's name clearly identified in the recording.

If a policy is implemented that requires updates to all passwords on a monthly basis, consider the cost of this policy before proceeding with the plan. For example, in running clusters, a failover will need to be initiated to change the service password on both nodes. Thus it will be virtually impossible to meet an availability of 99.999 percent with such a policy in place. If users are required to change their passwords on a monthly basis, they are more likely to forget their passwords and call in to have them reset. Make sure you will be able to pay the cost of supporting the security level you choose.

Creating Objects

Only qualified DBAs should have permissions to create physical database objects on any production server. Developers should have rights to create stored procedures, user-defined functions, and perhaps views. All other objects are the responsibility of the DBA.

The database owner (dbo) should own all user-defined database objects. Here are several ways in which the dbo is seen as the object owner. When objects are:

• Created by members of the sysadmin fixed server role.

• Created by those who log in using sa (not recommended).

• Created by the database owner (for example, by anyone who has been granted CREATE DATABASE permission).

• Created by members of db_creator fixed server role.

• Created by members of db_owner or db_ddladmin when the object is prefixed with dbo as the owner (for example, CREATE TABLE 'dbo.usertable).

• Created by a non-dbo and ownership is changed via sp_changeobjectowner (this is a reactive measure and can break scripts and stored procedures).

Ensure that dbo is the owner to prevent broken ownership chains. Broken ownership chains result when an object is created that depends on another object owned by a different user. For example, Todd creates a table, Mary creates a view on Todd's table, and Mary grants permissions to John to select from her view. SQL Server will check permissions for every break in ownership. Permissions will be checked for John on both Mary's view and Todd's table. If John does not have select permissions to Todd's table, John will not be able to access Mary's view. This behavior prevents users from accessing another user's object without explicit permissions. The exception would be if John were a member of a role that was granted permission on Todd's table.

Permissions

Permissions can be granted on a selected number of columns in a table. Row level security can be implemented only through a view, stored procedure, or function. Because managing column level permissions can become complex, it is recommended that column and row level security be managed by creating views or alternately stored procedures or functions. These objects will specify the columns and rows to be accessed. Permissions should be granted on the object rather than the underlying table.

If the company is developing custom-built software, prevent users from having any form of direct access to any table. Instead, create views for every table and grant permissions only to those views through application roles.

Documentation

Document each security measure on each SQL Server, including the time it was made. Include the following in the documentation:

• The authentication mode

• Members of the local Administrators group

• Members of the sysadmin and other fixed server roles

• Members of the db_owner and other fixed database roles

• Members of any user-defined roles

• The permissions granted for each object

• To be thorough, the DBA should document all changes in security, from server level changes to object level changes, as well as statement permissions as they occur.

Other

Windows 2000 and SQL Server 2000 service packs, patches, and hot fixes should be researched and applied on a regular basis.

Windows Authentication should be the chosen method for all clients who support trusted connections. For accounts that do not support trusted connections, a SQL Server login account and password should be created by the DBA.

DBAs should use extreme caution in configuring SQL XML support in Internet Information Server (IIS). Windows authentication should be used for the virtual directory that is created. Do not use sa if using a SQL Server account.

All means of access into SQL Server 2000 and to the data should be carefully explored and examined. Other means include Enterprise Manager, Microsoft Access, Query Analyzer, osql, third-party tools, and custom applications.

Applications should not return sensitive information to clients. Informational messages and errors should be replaced with more generic responses to prevent clients from viewing server names, IP addresses, code, and other secure items.

Although the master database is a convenient place to create stored procedures used for administration, a better plan is to create a standard SQLAdmin database on each server and store the objects there.

Extended Stored Procedures

The master database contains many extended stored procedures that enhance the functionality of SQL Server 2000. These procedures are actually registered dynamic link libraries (DLL) that expose functions that can be easily accessed using T-SQL statements. For example, registry entries can be created or read using xp_regwrite/xp_regread, new OLE Automation objects can be created using sp_OACreate, and e-mail can be sent using sp_sendmail. The list of extended stored procedures in the master database should be checked to ensure that no user including the public role has execute permissions unless a valid justification can be established. Extreme care should be taken prior to introducing any new extended stored procedures into a production environment. Thorough testing should be initiated in a testing/development environment to prevent the possibility of memory leaks and server accessibility issues in a production environment.

Warning: Be extremely stringent in granting execute permissions to users on extended stored procedures.

The extended stored procedure xp_cmdshell allows users to run operating system command prompt commands using T-SQL. Default permissions are granted for only the sysadmin fixed server role. Only DBAs should be granted sysadmin access. It is very important for DBAs to understand the security implications of xp_cmdshell. DBAs are able to run xp_cmdshell under the security context of the domain user account for the MSSQLSERVER service account. If the same domain user account is used for multiple SQL Server service accounts and has administrative rights and permissions, the DBA is able to use xp_cmdshell in an administrative capacity on those servers as well. To mitigate this risk, consider using different domain user accounts for the SQL Server service accounts for the various SQL Servers in the environment. Application specific SQL Servers can be grouped together using the same service account to allow DBAs to sufficiently support their application(s) without the ability to escalate their permissions for SQL Servers outside the application(s) they support.

Note: Restrict access to xp_cmdshell to the sysadmin fixed server role only. Seriously consider further network security to files and servers by restricting the network rights and permissions of the domain user account for the SQL Server service(s).

Stored procedure, views, and function definitions that are deemed sensitive and proprietary should be encrypted. Confidentiality should be maintained using the ENCRYPTION argument of the CREATE statements. Others will be unable to view the text of these objects within syscomments using graphical tools or sp_helptext.

Command line utilities that ship with SQL Server 2000 should always be coded to use trusted connections; for bcp, use –T, and for osql, use –E.

Script Security

Drives and directories containing administrative scripts must be protected from viewing and editing by anyone outside of the DBA group. A best practice for this is to use a source control tool such as Microsoft Visual SourceSafe® version control software in conjunction with NTFS to ensure that nothing is changed without authorization. You should never use hard-coded passwords in scripts when Mixed mode security is used, or if accessing, ftp sites, for example. If using the sa account, the password should not be explicitly referenced in any script, job, DTS package connection object, and so forth. Not only is this a security risk, but it also makes it impossible to ever change the password without a major change operation.

Other Issues

The DBA should change the default database for all users to a database other than master. Do not allow users to create objects in master or msdb because these two system databases contain vital information and stored procedures that SQL Server uses to continue to perform.

Note: Any accounts or permissions set in the model database will be the template for all databases created on that server.

Consider dropping the pubs and northwind sample databases from the production servers.

The public role in each database represents the default permissions for all users and roles in the database. The public role is in every database and cannot be removed, nor can users or roles be added or removed from this role. Therefore, be stringent in granting permissions directly to the public role because all users and roles of the database inherit the permissions. Also, any permissions granted to public are applied to any application roles when the roles are activated. Do not deny permissions to the public role.

The guest account, a user in each database by default, allows login accounts that do not have corresponding database user accounts the ability to access that database and use objects based on the permissions directly granted to the guest account and to the public role for that database. Therefore, to lock down security, the guest account should be removed from all databases and should not be added to the model database.

Note: The guest account cannot be removed from the master or tempdb system databases.

Two-Tier Security

A two-tier application model offers a single tier for client access and logic, and a single tier for data. Often the client access tier is hosted on IIS. There are some security issues that you must be aware of when it comes to IIS hosted processes directly connecting to your SQL Server. The primary concern is the protection of login account and password data. If your Web page defines a complete connection string in script, it is possible for anyone with access to the Web page source code to see a valid login account/password pair. This should be avoided at all costs. You should perform the following steps to ensure that login account and password data is not accessible in this manner:

• If your Web or client application is connecting to SQL Server through ODBC and requires a data source name (DSN), in the ODBC Administrator, choose Windows NT authentication when you configure the data source. This forces connections using the DSN to connect in Windows Authentication mode. Your SQL Server will need a login mapped to the user's account for them to gain access.

• If your Web or client application uses a Microsoft ActiveX® Data Objects (ADO) connection string, omit the parameters "UID" (user ID) and "PWD" when you use the ODBC provider, and "User ID" and "Password" when you use the SQLOLE DB provider. This will require your users to provide a username and password each time a connection is made. If you do not omit these parameters (if they are hard-coded in your ASP page), anyone accessing the page will be able to access your database.

• If you connect using the OLEDB provider for SQL Server (Provider=SQLOLEDB), the connection string must include "Integrated Security=SSPI."

Three-Tier Security

Three-tier applications are the foundation for a successful e-commerce solution. Applications are separated into three logical services layers: presentation, business, and data. The business services layer represents the middle tier of this foundation. Windows authentication should be implemented in addition to Component Services (COM+) roles for security at this layer. A Component Services role represents a collection of Windows 2000 business users and groups. A best practice is to use Windows 2000 groups within Component Services roles to enforce security rather than individual Windows 2000 users. Security can then be established and assigned to Component Services packages, components, and interfaces to the Component Services roles.

Component Services packages and applications can be created to contain both business and data components. The business components contain the business logic for the organization. The data components control the access of the data on the SQL Server 2000 database. Clients can access the business components by accessing the ASP page and then having the ASP page instantiate the business object. Access to the business components can be controlled by permissions and memberships using Component Services role-based security. SQL Server application roles and stored procedures can then be implemented to work in conjunction with the data components to further the development of a secure e-commerce business solution.

Physical Security

The production servers, hubs, routers, and switches all need to be physically secured behind locked doors and preferably in locked cabinets. You should assume the worst: that unauthorized individuals may have access to the server room. Therefore, never leave the computer unlocked with the administrator logged on.

Software on the production server should be physically secured. SQL Server 2000 data files, executables, and DLLs should all be secured using NTFS permissions. Only the SQL Server service(s) domain user account, the local SYSTEM account, and the local Administrators group (which should include all sysadmin domain users) should have permissions to these files. These permissions should be set to Full Control. This is the default behavior following installation for the C:\Program Files\Microsoft SQL Server\MSSQL folder and subfolders when SQL Server 2000 is installed on a stand-alone or member server. If SQL Server 2000 is installed as an upgrade, or on a domain controller, permissions may be different. The DBA should check permissions to ensure they meet the above recommendations.

For best results and flexibility, the development, test, and staging environments should be on separate domains from each other. For the staging environment, set up a completely isolated lab with minimal access to the production domain for the purpose of transferring backups or scripts. Doing this will allow the creation of an enterprise lab by completely duplicating the production environment in the lab. However, do not duplicate all user domain accounts from production into staging, and do not use any of the same passwords for service accounts (or sa login accounts) in the lab. Failing to observe this rule could result in a security breach based on lab access.

The most secure production environment will have at least one domain login account per SQL Server instance. These accounts will also need e-mail accounts to use alerts, which will be used for manageability. Security and the ease of administration should be evaluated when considering using multiple SQL Server domain user accounts.

Network Security

Network security involves limiting security infiltrations by securing the various networks and networking components. Although strictly not a function of the DBA, knowledge of the implementation by the network administrator coupled with input from the DBA will lead to the most secure environment.

A perimeter network (also known as DMZ, demilitarized zone, and screened subnet) is often used to secure back-end servers such as SQL Server 2000 from external attacks. A perimeter network is comprised of two separate firewalls. Each firewall monitors incoming and outgoing network traffic. Implementation usually includes IP packet filtering and rules for IP ports, protocols, and addresses. In addition, application-level proxies, stateful filters, and host-based protection can be implemented to further increase security measures. On one side of the perimeter network is the external network or the Internet. On the other side of the perimeter network lies the corporate network. Different levels of security and various security controls are implemented inside and outside of the perimeter network depending on the security policy that is used. Many implementations will place Web clusters and SQL Servers inside the perimeter network. Other more secure implementations will locate the SQL Servers within the internal corporate network itself.

Network security can be implemented through the use of various hardware components [such as storage area networks (SAN), segmented networks, firewalls, proxies, and switches] and various software components [such as group/account/local security policies, Kerberos (which includes mutual authentication and impersonation/delegation), Public Key Infrastructure (PKI) using digital certificates via a Secure Sockets Layer (SSL) channel, Internet Protocol Security (IPSec), protocol security and Virtual Private Networks (VPN)]. Use care to not duplicate security measures because too much security can cause problems with availability, usability, and performance.

For example, protocol encryption for all server network libraries using SSL can be established with the Super Socket Net-Library. The use of this feature requires a certificate from a valid Certification Authority (CA). Protocol encryption can be enabled for the server by selecting Force protocol encryption on the General tab of the Server Network Utility.

Protocol encryption can be enabled for the client by selecting Force protocol encryption on the General tab of the Client Network Library Utility. You can find more information about protocol encryption using certificates in SQL Server Books Online. (See Figure 3.1.)

Figure 3.1: Forcing Protocol Encryption

Securing the network can prevent external disruptions such as Denial of Service (DoS) attacks (network saturation), external hacking (illegal access and stealing), user impersonation and data interception. Other means within SQL Server 2000 can be used to prevent internal vendettas aimed at malicious corruption and destruction of data (such as by a disgruntled employee).

Registry Security

The following registry keys should be secured using NTFS permissions:

HKEY_LOCAL_MACHINE \SOFTWARE \MICROSOFT \MSSQLSERVER \MSSQLServer
HKEY_LOCAL_MACHINE \SYSTEM \CurrentControlSet \Services \MSSQLSERVER

Permissions should be set so that only the local Administrators group, the domain user account(s) that the SQL Server services are running under, and the local SYSTEM account have Full Control to these keys. Named instances will use separate registry keys that specify the instance name. These keys should be secured for all named instances on the server. This is the default behavior for the registry keys following a clean install on a member server. The DBA should check permissions to ensure they meet the above recommendations.

User and Account Management Issues

When operating a SQL Server environment, it is important to understand the following user and account management issues:

• Failover Clustering

• Log Shipping

• Local Administrators, Domain Administrators, and BUILTIN Administrators

• Auditing

• Proxy Account

• Login Accounts and Users Restored to Another SQL Server

Failover Clustering

The administrative login accounts for SQL Server 2000 failover clustering must utilize Windows Authentication. A local service account cannot be used for the SQL Server services in a SQL Server 2000 failover cluster. Although the account used is automatically assigned the proper privileges during the installation process, if the account is changed, it must have the following (or the Administrator group must have them):

• It must be a member of the local Administrators group (Windows NT 4.0, Enterprise Edition only)

• It must be granted the rights of "Act as part of the operating system," "Log on as a service," and "Replace a process level token"

• The service account for the cluster service must have the right to log on to SQL Server. Accepting the default means the account [NT Authority\System] must have logon rights to SQL Server so that the SQL Server resource DLL can run the isAlive query against SQL Server.

If the need arises to change the account that the SQL Server virtual server runs under, this must be done through SQL Server Enterprise Manager. This will change the service password on all of the nodes and grant the necessary permissions to the chosen user account. If SQL Server Enterprise Manager is not used to change passwords, things such as FullText may not function properly, and SQL Server may not be able to start.

Log Shipping

You should use Windows Authentication when you are using log shipping. If Mixed Mode must be used, however, the installation of log shipping creates the user log_shipping_monitor_probe. The Primary and Secondary servers use this account to update the log_shipping_primaries and log_shipping_secondaries tables when a transaction log backup, copy, or restore operation occurs.

Changing the role of a Secondary server to be a Primary server requires that the login accounts that existed on the original Primary server be in place at the Secondary server when it assumes Primary server role responsibilities. The Log Shipping Process does not automatically ship the login accounts (however, it does ship the users in a database). Clients attempting to access the new Primary server will be denied access unless the login accounts have been copied over and their SIDs resolved to the UIDs of the database users.

Local Administrators, Domain Administrators, and BUILTIN Administrators

The SQL Server 2000 BUILTIN\Administrators login account maps to the local Administrators group on the local computer where SQL Server 2000 is installed. The local Administrators group includes the Domain Administrators group. The BUILTIN\Administrators group is by default a member of the sysadmin fixed server role, to which the sa login account maps in SQL Server 2000. Thus, anyone who is a Domain Administrator or a local Administrator will automatically be a member of the sysadmin fixed server role for that SQL Server installation and possibly other installations. The DBA can resolve this by removing the BUILTIN\Administrators account from the sysadmin fixed server role. This practice is recommended by Microsoft.

Note: The BUILTIN\Administrators account can be removed from a server only if a login account on the SQL Server is created for the domain user account that the SQL Sever Agent service is running under. The login account should be added to the sysadmin fixed server role. For clustering scenarios, also create a SQL Server login account for the domain user account that the Microsoft Clustering Service is running under. Also add this login account to the sysadmin fixed server role. If Full-Text is enabled, add a login account for the Local System account using sp_grantlogin [NT Authority\System] and ensure that the login account is a member of the sysadmin fixed server role.

To determine the accounts that are members of a Windows group, the DBA can use the extended stored procedure xp_logininfo:

EXEC xp_logininfo  'DOMAIN/WindowsGroupName', 'members'

To determine the accounts that are members of a SQL Server role, the DBA can use Enterprise Manager or sp_helprolemember.

EXEC sp_helprolemember 'DatabaseRole'

Login Accounts

The following login account and permission structure follows recommendations from Microsoft for creating groups and assigning permissions (see Figure 3.2). The recommendation is to:

1. Add Windows 2000 network users to Windows 2000 groups.

2. Create a single SQL Server 2000 login account for the Windows 2000 group.

3. Create a user account in the database for the SQL Server 2000 login account.

4. Add the user account to a user-defined database role.

5. Grant permissions on views and stored procedures to the role.

Figure 3.2: Creating Groups and Assigning Permissions

Standardizing user and group security using this approach will greatly simplify administration and troubleshooting of database and statement access. As mentioned earlier, however, object permissions should be granted on views and stored procedures rather than the underlying tables whenever possible. (A key point to remember is that permissions are granted at a database level and each database has its own independent permissions structure.) By granting permissions on views, stored procedures, and functions, the DBA adds a layer of abstraction between the user and the underlying base object. If the DBA needs to determine the network name of a user in the Windows group who performed an operation, the DBA can use either the SYSTEM_USER function or the SUSER_SNAME() function. This can be very important when auditing which operator performed which function at what time. An example of this use would be a custom audit table that captures certain modifications on a base table using a trigger that records the operator by using a DEFAULT constraint for the user column of the audit table that contains one of the above functions.

Login account creation for SQL Server is restricted to the members of the fixed server roles sysadmin and securityadmin. Login accounts should be added using a script to the master..sysxlogins table with sp_grantlogin for Windows 2000 groups and users or sp_addlogin for SQL Server login accounts. Scripts allow the DBA to document what took place (who, what, when, why, and how) and to include commented rollback code to remove changes. These scripts should be maintained in a secure location.

To prevent a Windows 2000 login account from accessing SQL Server, the DBA can either revoke the login account using sp_revokelogin or deny the login account using sp_denylogin. If the login account is revoked, the user cannot connect to SQL Server through that login account. if the Windows 2000 user is a member of a Windows 2000 group that has another login account on the SQL Server, the user can still access SQL Server via that group's login account. To prevent all access for a Windows 2000 user, the DBA should DENY the login account with sp_denylogin. To remove a SQL Server login account, drop the login account with sp_droplogin.

There are a few items that the DBA should know about adding and dropping login accounts from a SQL Server. Trying to drop a login account for which there is an associated database user account that owns a database object will fail. Dropping the login account would also drop all associated database user accounts. If this were to happen, orphaned objects could potentially be left in the database for which there would be no associated database owner. To drop login accounts with associated database users that own objects, use one of the following methods:

• Script out the object creation using Enterprise Manager, drop the object, drop the login account, and then recreate the object, using the script just created, but specifying a different database user as the owner.

• Use sp_changeobjectowner to change the ownership of the object. The syntax is:

  sp_changeobjectowner [ @objname = ] ' object ' , [ @newowner = ] ' owner '

Note: Changing the object owner using sp_changeobjectowner can break scripts and stored procedures. Be sure to view all dependencies prior to making ownership changes or dropping the object. Use either Enterprise Manager or sp_depends to see which objects depend on this object and which objects this object depends on. Permissions should also be scripted out on the object so they can be reapplied following the change in ownership. Then view and make any necessary changes to update the scripts and stored procedures associated with this object.

If a Windows 2000 group or user account is deleted using Active Directory, SQL Server will not remove the associated login and user accounts. This prevents orphaned objects. Since the SID is created when the account or group is created, however, creating a new Windows 2000 account with the same name will not reinstate access to SQL Server. The SID of the new group or user account will be different, even though the name may be identical, and while users and administrators use user names, computers use Access Control Lists (ACLs), and ACLs use SIDs.

Auditing

Who has permissions to access SQL Server 2000? For what purpose has the user or group been given access? This information should be known and documented. If necessary, audits can be performed. Auditing can be enabled in several ways. To enable login auditing for both successful and failed login attempts, the DBA can click the appropriate option under Audit level on the Security tab of the SQL Server Properties dialog box.

These audited login attempts will be written to both the Windows 2000 Application Event Log and the SQL Server Event Log. For the change to take effect, the SQL Server service needs to be stopped and restarted. To see the current level of auditing, use Enterprise Manager or execute master..xp_loginconfig. The recommended practice is to monitor all failed login attempts.

Another tool that allows the DBA to gather information about using SQL Server is SQL Profiler. SQL Server storage and relational engine events can be captured using the server side tool SQL Profiler. These events can be either captured to a file or inserted into a table for further analysis. Events captured to a file can be viewed graphically with SQL Profiler. Event captured to a table can be viewed with any tool that can query a SQL Server table. Sorting and grouping the collected data improves analysis. Users need to be a member of the fixed server role sysadmin to create traces.

A class of significant auditing importance is the Security Audit Event Class in SQL Profiler. This class allows the DBA to monitor many security events with relative ease.

For more information, see "Security Auditing Event Category" in the SQL Server 2000 Books Online.

A type of auditing that is of particular importance to governmental agencies and high security organizations is the C2 audit mode. This mode captures all of the events of the Security Audit Event Class and all data columns. The auditing information is captured in the \Data folder. Sp_configure can be used to easily enable this mode of audit. The syntax for enabling this option is:

sp_configure 'c2 audit mode', 1

Note: Caution should be taken when enabling this option, because performance can be reduced, and the storage space required by this file or table can grow very quickly. The SQL Server service will shut down if it cannot write to the C2 audit log for any reason. If this occurs, the C2 audit mode can be disabled by using the –F startup option. Membership of the sysadmin fixed server role is required to enable C2 auditing.

For more information, see "C2 Audit Mode Option" in the SQL Server 2000 Books Online.

When auditing the system for security, limit the number of classes and data columns added to the trace definition. Also, limit the content of the trace by adding filters. To reduce the amount of overhead associated with using the graphical Profiler tool, execute sp_trace_status to run the trace behind-the-scenes. Traces can be set to begin automatically when the SQL Server service starts by encapsulating the trace definition in a user-defined stored procedure and setting that stored procedure to start automatically using sp_procoption.

Note: A very easy way to script out the trace definition is to use the Script Trace feature in Profiler.

Proxy Account

The proxy account is a domain user account specified by the system administrator for users who are not members of the sysadmin fixed server role. It allows those users to run xp_cmdshell and SQL Server Agent job steps that perform CmdExec or Microsoft ActiveX scripting operations under the security context of that domain account. Granting NTFS permissions on the files and folders can control the network permissions. By default only a member of the sysadmin fixed server role can run these operations.

Login Accounts and Users Restored to Another SQL Server

There are various situations when login accounts will need to be transferred to another SQL Server. Moving databases from one server to another is one example. The process of moving or copying databases may be part of a backup/restore, a detach/attach, or a Copy Database Wizard operation. A backup/restore operation may be the used to maintain a standby (warm) server as part a disaster recovery plan. Care should be taken when databases are transferred from one server to another. Mappings between login accounts and database user accounts can become unsynchronized.

Detaching and attaching databases is an easy approach to moving databases from one server to another. The process involves deleting the database entries from the system catalog of the original server using sp_detach_db (or Enterprise Manager), copying the data and log files to the new server and destination directories, and inserting the database entries into the new server's system catalog using sp_attach_db (or Enterprise Manager).

The Copy Database Wizard is a graphical user interface (GUI) based tool that guides DBAs in transferring databases and optionally, login accounts, from one server to another server. All login attempts detected at execution time of the package can be transferred, or only the login accounts used by the databases selected for transfer. A DTS package will be created on the destination server using the Transfer Logins Task followed by the Transfer Database Task.

When using the backup/restore and attach/detach system stored procedures for a user database, login accounts are not transferred over to the destination server. This can present various problems with security and accessibility. To prevent this situation from occurring, script out the login accounts on the source server and run the script on the destination server prior to the restore, or use the DTS Transfer Logins Task, or the Copy Database Wizard (which transfers both the login accounts and the database). Sp_change_users_login and sp_resolve_logins can be used to re-map the SQL Server authenticated login accounts to database user accounts.

Managing a Secured Environment

When managing a secured environment, it is important to ensure linked server security and DTS package security.

Linked Server Security

Your organization may have linked servers for which you will want to establish security. Linked server security can be established through either Enterprise Manager or sp_addlinkedsrvlogin. The most secure way to enforce linked server security is by using the Security Account Delegation feature of Kerberos security in Windows 2000 with Active Directory Services. This feature allows the client to maintain its Windows authenticated credentials as their request for distributed data is passed from server to server.

For more information about security account delegation in Kerberos see "Security Account Delegation" in SQL Server 2000 Books Online.

DTS Package Security

DTS packages saved to SQL Server or to a COM structured storage file can have an owner password and/or a user password. Owner passwords allow users to view and modify the package in the DTS Designer. User passwords allow users to execute the package. For security purposes, assign owner passwords to all packages. This prevents users from viewing or changing the contents of another user's package.

Note: SQL Server 7 requires Service Pack 3 to open and execute SQL Server 2000 password protected DTS packages.

The owner of a package that is scheduled for execution as a job is determined by the security account that the SQL Server is registered under in Enterprise Manager. For servers that are registered with Windows Authentication, the domain user account becomes the owner. For servers that are registered using a SQL Server login account, the SQL Server login account becomes the owner of the job.

Packages created on a workstation are run on the workstation in the security context of the Windows 2000 user who is currently logged on. Packages that are scheduled as part of a job are executed on the server. The security context that the package is run under depends on whether the owner of the job is a member of the sysadmin fixed server role.

Jobs that are owned by a login account that is a member of the sysadmin fixed server role will be run under the security context of the SQL Server Agent account. Jobs that are not will be run under the domain user account for the SQL Server Agent proxy account. Ensure that both of these accounts have the appropriate permissions for the files involved. When referencing network files within the package, always use Universal Naming Convention (UNC) names so that the path will be consistent for all users. To increase performance, copy the external files to the server that will be running the package.

If the package is executed using the command line utility dtsrun, the package runs in the security context of the currently logged on Windows 2000 user. If xp_cmdshell is used to execute dtsrun, then the security context used depends on whether or not the user is a member of the sysadmin fixed server role. If the user is a member, the package runs in the security context of the domain user account that the SQL Server service is running under. If the user is not a member, the package runs under the security context of the proxy domain user account.

To increase security, use Microsoft Data Link (.udl) files and Windows 2000 Authentication when establishing connections within packages. Also, if the package contains any COM objects that are called within ActiveX scripts, be sure to store those COM objects on the server on which the package is run.

Summary

This chapter discussed some of the more important security fundamentals and best practices for a SQL Server 2000 environment. Focus was placed on the development of a solid, dynamic, and effective security policy. This policy will form the foundation of an effective and consistent security structure for the data environment. Hardware security was examined from the network to the individual servers. Software security was examined from the client, to the middle tier, and to the back-end servers. Administration covered the security aspects of the server, login accounts, users, roles, and permissions. This information should enable the SQL Server 2000 DBA to better build, enforce, maintain, and improve the security model for the organization.

More Information

For more information, please see the following technical resources.

Web Sites

Microsoft TechNet Security:

https://www.microsoft.com/technet/security/default.mspx

Microsoft SQL Server Home:

https://www.microsoft.com/sql

NTBugtraq:

https://www.ntbugtraq.com/

Internet Security Systems:

https://www.iss.net/

The SANS Institute:

https://www.sans.org

CERT Coordination Center:

https://www.cert.org/

SQL Server Magazine:

https://www.sqlmag.com/

Microsoft Web Solution Platform:

https://www.microsoft.com/business/default.mspx

MSDN Online:

https://msdn2.microsoft.com/default.aspx

Papers

"Microsoft SQL Server 2000 Security White Paper":

https://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec00.mspx

"Three-Tier Security in an E-Commerce Environment":

https://www.microsoft.com/technet/archive/itsolutions/ecommerce/default.mspx

"SQL Server 2000 C2 Administrator's and User's Security Guide":

https://www.microsoft.com/Downloads/Release.asp?ReleaseID=25503

"A Blueprint for Building Web Sites Using the Microsoft Windows Platform":

https://www.microsoft.com/technet/prodtechnol/acs/reskit/acrkappa.mspx

Books

Delaney, Kalen. Inside Microsoft SQL Server 2000. Microsoft Press. 2000

https://www.microsoft.com/mspress/books/4297.asp

Microsoft Corporation. Microsoft SQL Server 2000 Resource Kit. Microsoft Press. 2001.

Howard, Michael. Designing Secure Web-Based Applications for Microsoft Windows 2000. Microsoft Press, 2000.

https://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=O9t1K4tUOB&isbn=0735609950&itm=1

Products

Microsoft Internet Security & Acceleration Server:

https://www.microsoft.com/ISAServer/

ISS Database Scanner v4.1 Evaluation:

https://www.iss.net/cgi-bin/download/evaluation/evaluation-select.cgi

Note: Third-party products are listed to enhance enterprise solutions. Microsoft does not directly endorse nor support the reliability of these products.