Top 10 tips for protecting SQL Server databases

The increasing growth of SQL Server databases has attracted a kind of attention. At the same time, those images are still coming. In order to protect the SQL Server database, to protect it from malicious damage or data loss, the top ten tricks will solve these concerns for you. 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 for scanning insecure settings 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.

8) grant excessive permissions to the guest account.

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

10) the owner (Everyone) group is incorrectly set to provide access to a specific registry key.

11) the SQL Server service account is incorrectly set.

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

Microsoft provides free download of MBSA.

3. Use Windows System Authentication Mode

In all possible cases, you should require the Windows system authentication mode for the connection to the SQL Server. by limiting connections to Microsoft Windows system users and domain user accounts, SQL Server is protected from most Internet tools. In addition, your Server will also benefit from the Windows system 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 side, you do not need to store passwords in Windows Authentication mode. password Storage is one of the main vulnerabilities in applications that use standard SQL Server to log on. to install the 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 system only.

4. Isolate your server and regularly back up

Physical and logical isolation forms the foundation of SQL Server Security. the database hosting machine should be physically protected, preferably a locked data center equipped with a flood 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 that require Windows system authentication. this will ensure that there will be no blank or fragile sa when the server is reset to 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 system services. each service must be associated with a Windows system account and the security context is derived from this account. SQL Server allows sa users (and sometimes other users) to access operating system features. these operating system calls are created by the security context of the account with 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 a specified instance, they should be named MSSQL $ InstanceName. run as a Windows system 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

Disable this service if you do not need it in your environment; otherwise, run it as a Windows system 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 administrator privilege of the local Windows System:

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

If you need to change the account associated with the SQL Server service, use SQL Server Enterprise Manager. enterprise Manager sets appropriate permissions for the files and registry keys used by SQL Server. do not use the "service" (in the control panel) on the Microsoft console to change these accounts, this requires a lot of registry keys and NTFS file system permissions and Micorsoft windows system user permissions.

Changes to account information will take effect the next time the service starts. 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 separately.

7. Disable the SQL Server port on the firewall

The default installation of SQL Server monitors TCP port 1433 and UDP port 1434. set 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 the 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 settings 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 directory, and the specified instance: sqlstp in the Program FilesMicrosoft SQL Server MSSQL $ Install directory. 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 % directory and sqlsp. log in the Temp directory of the Windows system.

Microsoft released a free utility, Killpwd, which will be found and deleted from your system.

10. Review the connection to SQL Server

SQL Server records 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) on the Security tab, click fail.

4) to make the setting take effect, you must stop and restart the server.

The ten tricks described above are very practical. By doing so, you can become a qualified manager to protect the SQL Server database and ensure the security of the SQL Server database.