10 steps to protect the security of SQL Server 2000

This section describes ten things you can do to improve the security of SQL Server installation:

Install the latest service package

To improve Server security, the most effective method is to upgrade to SQL Server 2000 Service Pack 4 (SP4 ). In addition, you should install all released security updates.

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

MBSA is a tool that scans insecure configurations of multiple Microsoft products, including SQL Server and Microsoft SQL Server 2000 Desktop Engine (MSDE 2000 ). It can run locally or through the network. This tool detects the installation of SQL Server for the following issues:

Too many sysadmin fixed server role members.

Authorize a role other than sysadmin to create a CmdExec job.

Empty or simple password.

Fragile Authentication mode.

Grant too many permissions to the Administrator group.

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

Use the plain text sa password in the installation file.

Grant excessive permissions to the guest account.

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

The owner (Everyone) group is incorrectly configured to provide access to a specific registry key.

The SQL Server service account is incorrectly configured.

No necessary service packages and security updates are installed.

Use Windows Authentication Mode

Whenever possible, you should require the Windows Authentication Mode for the connection to the SQL Server. It protects SQL Server against most Internet tools by limiting the connection between Microsoft Windowsreg; users and domain user accounts ,. In addition, your server will also benefit from Windows security enhancement mechanisms, such as stronger authentication protocols and mandatory password complexity and expiration time. In addition, credential delegation (the ability to bridge creden between multiple servers) can only be used in Windows Authentication mode. On the client, password is no longer required for Windows Authentication mode. Password Storage is one of the major vulnerabilities in applications that use standard SQL Server to log on.

To install Windows Authentication Mode in Enterprise Manager of SQL Server, follow these steps:

Expand a server group.

Right-click the server and click Properties.

On the Security tab, click authentication only for Windows.

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

Isolate your server and regularly back up

Physical and logical isolation forms the foundation of SQL Server Security. The machine hosting the database should be physically protected, preferably a locked data center equipped with a flood detection and fire detection/Fire Fighting System. The database should be installed in the security area of the enterprise intranet. Do not directly connect to the Internet. Regularly back up all data and store copies outside the secure site.

Assign a strong sa Password

The sa account should always have a strong password, even on Servers configured to require Windows authentication. This will ensure that no blank or fragile sa will appear when the server is reconfigured as a hybrid authentication.

To assign a sa password, follow these steps:

Expand the server group and then expand the server.

Expand security and click log on.

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

In the Password box, enter a new password.

Restrict SQL Server Service Permissions

SQL Server 2000 and SQL Server Agent run as Windows Services. Each service must be associated with a Windows Account and the security context is derived from this account. SQL Server allows users (and sometimes other users) that log on to the sa to access the 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 broken, these operating system calls may be exploited to attack other resources, as long as the process (SQL Server service account) can

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

We recommend that you use the following settings:

SQL Server Engine/MSSQLServer

If you have specified instances, they should be named MSSQL $ InstanceName. Run as a Windows domain user account with general user permissions. Do not run as a local system, local administrator, or domain administrator account.

SQL Server Agent Service/SQLServerAgent

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

Note: if one of the following conditions is true, the SQL Server Agent requires the local Windows administrator privilege:

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

The SQL Server Agent uses multiple servers to manage the master Server (MSX) account, which uses standard SQL Server Authentication for connection.

The SQL Server Agent runs Microsoft ActiveXreg; scripts or CmdExec jobs owned by non-sysadmin fixed Server role members.

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 used by SQL Server. Do not use the "service" on the Microsoft Console (in the Control Panel) to change these accounts, as this requires manual modulation of a large number of registry keys and NTFS file system permissions and Micorsoft Windows user permissions.

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 use Enterprise Manager to change the two services respectively.

Disable the SQL Server port on the firewall

By default, SQL Server monitors TCP port 1433 and UDP port 1434. Configure your firewall to filter out the packets that reach these ports. In addition, the firewall should also block other ports associated with the specified instance.

Use the safest File System

NTFS is the most suitable File System for SQL Server installation. It is more stable and easier to restore than the FAT file system. It also includes some security options, such as file and directory ACLs and file encryption (EFS ). If NTFS is detected during installation, SQL Server sets an appropriate ACL on the registry key and file. You should not change these permissions.

Through EFS, database files are encrypted under the Account identity that runs SQL Server. Only this account can decrypt these files. If you need to change the account for running SQL Server, you must first decrypt these files under the old account and then re-encrypt them under the new account.

Delete or protect old installation files

The SQL Server Installation File may contain plain text or simple encryption creden。 and other sensitive configuration information recorded during installation. The storage location of these log files depends on the installed SQL Server version. In SQL Server 2000, the following files may be affected: during default installation: In the Program FilesMicrosoft SQL ServerMSSQLInstall folder, and the specified instance: sqlstp in the Program FilesMicrosoft SQL Server MSSQL $ Install Folder. log, sqlsp. log and setup. iss

If the current system is upgraded from SQL Server 7.0, check the following files: setup. iss in the % Windir % folder and sqlsp. log in the Windows Temp folder.

Review the connection to SQL Server

SQL Server can record event information for System Administrator review. At least you should record failed SQL Server connection attempts and regularly view this log. If possible, do not save these logs and data files on the same hard disk.

To Audit Failed Connections in Enterprise Manager of SQL Server, follow these steps:

Expand a server group.

Right-click the server and click Properties.

On the Security tab, click audit level.

To make this setting take effect, you must stop and restart the server.