SQL Server Security Best Practices

SQL Server Security Best Practices (2005/2008)

Date: 27/02/2011

Introduction

This paper contains administrative and operational tasks that should be taken in account from security perspective when using Mircosoft SQL Server. The article covers operative instructions and example of code snippets needed for DBA's and Server administrators.

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

Reduce Attack Surface

SQL Server comes with several features that the administrators can choose to install during the installation process:

Database Engine
Reporting Services
Integration Services
Analysis Services Engine
Notification Services
Documentation and Samples (Sample databases & codes)

It is a good practice to install only needed features and by doing that reducing the attack surface.

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

GO

RECONFIGURE

GO

EXEC sp_configure 'xp_cmdshell', 0

GO

RECONFIGURE

GO

Additional Instructions

Do not install sample databases and sample codes on SQL servers in the production environment.
If needed, install the samples only in the development and test environments.
Use the Configuration tools such as sp_configure or SQL Server Surface Area Configuration tool to enable only needed features.
When upgrading from SQL Server 2000 to 2005 and on, it is important to review the configuration settings and turn off features such as the xp_cmdshell, because the upgrade process does not change these settings by default.
Turn off unnecessary services by setting them to disabled or manual startup.

Windows and SQL Authentication modes

SQL Server 2005 and 2008 support two modes for validating connections and authenticating access to database resources: “Windows Authentication mode” and “SQL Server and Windows Authentication mode.” Both of these authentication methods provide access to SQL Server and its resources.

Windows Authentication Mode

Windows Authentication mode is the default and recommended authentication mode. It leverages local accounts, Active Directory user accounts, and groups when granting access to SQL Server. In this mode, you, as the database administrator, are given the opportunity to grant domain or local server users access to the database server without creating and managing a separate SQL Server account. Another advantage of using Windows Authentication is management of password policy by the Active Directory or the local security policy.

Mixed Mode - SQL Server & Windows Authentication

SQL authentication mechanism is based on accounts that are managed inside the sql server, including the password policy.

Mixed authentication (SQL Server and Windows Authentication mode) is still required if there is a need to support legacy applications, application requires it or clients coming in from platforms other than Windows and a need for separation of duties exists.

Configuring SQL Server Authentication Modes

To select or change the server authentication mode, follow these steps:

In SQL Server Management, right-click on a desired SQL Server and then click Properties.
On the Security page, select the desired server authentication mode under Server Authentication and then click OK.
In the SQL Server Management Studio dialog box, click OK to acknowledge the need to restart SQL Server.
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:
USE MASTER

ALTER LOGIN sa DISABLE;

GO

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.

Instructions

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:

USE MASTER

IF EXISTS (SELECT * FROM sys.server_principals

WHERE name = N’BUILTIN\Administrators’)

DROP LOGIN [BUILTIN\Administrators]

GO

Defining Database Ownership

SQL Server instance can contain many databases which were created by users who are database owners -DBO (by default) as it can be seen in the following image: User workshop created the workshop database and is a member of db_owner database role.

Also members of server role sysadmin are also database owners, who have the db_owner role in every user database. Members of the db_owner role have approximately the same privileges as the dbo use. For example the SA user is a member of sysadmin server role as it can be seen in the following image:

The SA user is also a member of the db_owner role, as it can be seen in the following image:

Thus several best practices should be implemented regarding these special ownerships:

Minimize the number accounts/users that have the db_owner role for each database.
Have distinct owners for databases; not all databases should be owned by SA or by any other user in sysadmin server role.

Disabling certain system stored procedures

SQL Server comes with various system stored procedures such as xp_cmdshell or sp_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 the network connectivity

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:

Choose Start, All Programs, Microsoft SQL Server 2005/2008, Configuration Tools, SQL Server Configuration Manager.
Expand the SQL Server 2008 Network Configuration node and select Protocols for the SQL Server instance to be configured.
In the right pane, right-click the protocol name TCP/IP and choose Properties.
In the TCP/IP Properties dialog box, select the IP Addresses tab.
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.
In the IPAll section for each instance, enter a new port that you want SQL Server 2008 to listen on.
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:

Choose Start, All Programs, Microsoft SQL Server 2008, Configuration Tools, SQL Server Configuration Manager.
Expand the SQL Server 2008 Network Configuration node and select Protocols for the SQL Server instance to be configured.
Right-click Protocols for [Server\Instance Name] and then choose Properties.
In the Hide Instance box on the Protocols for [Server\Instance Name] Properties page select 'Yes'.
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.

Encryption principals

Sensitive information such as Credit card numbers, SSN, Salaries etc that is stored in the database should be encrypted.

SQL Server 2005/2008 includes encryption capabilities as described below:

Cell Level Encryption – Supported in SQL Server 2005 (Supported also in 2008)

SQL Server 2005 has an encryption hierarchy, as shown below, that needs to be followed in order to properly support the native encryption capabilities.

The top-level resource in the SQL Server encryption hierarchy is theService Master Key, which is encrypted by the Windows Data Protection API and created the first time a newly-created key needs to be encrypted. Second in line is the Database Master Key. This key can be used to create certificates and asymmetric keys. Next come certificates and asymmetric keys. Both can be used to create symmetric keys or encrypt data directly. Last in line are symmetric keys, which can also be used to encrypt data.

Here are the steps for encrypting information on a cell level:

Create a Database Master Key - Before creating certificates (assuming the Database Master Key is used to create the certificate) or other keys can be generated, a Database Master Key must be created:

USE <DatabaseName>
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Password>'

Backup Database Master Keys - Once a Database Master Key is created, you should back it up to a secure location, preferably offsite. To back up a Database Master Key, use the following syntax:

USE <DatabaseName>
BACKUP MASTER KEY TO FILE = '<FolderLocation>'
ENCRYPTION BY PASSWORD = '<Password>'

Create a certificate - For creating symmetric keys for data encryption or to encrypt the data directly

USE <DatabaseName>
CREATE CERTIFICATE certXXXXX WITH SUBJECT='XXXXXX Certificate',
EXPIRY_DATE = '03/09/2018''

Backup the Certificate

USE <DatabaseName>
BACKUP CERTIFICATE certXXXXX TO FILE = '<FileLocation>',
ENCRYPTION BY PASSWORD='<Password>'

Encrypt the data via certificate

INSERT INTO [DatabaseName].[SomeTable]

values( N'data encrypted by certificate ''ThisIsSomething''', EncryptByCert(Cert_ID('certXXXXX'), @cleartext));

TDE – Transparent Data Encryption in SQL Server 2008 (Database Level Encryption)

TDE is a new feature in SQL Server 2008 - it provides real time encryption of data and log files. It is important to mention that this is database level encryption. Data is encrypted before it is written to disk and also data is decrypted when it is read from disk. The "transparent" aspect of TDE is that the encryption is performed by the database engine and SQL Server clients are completely unaware of it. There is absolutely no code that needs to be written to perform the encryption and decryption. There are a couple of steps to be performed to prepare the database for TDE, and then the encryption is turned on at the database level via an ALTER DATBASE command.

With TDE the backup files are also encrypted when using just the standard BACKUP command once encryption is turned on for the database.

Here are the steps that enable TDE in SQL Server 2008:

Create a Master Key - A master key is a symmetric key that is used to create certificates and asymmetric keys. Execute the following script to create a master key

USE master;

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = 'Create^A#LongPassw@rd123';

Note that the password should be a strong one (i.e. use alpha, numeric, upper, lower, and special characters) and you have to backup (use BACKUP MASTER KEY) and store it in a secure location

Create a Certificate – Certificates can be used to create symmetric keys for data encryption or to encrypt the data directly. Execute the following script to create a certificate

USE master;

CREATE CERTIFICATE TDECert

WITH SUBJECT = 'TDE Certificate'

Note that certificates also need to be backed up (use BACKUP CERTIFICATE) and stored in a secure location

Create a Database Encryption Key -A database encryption key is required for TDE. Execute the following script to create a new database and a database encryption key for it:

CREATE DATABASE mssqltips_tde

USE mssqltips_tde;

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE TDECert

In order to work with TDE the encryption key must be encrypted by a certificate (a password will not work) and the certificate must be located in the master database.

Enable TDE - The final step required to implement TDE is to execute the following script:

ALTER DATABASE mssqltips_tde

SET ENCRYPTION ON

SELECT [name], is_encrypted FROM sys.databases

Additional Instructions

Encrypt high-value and sensitive data.
Use symmetric keys to encrypt data, and asymmetric keys or certificates to protect the symmetric keys.
Password-protect keys and remove master key encryption for the most secure configuration.
Always back up the service master key, database master keys, and certificates by using the key-specific DDL statements.
Always back up your database to back up your symmetric and asymmetric keys.

SSL Configuration in SQL Server

Microsoft SQL Server can use Secure Sockets Layer (SSL) to encrypt data that is transmitted across a network between an instance of SQL Server and a client application. Enabling SSL encryption increases the security of data transmitted across networks between instances of SQL Server and applications.

Instructions – Configuring SSL for SQL Server

Install a certificate in the Windows certificate store of the server computer.
Click Start, in the Microsoft SQL Server program group, point to Configuration Tools, and then click SQL Server Configuration Manager.
Expand SQL Server Network Configuration, right-click the protocols for the server you want, and then click Properties.
On the Certificate tab, configure the Database Engine to use the certificate.
On the Flags tab, view or specify the protocol encryption option. The login packet will always be encrypted.
- When the ForceEncryption option for the Database Engine is set to Yes, all client/server communication is encrypted and clients that cannot support encryption are denied access.
- When the ForceEncryption option for the Database Engine is set to No, encryption can be requested by the client application but is not required.
- SQL Server must be restarted after you change the ForceEncryption setting.

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

In SQL Server Management Studio, right-click on a desired SQL Server and then click Properties.
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
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'

RECONFIGURE

EXEC sp_configure 'c2 audit mode','1'

RECONFIGURE

Tools for analyzing best practices of SQL server hardening

SQL Server 2005 /2008 Best Practices Analyzer (BPA) gathers data from Microsoft Windows and SQL Server configuration settings. Best Practices Analyzer uses a predefined list of SQL Server 2005/2008 recommendations and best practices to determine if there are potential security issues in the database environment.

For SQL 2005

http://www.microsoft.com/downloads/en/details.aspx?FamilyId=da0531e4-e94c-4991-82fa-f0e3fbd05e63&displaylang=en

For SQL 2008

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=0fd439d7-4bff-4df7-a52f-9a1be8725591&displaylang=en

Additional Instructions

Run periodically the BPA in order to find security issues.

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.

SQL Server Security Best Practices

Quick DL!