10 steps to securing SQL Server

Server

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

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

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 configuration of the Everyone group provides access to specific registry keys.
An incorrect configuration of the SQL Server service account.
12 The necessary service packs and security updates are not installed.

Microsoft provides free downloads of MBSA.

3. Use 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 connectivity to Microsoft windows® users and domain user accounts, and your server will benefit from Windows security enhancements, For example, 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:

1) Expand the server group.
2 Right click on the server, and then click Properties.
3 in the authentication of the Security tab, click Windows only.

4. Isolate your server and back it up regularly.

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.

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

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. Restrict the permissions of the SQL Server service.

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 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. Runs as a Windows domain user account with general user rights. Do not run as a local system, local administrator, or domain administrator account.

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

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

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

9. Delete or protect the 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 is <systemdrive>:\program Files\Microsoft The SQL Server\MSSQL\Install folder, and the specified instance <systemdrive>:\program Files\Microsoft Name>\install in SQL server\ mssql$<instance Sqlstp.log folder, Sqlsp.log and Setup.iss

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.

Microsoft has released a free utility Killpwd that 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 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:

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.