Wednesday, April 25, 2012

SQL Server Security Best Practices

Summary of Best Practices

  • SQL Server should be hardened after the installation.
  • After the installation, use the SQL Server Configuration Manager tool in order to disable unnecessary features and services.
  • Install only required components.
  • Recent service packs and critical fixes should be installed for SQL Server and the Windows.
  • Windows Authentication mode is more secure rather than SQL Authentication.
  • If there is still a need to use SQL Authentication – enforce strong password policy.
  • Disabled the SA account and rename it. Do not use this account for SQL server management.
  • Change default SQL Server ports associated with the SQL Server installation to put off hackers from port scanning the server.
  • Hide SQL Server instances or disable the SQL Server Browser service.
  • Remove BUILDIN\Administrators group from the SQL Server Logins.
  • Enable logging SQL Server login attempts (failed & successful).

SQL Server Surface Area Configuration tool

SQL Server 2005 contains Configuration tools such as system stored procedure called sp_configure or SQL Server Surface Area Configuration tool (for services and features) in order to enable/disable optional features as needed. Those features are usually installed as disabled by default. Here is the list of the features that can be enabled using the tool:
  • xp_cmdshell
  • SQL Server Web Assistant
  • CLR Integration
  • Ad hoc remote queries (the OPENROWSET and OPENDATASOURCE functions)
  • OLE Automation system procedures
  • System procedures for Database Mail and SQL Mail
  • Remote use of a dedicated administrator connection

The CLI version of the tool - sac.exe can be found at %Program Files%\Microsoft SQL Server\90\Shared. Using this tools it is possible to import and export settings between several servers on the network. You must have sysadmin privilege in order to use this tool.
Following is an example of exporting all settings from the default instance of SQL Server on server A and importing them to server B:

sac out serverA.out -S serverA -U admin -I MSSQLSERVER
sac in serverA.out -S serverB
The following code snippet describes how to remove SQL Server components by using shell commands. At the command prompt, run the following command:
%ProgramFiles%\Microsoft SQL Server\90\Setup Bootstrap\ARPWrapper.exe /Remove
Uninstall the SQL Server components one at a time until desired SQL Server components are uninstalled
Stored Procedure – sp_configure
Sp_configure is a system stored procedure which can be used instead of the Surface Area Configuration Tool in order to enable/disable the features in SQL Server. Following is an example of using the sp_configure in order to disable the xp_cmdshell command which enables to run shell commands on the server. The xp_cmdshell is turned off by default in SQL Server 2005 and on.
EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'xp_cmdshell', 0

Configuring SQL Server Authentication Modes

To select or change the server authentication mode, follow these steps:
  1. In SQL Server Management, right-click on a desired SQL Server and then click Properties.
  2. On the Security page, select the desired server authentication mode under Server Authentication and then click OK.
  3. In the SQL Server Management Studio dialog box, click OK to acknowledge the need to restart SQL Server.
  4. In Object Explorer, right-click on a desired server and then click Restart. If the SQL Server Agent is running, it requires a restart also.

Using Windows authentication is a more secure choice. However, if mixed mode authentication is required, you must make sure to leverage complex passwords and the SQL Server 2005/2008 password and lockout policies to further bolster security.
Here is an example of password policy for Sql accounts:
  • The password must contain uppercase & lowercase letters.
  • The password must contain numbers & alphanumeric characters.
  • The password must contain non-alphanumeric characters such as &, ^,%,*,$ etc.
  • Do not use common known passwords that are easy to guess such as: admin, password, sa, administrator, sysadmin etc.
  • Passwords should be long enough (at least 8 characters long).
  • SQL Server 2005 and on does not allows blank password for the SA account. If you are using earlier version of SQL, set a password for sql accounts and also for the SA account according to according to password policy.

Note: If Windows Authentication mode is selected during installation, the SA login is disabled by default. If the authentication mode is switched to SQL Server mixed mode after the installation, the SA account is still disabled and must be manually enabled. It is a best practice to reset the password when the mode is switched.

Additional Instructions

  • Use Windows Authentication mode when it is possible.
  • Use Mixed Mode Authentication only for legacy applications and non-Windows users.
  • When using Mixed Mode Authentication – Potential attackers are aware of the SA user and this takes hacking one step easier if they will take control on this powerful account. Thus, in this mode the SA account must be renamed to a different account as follows:
    ALTER LOGIN sa WITH NAME = [WinnerUser];
    Note: Before renaming the SA account, verify that another account with administrative privileges exists in order to have access to the SQL Server.
  • Manage strong password policy for the SA account and change the password periodically.
  • Do not manage SQL Server by using the sa login account; assign sysadmin privilege to a knows user or group.


Administrative Privileges

Privileges with elevated permissions in SQL Server include:
  • SQL Server SA build-in login (relevant if it is enabled).
  • Members of the sysadmin (BUILDIN\administrators) SQL Server role.
  • Logins with CONTROL SERVER permission.


  • Administrator privileges should be used only when they are really needed.
  • Keep minimum administrators as it can be.
  • Allocate different administrative accounts if there is more than one administrator.
  • Provision admin principals explicitly.
  • Avoid dependency on the "BUILTIN\Administrators" Windows group.

Here is an Transact-SQL (TSQL) syntax for removing the BUILTIN\Administrators Windows Group from a SQL Server instance in case of this group existence from previous versions of SQL Server or using BETA code. This code should be executed on each SQL Server instance installed in the organization:
IF EXISTS (SELECT * FROM sys.server_principals
WHERE name = N’BUILTIN\Administrators’)
DROP LOGIN [BUILTIN\Administrators]

Disabling certain system stored procedures

SQL Server comes with various system stored procedures such as xp_cmdshell orsp_send_dbmail that interact with operating system or execute code outside of a normal SQL Server permissions and may constitute a security risks. Thus such stored procedures should be specially treated.
In SQL Server 2005 it is possible to enable/disable these features in the Surface Area Configuration tool – SAC - (as was mentioned before) which can be found at Start -> All Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> Surface Area Configuration tool. Following features should stay disabled (which are disabled by default):

  • Disable xp_cmdshell unless it is absolutely needed.
  • Disable COM components once all COM components have been converted to SQLCLR.
  • Disable both mail procedures (Database Mail and SQL Mail) unless you need to send mail from SQL Server. Prefer Database Mail as soon as you can convert to it.

Additional Instructions

  • Use SQL Server Surface Area Configuration to enforce a standard policy for extended procedure usage.
  • Document each exception to the standard policy.
  • Do not remove the system stored procedures by dropping them.
  • Do not DENY all users/administrators access to the extended procedures.

Notes: Some system stored procedures, such as procedures that use SQLDMO and SQLSMO libraries, cannot be configured by using SQL Server Surface Area Configuration. They must be configured by using sp_configure or The SQL Server Management Studio(SSMS) directly.
In SQL Server 2008, the SAC was replaced by Policy Based Management framework. This could be accessed from the SSMS under the Management option. It allows you to define and enforce policies for configuring and managing SQL Server across the enterprise.

Hardening SQL Server Ports with SQL Configuration Manager

A default installation of SQL Server 2005/ 2008 use TCP port 1433 for client requests and communications. These ports are well known and are common target for hackers. Therefore it is recommended to change default ports associated with the SQL Server installation.

Following are these steps to change the default port using SQL Server Manager
Configuration tools:

  1. Choose Start, All Programs, Microsoft SQL Server 2005/2008, Configuration Tools, SQL Server Configuration Manager.
  2. Expand the SQL Server 2008 Network Configuration node and select Protocols for the SQL Server instance to be configured.
  3. In the right pane, right-click the protocol name TCP/IP and choose Properties.
  4. In the TCP/IP Properties dialog box, select the IP Addresses tab.
  5. There is a corresponding entry for every IP address assigned to the server. Clear the values for both the TCP Dynamic Ports and TCP Port for each IP address except for the IP addresses under IPAll.
  6. In the IPAll section for each instance, enter a new port that you want SQL Server 2008 to listen on.
  7. Click Apply and restart the SQL Server Services.
Hiding a SQL Server Instance from Broadcasting Information

The SQL Server Browser service enumerates SQL Server information on the network. In such way attackers can use SQL Server clients to browse the current infrastructure and retrieve a list of running SQL Server instances. Thus it is strongly recommended to hide SQL instances from being shown in the network as follows:

  1. Choose Start, All Programs, Microsoft SQL Server 2008, Configuration Tools, SQL Server Configuration Manager.
  2. Expand the SQL Server 2008 Network Configuration node and select Protocols for the SQL Server instance to be configured.
  3. Right-click Protocols for [Server\Instance Name] and then choose Properties.
  4. In the Hide Instance box on the Protocols for [Server\Instance Name] Properties page select 'Yes'.
  5. Click OK and restart the services for the change to take effect.

Additional Instructions
  • Allow only network protocols that are needed.
  • CONNECT permission should be granted only on endpoints to logins that need to use them.
  • If there is a need to work with SQL Login, install an SSL certificate from a trusted CA rather than SQL Server's self signed certificates.
  • Avoid the exposure of SQL Server to the public internet/intranet.

Auditing Mechanism in SQL Server
SQL Server security auditing monitors and tracks activity to log files that can be viewed through Windows application logs or SQL Server Management Studio. SQL Server offers the following four security levels with regards to security:

  • None—Disables auditing (no events are logged)
  • Successful Logins Only—Audits all successful login attempts
  • Failed Logins Only—Audits all failed login attempts
  • Both Failed and Successful Logins—Audits all login attempts

The default mode is: Failed Logins Only. Thus, it is recommended to set the auditing mode to be Both Failed and Successful Logins.

Configuring SQL Server Security Logs for Auditing

To configure security login auditing for both failed and successful logins, follow these steps

  1. In SQL Server Management Studio, right-click on a desired SQL Server and then click Properties.
  2. On the Security page under Login Auditing, select the desired auditing criteria option button, such as Both Failed and Successful Logins, and then click OK
  3. Restart the SQL Server Database Engine and SQL Server Agent to make the auditing changes effective.

Additional Instructions
  • Auditing is scenario-specific. Balance the need for auditing with the overhead of generating addition data.
  • Audit successful logins in addition to unsuccessful logins if you store highly sensitive data.
  • Enable C2 auditing or Common Criteria compliance only if required by selecting the appropriate checkbox (Those options should be selected only if there is a need to comply with these security standards)

In order to configure the C2 audit, you have to activate (in Query Analyzer or osql.exe) and run the following (Command example):

EXEC sp_configure 'show advanced option', '1'
EXEC sp_configure 'c2 audit mode','1'

Patching and updates

Security updates and patches are constantly being released by Microsoft. It is advantageous to install these updates made available for SQL Server and the operating system. These patches can be manually downloaded and installed, or they can be automatically applied by using Microsoft Update.
Additional Instructions
  • Always stay as current as possible.
  • Enable automatic updates whenever feasible but test them before applying to production systems.



Post a Comment

© 1998 NINJA20 v2.5, All Rights Reserved. Powered by Blogger

Designed by ScreenWritersArena