Ten tips for protecting your SQL Server database

SQL Server database is growing, attracting a good look, while those who are plotting to protect the SQL Server database, so that it from malicious destruction or loss of data information, ten tricks for you to solve these concerns. 1. Install the latest service pack

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

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

MBSA is a tool for scanning unsafe settings for multiple Microsoft products, including SQL Server and Microsoft SQL Server Desktop Engine (MSDE 2000). He can run it locally, can also be run over the network. The tool detects SQL Server installations for the following issues:

1) Too many members of the sysadmin fixed server role.

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

3 empty or simple password.

4. Fragile authentication mode.

5 give the Administrators group too many rights.

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

7 The installation file uses the SA password in plain text.

8 Grant the Guest account too much rights.

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

10 the incorrect setting of the Everyone group provides access to specific registry keys.

Incorrect settings for the SQL Server service account.

12 The necessary service packs and security updates are not installed.

Microsoft provides free downloads of MBSA.

3. Using Windows System authentication Mode

At all times, you should need the Windows Authentication mode for the connection point to SQL Server. He protects SQL Server from most Internet tools by restricting connections to users and domain user accounts on Microsoft Windows systems, and Your server will also 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 system authentication mode. On the client side, Windows system authentication mode no longer requires storing passwords. Storing passwords is one of the major vulnerabilities of applications that use standard SQL Server logins. To install the Windows System authentication mode in SQL Server Enterprise Manager, follow these steps:

1) Expand the server group.

2 Right click on the server, and then click Properties.

3 in the authentication of the Security tab, click on the Windows only system.

4. Isolate your server and regularly back up

Physical and logical isolation forms the basis for SQL Server security. The machine that hosts the database should be in a place that is physically protected, the best is a locked room, equipped with flood detection and fire detection and fire systems. The database should be installed in the security area of the Intranet Do not connect directly to the Internet. Back up all the data on a regular basis and save the copy at a secure site field point.

5. Assign a robust sa password

The SA account should always have a strong password, even on servers that are set up to require Windows authentication. This ensures that when the server is reset to mixed Mode authentication at a later time, a blank or vulnerable SA is not present.

To assign the SA password, use the following procedure:

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

2) expand Security, and then click Login.

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

4 in the Password box, enter a new password.

6. Restricting permissions for SQL Server services

SQL Server 2000 and SQL Server agents are run as Windows System services. Each service must be associated with a Windows system 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 process (SQL Server service account) has 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:

1) SQL Server Engine/mssqlserver

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

2) SQL Server Agent service/sqlserveragent

Disable the service if it is not necessary in your environment, otherwise run as a Windows system domain user account with general user privileges. 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 system Administrator privileges:

SQL Server Agent connects to SQL Server using standard SQL Server authentication (not recommended);

The SQL Server Agent uses a multiple server Administration master server (MSX) account, which is connected using standard SQL Server authentication;

The SQL Server Agent runs a Microsoft ActiveX 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 Server service, use SQL Server Enterprise manager.enterprise Manager will be SQL The files and registry keys used by the server set the appropriate permissions. Do not use the Microsoft Management Console Services (in Control Panel) to change these accounts because it requires manual modulation of a large number of registry keys and NTFS file system permissions and Micorsoft Windows System user Rights.

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 SQL Server Agent, you must make changes to two services using Enterprise Manager.

7. Disable SQL Server ports on the fire wall

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

8. Use the most secure file system

NTFS is the most appropriate file system for installing SQL Server. He is more stable and easier to recover than the FAT file system. And he includes security options such as file and directory ACLs and file encryption (EFS). If Ntfs,sql is detected during the installation process Server sets the appropriate ACLs on the registry keys and files. You should not change these permissions.

With EFS, the database files are encrypted under 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 under the new account.

9. Delete or protect old installation files

The SQL Server installation file may contain information that is either plain text or simple encrypted and other sensitive settings that are recorded during setup. The location of these log files depends on the version of SQL Server that is installed. In SQL Server 2000, the following files may be affected: the default installation :P rogram filesmicrosoft SQL Servermssqlinstall directory, and the specified instance: program FilesMicrosoft SQL Server mssql$ Sqlstp.log, Sqlsp.log and Setup.iss in the install directory.

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

Microsoft has released a free utility KillPwd, who will find and remove these passwords from your system.

10. Audit connections to SQL Server

SQL Server can log event information for system Administrator review. At least you should record the failed SQL Server connection attempt 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:

1) Expand the server group.

2 Right click on the server, and then click Properties.

3 In the Security tab of the audit level, click Failed.

4 to make this setting effective, you must stop and restart the server.

The 10 tricks described above are very practical, and by doing this 10, you can become a qualified housekeeper to protect your SQL Server database and ensure that your SQL Server database is secured.