Top 10 tricks to protect SQL Server database security

Source: CCID

1. Install the latest service package

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

2. Use 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:

1) Too many sysadmin fixed server role members.

2) grant other roles other than sysadmin the right to create a CmdExec job.

3) Empty or simple password.

4) Fragile Authentication mode.

5) grant too many rights to the Administrator group.

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

7) use the plain text sa password in the installation file.

Grant excessive permissions to the guest account.

9) run SQL Server in a system that is also a domain controller.

10) if the owner (Everyone) group is incorrectly configured, access to the specific registry key is provided.

11) the SQL Server service account is incorrectly configured.

12) do not install necessary service packages and security updates.

Microsoft provides free download of MBSA.

3. Use Windows Authentication Mode

Whenever possible, you should require the Windows Authentication Mode for the connection to the SQL Server. It protects SQL Server from most Internet tools by limiting connections to Microsoft Windows users and domain user accounts, and your Server will also benefit from the Windows security enhancement mechanism, for example, 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:

1) Expand the server group.

2) Right-click the server and click Properties.

3) on the Security tab, click Windows only.

4. Isolate your server and regularly back up

Physical and logical isolation forms the foundation of SQL Server Security. The machines that reside in the database should be physically protected, preferably a locked data center equipped with a flood detection, fire detection, and 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.

5. 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:

1) Expand the server group and then expand the server.

2) expand security, and then click log on.

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

4) In the Password box, enter the new password.

6. 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 processes (SQL Server service account) can be accessed. Therefore, it is very important to grant only necessary permissions to the SQL Server service.

We recommend that you use the following settings:

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

2) 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, and the account uses standard SQL Server Authentication for connection;

The SQL Server Agent runs Microsoft ActiveX scripts or CmdExec jobs owned by non-sysadmin fixed Server roles.

To change the account associated with the SQL Server 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.

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

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

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

Microsoft released a free utility, Killpwd, which will find and delete these passwords from your system.

10. 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:

1) Expand the server group.

2) Right-click the server and click Properties.

3) In the Security tab's audit level, click fail.

4) To make this configuration take effect, you must stop and restart the server.