Implementation of Server Level Security and Object Level Security

  • Article

Updated : May 16, 2003

On This Page

Use of Security Identifiers (SIDs)
Generation of GUIDs for Non-Trusted Users
Renaming Windows User or Group Accounts
Views of the sysxlogins System Table
Implementation of Object Level Security
Secure Server Installation
Secure Server Administration

Use of Security Identifiers (SIDs)

SQL Server 2000 checks whether the security identifier (SID) or group membership SIDs of the user 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 access rights of the user are then checked for any objects the user is attempting to access. If access has not been granted for a particular set of logon credentials, the connection to the server is terminated.

When a Windows user or group is either granted or denied access to SQL Server 2000, this information is stored in the sysxlogins system table (discussed later). SQL Server 2000 identifies users connecting through a trusted connection by their SID and group membership SIDs.

Generation of GUIDs for Non-Trusted Users

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.

Renaming Windows User or Group Accounts

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 fully qualified name of the user or group 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 many name lookups are done or the domain controller is connected over a slow WAN link.

The fact that the names of SQL Server 2000 users and groups may differ from those of Windows users and groups 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 login name and SID of the user, respectively, they first query 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.

Views of the sysxlogins System Table

The sysxlogins system table contains information about logins of users. This system table, which exists only in the master database, should be accessed only through these views:

  • Syslogins — provides information on SQL Server logins, and interprets the status column so that it can be understood more readily.

  • sysremotelogins — contains one row for each remote user allowed to call remote stored procedures on SQL Server.

  • sysoledbusers — contains one row for each user and password mapping for the specified linked server.

Implementation of Object Level Security

How Permissions Are Checked

SQL Server 2000 uses SIDs to identify Windows users and groups. However, due to the length of SIDs (up to 85 bytes), SQL Server 2000 maps the SIDs to SQL Server user IDs inside each database. The SIDs are mapped to user IDs in the sysusers table. The SQL Server 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 Windows user connects to SQL Server 2000, the server creates a Process Status Structure (PSS) in memory, comprising the SID of the user, 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 Windows user establishing multiple sessions with SQL Server 2000 will have multiple PSS structures.

When a user attempts to access a database, SQL Server checks the sysusers table to determine whether the user has been denied access directly or by virtue of being a member of a group that has been denied access. If the user is denied access, this is enforced. Otherwise, the sysusers table is checked again, and all user IDs for which the user qualifies are collected. If a 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. 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 SQL Server 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 for performance reasons.

Cost of Changing 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 kept current. This is implemented by versioning.

When permissions are first checked, a version number is assigned. When the permissions on an object are changed, SQL Server 2000 increments the version counter. Whenever an object is accessed, the value of this permission counter is checked. If it differs from the cached value, the content of the cache is discarded, and the object permissions are re-established.

Renaming Windows User or Group Accounts

With SQL Server 2000, you can 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. It is important to understand the reasons for this.

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. Her 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, her 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. This is why SQL Server 2000 does not automatically rename user accounts when the Windows account in the Windows User Directory is renamed.

WITH GRANT Option

The WITH GRANT option is optional syntax that can be used with the GRANT statement. This option applies only to object permissions, and allows the recipient of the GRANT statement to pass on 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.

sysusers System Table

In some ways, the sysusers table is to the database what the sysxlogins table is to the instance. 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 of the sysusers table is used in a similar way to the hasaccess column in the sysxlogins table. The value in this column will be set to zero if the corresponding user has not been granted rights to access the database but creates objects, is explicitly granted permissions on database objects, or is added explicitly to a role.

Objects created by a user are normally owned by that user, and are not owned by the group through which the user was granted access to the database. This direct ownership eliminates ambiguity when a user who creates an object is a member of more than one group with the necessary permissions. There is an exception to this rule. An object can be directly owned by a role or Windows group if the user who creates it explicitly assigns ownership to the role or group when she creates the object. For example:

CREATE TABLE [builtin\administrators].test_table(acolumn VARCHAR(2))

In this situation, an entry for the user must exist in the sysusers table so that the object can have the appropriate owner. The entry is created automatically, but the user 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.

sysmembers System Table

The sysmembers system table is used to record the membership of users in database roles. It is one of the smaller tables, containing only two columns, and one row for each member of a database role.

SQL Server 2000 places 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.

syspermissions System Table

The syspermissions system table, which exists in every database, is used to track permissions that have been granted or denied to users.

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 columns 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.

Named Pipes and Multiprotocol Permissions

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. After Windows has authenticated the client (in the same way as it would for access to any other resource), the Named Pipes session is established over the IPC$ share. This takes place before any attempt is made to pass the connection to SQL Server.

All users who will be connecting to SQL Server 2000 through the Named Pipes Net-Library must have a Windows account and 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 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 multiprotocol network library cannot be used to connect to named instances of SQL Server 2000.

Secure Server Installation

Physical and logical isolation make up the foundation of SQL Server security. Machines hosting a database should be in a physically protected location, ideally a locked machine room with monitored flood detection and fire detection/suppression systems. Databases should be installed in the secure zone of your corporate intranet and never directly connected to the Internet. Back up all data regularly and store copies in a secure off-site location. For guidance on backup procedures and other operational best practices, see SQL Server 2000 Operations Guide.

File System

NTFS is the preferred file system for installations of SQL Server. It is more stable and recoverable than FAT file systems, and enables security options such as file and directory access control lists (ACLs) and file encryption (EFS). During installation, SQL Server will set appropriate ACLs on registry keys and files if it detects NTFS. These permissions should not be changed.

With EFS, database files are encrypted under the identity of the account running SQL Server. Only this account can decrypt the files. If you need to change the account that runs SQL Server, you should first decrypt the files under the old account, then re-encrypt them under the new account.

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.

Firewalls

Divide your network into security zones separated by firewalls. Block all traffic, then selectively admit only what is needed. Before installing SQL Server, you should insure that your perimeter firewall filters out packets addressed to TCP port 1433 (monitored by the default instance) and User Datagram Protocol (UDP) port 1434 (monitored by one of the instances on the computer). Additional ports associated with named instances should also be blocked at the perimeter.

If you are installing the server inside a Windows domain, you should configure interior firewalls to permit Windows authentication. Open ports used by Kerberos or NTLM authentication. If your application uses replication, you should consult the "Configuring a Publisher or Distributor to Listen on TCP/IP" topic in SQL Server 2000 Books Online. If your application uses distributed transactions, you may also need to configure your firewall to allow Microsoft Distributed Transaction Coordinator (DTC) traffic to flow between separate DTC instances, and between the DTC and resource managers such as SQL Server. For more information, see 250367 "Configuring Microsoft Distributed Transaction Coordinator (DTC) to Work Through a Firewall".

Disabling NetBIOS and SMB

Servers in the perimeter network should have all unnecessary protocols disabled including NetBIOS and server message block (SMB). Web servers and Domain Name System (DNS) servers do not require NetBIOS or SMB. These protocols should both be disabled to counter the threat of user enumeration.

NetBIOS uses the following ports:

  • UDP/137 (NetBIOS name service)

  • UDP/138 (NetBIOS datagram service)

  • TCP/139 (NetBIOS session service)

SMB uses the following ports:

  • TCP/139

  • TCP/445

On servers accessible from the Internet, you should disable SMB by removing File and Printer Sharing for Microsoft Networks and Client for Microsoft Networks using the Transmission Control Protocol/Internet Protocol (TCP/IP) properties dialog box in your Local Area Connection properties.

To disable SMB

  1. On the Start menu, point to Settings, and then click Network and Dial-up Connections.

  2. Right-click Internet facing connection, and then click Properties.

  3. Select Client for Microsoft Networks, and then click Uninstall.

  4. Follow the uninstall steps.

  5. Select File and Printer Sharing for Microsoft Networks, and then click Uninstall.

  6. Follow the uninstall steps.

To disable NetBIOS over TCP/IP

  1. Right-click My Computer on the desktop, and then click Manage.

  2. Expand System Tools, and then select Device Manager.

  3. Right-click Device Manager, point to View, and then click Show hidden devices.

  4. Expand Non-Plug and Play Drivers.

  5. Right-click NetBios over Tcpip, and then click Disable.

This disables the SMB direct host listener on TCP/445 and UDP 445.

Note: This procedure disables the nbt.sys driver. The WINS tab of the Advanced TCP/IP Settings dialog box contains a Disable NetBIOS over TCP/IP option. Selecting this option only disables the NetBIOS Session Service (which listens on TCP port 139). It does not disable SMB completely. To do so, follow the steps listed above.

Before Running SQL Server Setup

When planning a SQL Server installation, isolate components from each other. This will minimize the chance that if one component is compromised it could be used to further compromise other components in your system. Your goal is to avoid exposing your server to the vulnerabilities of other software, and vice versa. The following constitute best practices:

  • Use a dedicated computer as a database server.

  • Do not install SQL Server on a domain controller.

  • Do not run Microsoft Internet Information Server (IIS) and SQL Server on the same computer.

  • If you must run multiple services on a single computer, run each service under its own Windows account.

  • Install SQL Server program and database files on a non-system volume, separate from the operating system.

What Does SQL Server Install?

When you install SQL Server, a number of Windows services are installed in addition to program and data files, which by default will be located under \Program Files\Microsoft SQL Server. The following table lists the services and folders that may be created, depending on your choices while running Setup.

Note: The following discussion focuses on SQL Server proper. For detailed discussions of Replication, Analysis Services, or Data Transformation Services, see SQL Server 2000 Books Online.

Item

Details

Services

MSSQLSERVER

MSSQLServerADHelper

Microsoft Search

SQLSERVERAGENT

Folders

\program files\Microsoft SQL Server\mssql\binn (program files)

\program files\Microsoft SQL Server\mssql\data (data files including .mdf, .log and .ndf)

\program files\Microsoft SQL Server\80\tools (shared tools/books online)

\program files\Microsoft SQL Server\mssql\logs (error logs)

\program files\Microsoft SQL Server\mssql\backup (backup files)

\program files\Microsoft SQL Server\mssql\jobs (temp job output files)

For named instances, the instance name is used in the file path:

\program files\Microsoft SQL Server\MSSQL$InstanceName\binn

\program files\Microsoft SQL Server\MSSQL$InstanceName\data

Installing SQL Server

During the SQL Server setup, select the custom setup option. This allows you to choose which items to install. You should install only required components on a production database server. The following items should be omitted.

Tool

Purpose

Upgrade tools

Used to upgrade SQL Server version 6.5 databases

Performance counters

Used for performance monitoring and optimization

Books Online

SQL Server documentation

Development tools

Includes headers and library files used by C developers, together with MDAC and XML SDKs and an interface for stored procedure debugging

Code samples

Sample code used for developer education

Whenever possible, you should require Windows Authentication Mode for connections to SQL Server. This will shield your SQL Server installation from most Internet-based attacks by restricting connections to Windows user and domain user accounts. Your server will also benefit from Windows security enforcement mechanisms, such as stronger authentication protocols and mandatory password complexity and expiration. In addition, credentials delegation (the ability to bridge credentials across multiple servers) is only available when using Windows Authentication Mode and Kerberos. On the client side, Windows Authentication Mode eliminates the need to store passwords, which is a major vulnerability in applications that use standard SQL Server logins.

During installation, you will be required to set a password on the sa account. The sa account should always have a strong password, even on servers that are configured to require Windows Authentication. This will ensure that a blank or weak sa password is not exposed in the future if the server is reconfigured for Mixed Mode Authentication.

Do not plan to use the sa account for day-to-day administration. Microsoft recommends that SQL Server administrators be granted access to SQL Server through membership in a Windows group that is a member of the sysadmin fixed server role in SQL Server. This approach has one minor drawback: Windows administrators can add any user account to a Windows group. It follows that if a Windows group is a member of the sysadmin fixed server role, the Windows administrators will be able to grant anyone sysadmin permissions on SQL Server 2000 by adding their user account to that Windows group. If this is a concern, you should add only individual Windows user accounts to the sysadmin fixed server role**.**

Services

SQL Server 2000 and SQL Server Agent run as Windows services. Each service must be associated with a Windows account, from which it derives its security context. SQL Server allows members of the sysadmin fixed server role to access operating system features. These operating system calls are made with the security context of the Windows account that owns the server process. If the server is cracked, these operating system calls may be used to extend the attack to any other resource to which the owning Windows process (the SQL Server service account) has access. For this reason, it is important to grant only necessary privileges to SQL Server services. The following settings are recommended.

SQL Server Engine/MSSQLServer

This is the SQL Server database engine and is the only mandatory service. Run as a Windows domain user account with regular user privileges. Do not run as local system, local administrator, or domain administrator accounts. If there are named instances, they will be named MSSQL$InstanceName.

SQL Server Agent Service/SQLServerAgent

This is a support service that allows you to schedule commands and notify operators when errors occur. Disable if not required in your environment; otherwise run as a Windows domain user account with regular user privileges. Do not run as local system, local administrator, or domain administrator accounts. If there are named instances, they will be named SQLAgent$InstanceName.

Important: SQL Server Agent will need local Windows administrator privileges if one of the following is true:

SQL Server Agent connects to SQL Server using SQL Server Authentication (not recommended).

SQL Server Agent uses a multiserver administration master server (MSX) account that connects using SQL Server Authentication.

SQL Server Agent runs Microsoft ActiveX® script or CmdExec jobs owned by users who are not members of the sysadmin fixed server role.

SQL Server Active Directory Helper/MSSQLServerADHelper

This service supports Active Directory integration, including database instance registration. Disable if not required in your environment; otherwise run as a Windows local system account.

Microsoft Search

This service provides full-text search capabilities. Disable if not required in your environment; otherwise run under the local system account.

Microsoft DTC

This service coordinates the processing of transactions distributed across two or more servers. Disable if not required in your environment.

If you need to change the account associated with a SQL Server service, use SQL Server Enterprise Manager. Enterprise Manager will set appropriate permissions on the files and registry keys used by SQL Server. Never use the Services applet of Microsoft Management Console (in Control Panel) to change these accounts, because this requires manual adjustment of dozens of registry permissions, NTFS file system permissions, and Windows user rights — a process that is prone to error.

For more information, see 283811 "HOW TO: Change the SQL Server Service Account Without Using SQL Enterprise Manager in SQL Server 2000,". Changes to account information will take effect the next time the service is started. If you need to change the account associated with SQL Server and SQL Server Agent, you must apply the change to both services separately using Enterprise Manager.

SQL Server Accounts

Authorization in SQL Server is managed through SQL Server logins, database users, and a variety of roles. Logins grant access to SQL Server but not to individual databases. Logins are associated with database users to provide access to individual databases. Database users are generally placed in fixed or user-defined roles, and permissions are granted to the roles to determine the capabilities of the login within the database.

Use the following recommendations to ensure you apply stringent authorization settings in the database or instance of SQL Server.

Remove the BUILTIN\Administrators Server Login

By default, the BUILTIN\Administrators local Windows group is added to the sysadmin fixed server role, the members of which administer SQL Server. This means that Windows domain or system administrators who are members of BUILTIN\Administrators have unrestricted access to the SQL Server database. Most companies assign different employees to the domain and system administrator roles. If you want do this, remove the BUILTIN\Administrators SQL Server login and create a Windows group for database or system administrators.

To add a new login for database administrators

  1. Start Enterprise Manager.

  2. Expand Microsoft SQL Server, SQL Server Group, and then your SQL Server.

  3. Expand the Security folder, select and right-click Logins, and then click New Login.

  4. In the Name field, enter a custom Windows group that contains only database administrators.

  5. Click the Server Roles tab and select System Administrators.

This adds the new login to the sysadmin server role.

To delete the BUILTIN\Administrators login

  1. Start Enterprise Manager.

  2. Expand Microsoft SQL Server, SQL Server Group, and then your SQL Server.

  3. Expand the Security folder, and select Logins. If BUILTIN\Administrators appears in the list of logins, right-click it, and then click Delete to remove the login.

For more information, see 263712 "How to Impede Windows NT Administrators from Administering a Clustered SQL Server". For more information on re-configuring the SQL service accounts after the installation, see Changing Passwords and User Accounts.

Do Not Enable the SQL Guest User Account

The guest user account allows a login without a user account to access a database. By default, the guest account is not enabled in any of the user databases. Do not change this setting. Enabling the guest user in a database allows authenticated logins that have not been explicitly granted access to the database to enter the database using the guest credentials.

To check the status of the guest account, open the Users folder in the Computer Management tool. The guest account should be displayed with a cross icon. If it is not disabled, open its Properties dialog box and select Account is disabled. You should also remove the guest account from all user-defined databases. Note that you cannot remove guest from the master, tempdb, or distribution databases created during replication.

Do Not Grant Permissions to the Public Role

All databases contain a public database role of which all users, groups and roles are members. Do not grant additional permissions to this role. Granting permissions to public effectively grants them to all users of the database, including application roles, database roles, and individual users. The best practice is to grant specific, required permissions to select users, roles, or groups.

Encrypting Data Files with EFS

If your server is installed on an NTFS file system, you may choose to encrypt some data files for additional security. You should evaluate the performance cost of encryption on a test server, before implementing it on your production server. Usually the performance impact is negligible, because data files are decrypted when the server process starts.

Encrypt only at the file level, not the directory level. While it is often a best practice to encrypt at the directory level when using EFS so that new files added are encrypted, you should encrypt your SQL Server data files at the file level only. This avoids encrypting your log files.

To encrypt a file with EFS

  1. Right-click on the file.

  2. Select Properties.

  3. Click Advanced.

  4. Select Encrypt contents to be secure.

Choosing Static Ports for Named Instances

The default instance of SQL Server listens on TCP port 1433 by default. Named instances, however, are dynamically assigned TCP ports by Windows when the SQL Server service is started. It is possible, however, that multiple restarts could change this association of named instances to dynamically assigned TCP ports.

Both Kerberos and the SQL Server delegation functionality require that the SQL Server service have a service principal name (SPN) registered against it. Part of the SPN is the port number. Assigning static TCP ports to named instances is therefore highly recommended. In addition to enabling these important security features, static TCP ports are easier to manage when configuring internal firewalls.

Note: Never open SQL Server ports on your external firewall.

Use the Server Network Utility to assign a static TCP port to each named instance. You must pick TCP ports that are not used by other applications running on the same computer or cluster. For a list of well-known ports registered for use by various applications, see https://www.iana.org/. It is a good idea to include your network administrator in the planning of port assignments.

To assign a static TCP port to a named instance

  1. Start the SQL Server Network Utility.

  2. Select the General tab.

  3. Select the named instance from the drop down menu.

  4. In the Enabled Protocols dialog box, click TCP/IP.

  5. Click the Properties button.

  6. Enter the desired static port number and click OK.

You must restart the server for these changes to take effect.

Delete or Secure Old Setup Files

SQL Server setup files may contain information that can make you vulnerable to attack, such as:

  • Credentials that are weakly encrypted.

  • Credentials that appear in plain text (no encryption at all).

  • Sensitive configuration settings.

In SQL Server 2000, the following files may be affected: sqlstp.log, sqlsp.log, and setup.iss in the <systemdrive>:\Program Files\Microsoft SQL Server\MSSQL\Install folder for a default installation, and the <systemdrive>:\Program Files\Microsoft SQL Server\ MSSQL$<Instance Name>\Install folder for named instances.

If the current system is an upgrade from SQL Server version 7.0 installations, the following files should be checked as well: setup.iss in the %Windir% folder, and sqlsp.log in the Windows Temp folder.

Microsoft distributes a free utility, Killpwd, which will locate and remove these passwords from your system. To learn more about this free download, see Microsoft Knowledge Base article 263968, "Service Pack Installation May Save Standard Security Password in File".

Secure Server Administration

Security is an ongoing responsibility for database administrators. The points covered below are especially important.

Install All Service Packs and Security Patches

The best way to maintain the security of your server is to install all security patches and service packs as they are released. To subscribe to e-mail notifications of new security patches, sign up for the Microsoft Security Notification Service.

Monitor Your Server with Microsoft Baseline Security Analyzer (MBSA)

MBSA is a tool that scans for common insecure configurations in several Microsoft products, including SQL Server and Microsoft SQL Server 2000 Desktop Engine (MSDE 2000). It can be run locally or over a network. It tests SQL Server installations for problems such as:

  • Too many members of the sysadmin fixed server role.

  • Granting of right to create CmdExec jobs to roles other than sysadmin.

  • Blank or trivial passwords.

  • Weak authentication mode.

  • Excessive rights granted to the Administrators group.

  • Incorrect ACLs on SQL Server data directories.

  • Plain-text sa password in setup files.

  • Excessive rights granted to the guest account.

  • SQL Server running on a system that is also a domain controller.

  • Improper configuration of the Everyone group, providing access to certain registry keys.

  • Improper configuration of SQL Server service accounts.

  • Missing service packs and security updates.

MBSA can be run from the graphical user interface (GUI) or the command line. The GUI version is the best option for most users, since it is easier to use and generates a detailed report in XML format.

To execute the command line version type the following at the command prompt:

cd "c:\program files\microsoft baseline security analyzer"
mbsacli

MBSA reports refer to Microsoft Security Bulletins, which describe potential problems and include links to security patches.

Microsoft distributes MBSA as a free download. For complete documentation and the latest version of MBSA, see Microsoft Baseline Security Analyzer.

Auditing

SQL Server supports several levels of auditing of both events and states. At a minimum, you should log failed connection attempts to SQL Server and review the SQL error log regularly. When possible, save the SQL error logs to a different hard drive than the one on which data files are stored. Configure login auditing using SQL Server Enterprise Manager.

Login Auditing

To enable auditing of failed connections with Enterprise Manager in SQL Server :

  1. Expand a server group.

  2. Right-click a server, and then click Properties.

  3. On the Security tab, under Audit Level, click Failure.

You must stop and restart the server for this setting to take effect.

General Auditing

SQL Server 2000 supports definition of audit events and columns, and analysis of audit logs with a GUI-based tool: SQL Profiler. Events that can be captured and analyzed include the following:

  • End user activity (all SQL commands, Logout/Login, enabling of application roles).

  • Administrator activity (DDL, other than GRANT/REVOKE/DENY and security events, and database or server configuration).

  • 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).

If you want auditing to start whenever SQL Server starts, you should script the audit, wrap it in a stored procedure, and mark that stored procedure for AutoStart.

For more information, see SQL Server 2000 Auditing, and the "Using Audit Logs" topic at SQL Server 2000 Books Online or Using Audit Logs.

C2 Auditing

SQL Server can be configured to perform the level of auditing required for C2 certification under the Trusted Database Interpretation (TDI) of the Trusted Computer System Evaluation Criteria (TCSEC) of the United States National Security Agency. This level of auditing, known as "C2 audit mode," can be enabled using the "c2 audit mode" advanced option of sp_configure stored procedure. C2-level auditing can produce a very large amount of data in a short period; therefore, this option should not be selected unless absolutely required. To preserve the integrity of the C2-level audit trail, SQL Server will shut itself down if its log files exceed available storage when running in this mode. For more information, see Microsoft SQL Server 2000 C2 Evaluation.