10 steps to protect SQL Server 2000 security

Here are 10 things you can do to improve the security of your SQL Server installation:

Install the latest Service pack

One of the most effective ways to improve server security is to upgrade to SQL Server Service Pack 4 (SP4). In addition, you should also install all published security updates.

Use the Microsoft Baseline Security Analyzer (MBSA) to evaluate server security.

MBSA is a tool that scans for unsafe configurations of a variety of Microsoft products, including SQL Server and Microsoft SQL Server Desktop Engine (MSDE 2000). It can be run locally, or it can run over the network. The tool detects SQL Server installations for the following issues:

Too many members of the sysadmin fixed server role.

Grant the right to create cmdexec jobs for roles other than sysadmin.

An empty or simple password.

Fragile authentication mode.

Give the Administrators group too many rights.

Incorrect access control table (ACL) in the SQL Server data directory.

Use plain text for the sa password in the installation file.

Grant the Guest account too many rights.

Run SQL Server on a system that is also a domain controller.

Incorrect configuration of the Everyone group, providing access to specific registry keys.

Incorrect configuration of the SQL Server service account.

The necessary service packs and security updates are not installed.

Using Windows authentication Mode

Whenever possible, you should require Windows authentication mode for connections to SQL Server. It protects SQL Server from most Internet tools by restricting connections to Microsoft Windowsreg user and domain user accounts. Also, your server will benefit from Windows security enhancements, such as stronger authentication protocols and enforced password complexity and expiration times. In addition, credential delegation (the ability to bridge credentials across multiple servers) can also be used only in Windows authentication mode. On the client side, Windows authentication mode no longer requires storing passwords. Storing passwords is one of the major vulnerabilities of applications that log on using standard SQL Server.

To install the Windows authentication mode in SQL Server Enterprise Manager, use the following procedure:

Expand the server group.

Right-click on the server and click Properties.

In authentication for the Security tab, click Windows only.

For more information, see "Authentication Mode" in SQL Server Books Online or MSDN.

Isolate your server and regularly back up

Physical and logical isolation form the basis of SQL Server security. The machine hosting the database should be in a physically protected place, preferably a locked room, equipped with flood detection and fire detection/fire systems. The database should be installed in the security zone of the enterprise intranet and not directly to the Internet. Back up all data on a regular basis and save the copy at a secure site field point.

Assign a robust sa password

The SA account should always have a strong password, even on servers that are configured to require Windows authentication. This will ensure that when the server is reconfigured for mixed mode authentication, there will be no blank or vulnerable SAS.

To assign the SA password, use the following procedure:

Expand the server group, and then expand the server.

Expand Security, and then click Login.

In the details pane, right-click the SA, and then click Properties.

In the Password box, enter a new password.

Restricting permissions for SQL Server services

SQL Server 2000 and SQL Server agents run as Windows services. Each service must be associated with a Windows account and derive a security context from this account. SQL Server allows users (and sometimes other users) that are logged on by the SA to access operating system features. These operating system calls are created by the security context of the account that owns the server process. If the server is compromised, these operating system calls may be exploited to attack other resources, as long as the owning process (the SQL Server service account) can

Access to it. Therefore, it is important to grant the SQL Server service only the necessary permissions.

We recommend that you use the following settings:

SQL Server Engine/mssqlserver

If you have the specified instance, they should be named Mssql$instancename. Runs as a Windows domain user account with general user rights. Do not run as a local system, local administrator, or domain administrator account.

SQL Server Agent service/sqlserveragent

If you do not need it in your environment, disable the service, otherwise run as a Windows domain user account with general user rights. Do not run as a local system, local administrator, or domain administrator account.

Important: If one of the following conditions is true, the SQL Server Agent will require local Windows Administrator permissions:

SQL Server Agent uses standard SQL Server authentication to connect to SQL Server (not recommended).

The SQL Server Agent uses the multiple server Management master server (MSX) account, which is connected using standard SQL server authentication.

The SQL Server Agent runs a Microsoft activexreg script or CmdExec job owned by a member of a non-sysadmin fixed server role.

If you need to change the account associated with the SQL Serve r service, use SQL Server Enterprise Manager. Enterprise Manager Sets the appropriate permissions for the files and registry keys that SQL Server uses. Do not use the services (in Control Panel) of the Microsoft management console to change these accounts, because you need to manually modulate a large number of registry keys and NTFS file system permissions and micorsoft Windows user permissions.

Changes to the account information will take effect the next time the service starts. If you need to change the accounts associated with SQL Server and the SQL Server Agent, you must make changes to two services using Enterprise Manager.

Disabling the SQL Server port on the fire wall

The default installation of SQL Server monitors TCP port 1433 and UDP port 1434. Configure your firewall to filter out packets that reach these ports. Also, you should block other ports associated with the specified instance on the firewall.

Use the most secure file system

NTFS is the best file system for installing SQL Server. It is more stable and easier to recover than a FAT file system. It also includes security options such as file and directory ACLs and file encryption (EFS). During installation, if the Ntfs,sql Server is detected, the appropriate ACLs are set on the registry keys and files. You should not change these permissions.

With EFS, the database files are encrypted under the identity of the account that is running SQL Server. Only this account can decrypt these files. If you need to change the account that is running SQL Server, you must first decrypt the files under the old account and then encrypt them again under the new account.

Remove or protect old installation files

The SQL Server installation file may contain credentials that are either plain text or simple encryption, and other sensitive configuration information that is recorded during the installation process. The location where these log files are saved depends on the version of SQL Server that is installed. In SQL Server 2000, the following files may be affected: The default installation: Program FilesMicrosoft SQL Servermssqlinstall folder, and the specified instance: program FilesMicrosoft Sqlstp.log, Sqlsp.log, and Setup.iss in the SQL Server Mssql$install folder

If the current system was upgraded from a SQL Server 7.0 installation, you should also check the following files: Setup.iss in the%Windir% folder and Sqlsp.log in the Windows Temp folder.

Audit connections to SQL Server

SQL Server can log event information for system Administrator review. At a minimum, you should log failed SQL Server connection attempts and review the log periodically. If possible, do not save these logs and data files on the same hard drive.

To audit failed connections in SQL Server Enterprise Manager, use the following procedure:

Expand the server group.

Right-click on the server and click Properties.

In the auditing level of the Security tab, the click Failed.

In order for this setting to take effect, you must stop and restart the server.