SQL Server 2008 Security Best Practices

2012/06/01

If you are working as a DBA for a financial organisation where sensitive financial data is stored in your databases then security is usually your immediate concern. Securing SQL Server is not a easy task and you must follow a series of best practices in order to accomplish this task. In this blog post I’m listing these SQL Server 2008 security best practices that must be followed in order to secure your SQL Server installation.

Following best practices assume the SQL Server will be installed in a production environment requiring some security:

Physically secure the server on which SQL Server lives.
Make sure all SQL Server data files and system files are installed on an NTFS partition and that the appropriate permissions are defined for the files.
Use a low-privilege user account for the SQL Server service. Don’t use LocalSystem or Administrator.
Delete setup files. Setup files may contain plain text and weakly encrypted credentials. They contain sensitive configuration information that has been logged during installation. These files include sqlstp.log, sqlsp.log, and setup.iss in the MSSQL\Install (or MSSQL$;\Install). Microsoft provides a free utility called killpwd that locates and removes these passwords from your system.
Secure the ‘sa’ account with a strong password.

Remove all sample users and sample databases.
Rename and disable ‘sa’ account because potential attackers are aware of ‘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 = [ReadOnlyUser];

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. You can execute select @@version to see precisely which version you are running.
Manage strong password policy for the SA account and change the password periodically. This can be done by ensuring Password Policy and Password Expiration is option are checked for ‘sa’ account.
Revoke sysadmin access for BUILTIN\Administrators group.

 USE [master]

GO

IF EXISTS (SELECT * FROM [sys].[server_principals]

WHERE [name] = N’BUILTIN\Administrators’)

DROP LOGIN [BUILTIN\Administrators]

GO

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.
Do not manage SQL Server using ‘sa’ account.
Review all passwords. At the very least, check for null passwords using the following SQL:

SELECT [name], [password] FROM [master]..[syslogins]

WHERE [password] IS NULL

Remove the guest user from all databases except from master, msdb, tempdb.
Review how roles are assigned to users at a database and server level and limit assignment to the minimal set necessary.
Put a process in place that allows you to periodically review role and group membership.
Use Windows Authentication rather than Mixed Mode Authentication.
Remove network libraries that are not used (or that you don’t know are used). SQL Server can be accessed using several network libraries. Most environments are based on TCP/IP, in which case all other network libraries should be removed.
Do not use common TCP\IP ports such 1433 and 1434.
Require all access to the database server to be networked. Don’t allow or promote remote access to the operating system and running tools locally.
Remove or restrict access to extended (xp__ stored procedures).
Reduce Surface Area Configuration.
Restrict access to OLE Automation and xp_cmdshell procedures. If possible use SQL CLR procedures to perform these tasks.
Restrictions can be to administrator accounts only or in some cases even more restrictive.
Do not install user-created extended procedures because they run with full security rights on the server.
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.
Check and limit procedures that are available to PUBLIC. To check which procedures may be a problem, you can use the following SQL code:

SELECT * FROM [name]

FROM [master]..[sysobjects] o

JOIN [master]..[sysprotects] p

WHERE p.[uid] = 0 and p.[xtype] IN ('X','P')

AND o.[id] = p.[id]

Disable SQL mail capabilities and find alternative solutions to notification methods.
Do not install full-text search unless your application requires it.
Disable Microsoft Distributed Transaction Coordinator unless distributed transactions are really required for your application.
Check for startup Trojans. Make sure there are no weird calls in master..sp_helpstartup.
Check for password-related Trojans by comparing [master]..[sp_password] to that of a fresh install.
Closely monitor all failed login attempts. Put together the procedure and process for giving you constant access to this information.
Audit that developers cannot access production instances.
Encrypt the entire database using Transparent Database Encryption (TDE) in SQL Server 2008 (Enterprise Edition).
BitLocker may be used to encrypt an entire volume, but Transparent Data Encryption (TDE) is probably a better choice.
Enable DDL and sysadmin activity auditing.
Only grant CONNECT permissions on endpoints to logins that need to use them.