This is a document listing SQL Server security best practices. Some of these best practices may also be a best practice for performance and administration, but others of these best practices may cause performance to degrade and administration costs to escalate. It is left to you to decide which practices to implement based on all factors.

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

Security during Installation and Configuration

• Place the server in a room accessible only to authorized persons.
  • I took this item directly from this Microsoft Knowledge Base Article: Security Considerations for a SQL Server installation.
• Keep the computer running SQL Server off the Internet during installation of SQL Server.
  • This reduces the chance that the computer will become infected prior to the implementation of adequate security features.
• Don't install SQL Server on a domain controller.
  • If the security of the domain controller or of SQL Server is compromised, then it possible the security of the the other will be compromised. Performance may also be adversely impacted.
• Don’t install SQL Server on an IIS server.
  • IIS is already a target or Internet attacks and placing the SQL Server on the same computer increases the risk for the SQL Server.
• Place all SQL Server data files on an NTFS partition, not a FAT partition.
  • It is very difficult now to not use NTFS partitions, but you definitely should use NTFS for SQL data files because NTFS offers better security.
• Disable unnecessary services, software, and protocols on the server.
  • With Windows Server 2003 and later versions of Windows, there is very little, or possibly no services to disable because a new clean install of the OS will have unnecessary features disabled by default.
• Disable NetBios over TCP and SMB (ports 139 and 445).
  • How to disable NetBIOS over TCP/IP.
  • How to disable SMB.
• Only enable the protocols SQL Server will need (usually TCP/IP) for communication with SQL Server.
  • A general security principal is to turn off what is not needed. Use SQL Server Configuration Manager for this.
• Only enable the required SQL Server services
  • Review the list of SQL Server services, and all services, for ones that are not used and can be turned off.
    • SQL Server, SQL Server Agent, SQL Server Browser, SQL Server VSS Writer, and more
    • This article lists this SQL Server services for each version of SQL Server.
• Don't use the SQL Browser service. Turn it off.
  • The SQL Browser service is not required to use SQL Server. You may recall that it was the target of the SQL Slammer worm. Turning off this service eliminates one possible attack vector. This article explains how SQL Browser works. You can choose to hide some SQL Server instances so that SQL Browser does not announce them.
• Use a port other than 1433.
  • It is well known that port 1433 is the common port for SQL Server; but you can configure SQL Server to run on most any port. Running SQL Server on a different port forces hackers to spend a little more time to discover the port and identify what is listening on that port.
• Use SQL Server Management Studio to modify accounts, not Control Panel - Services.
  • When you use SQL Server Management Studio to modify the service accounts, SSMS will also make registry entries that are necessary for a few features to work. Making the changes through Control Panel will not include the related registry changes.
• Configure each SQL Server service (MSSQLServer, SQL Browser, etc.) to use a different user account.
  • This reduces what a hacker can do in the case the account of a specific service is compromised. If the hacker discovers the password to the SQL Browser service, the hacker will not also have immediate knowledge of the password for the SQL Server service.
• Configure the windows account for each service to only have the minimal privileges necessary to function.
  • Microsoft discourages the use of LocalSystem, which allows many privileges to the whole server, and discourages NetworkService. Details about the minimal privileges for each service can be found in this article.
• Configure the windows account for each service to deny logon locally.
  • The account should be used exclusively by the service. As a service account, the password will probably not be changed frequently, thus it is best to make sure the account cannot be used to logon by an individual in case the password is discovered.
• Create account names for the services that are not obvious.
  • Don't name the account for SQL Server "SQLServerServiceAccount" because this makes the account name a more likely target of a hacking attempt.
• Use long and complex passwords for the service accounts.
  • Longer passwords are more difficult to hack with brute force methods. I recommend at least 20 characters.
• Rename the sa account.
  • It is possible to rename the sa account, so do so. While you are at it, rename the Administrator account on the operating system too.
• Make sure the sa account has a complex password.
  • Make sure to change the password occasionally.
• Remove sample code and databases such as Northwind and pubs.
  • This eliminates a possible point of attack for hackers.
• Delete installation logs and scripts.
  • This reduces the risk that a hacker will discover information in these files to assist in an attack.
• Disable xp_cmdshell.
  • xp_cmdshell is disabled by default in SQL2005 and SQL2008. You should keep it disabled, and insure that it is disabled.
• Review permissions and policies when upgrading from earlier versions of SQL Server.
  • When you upgrade, especially from pre-SQL2005 versions, you should review all of your security policies relevant to the database. You can probably do a number of things to improve security now that were not available prior to SQL 2005.
• Review this document from the Microsoft Patterns and Practices team.
  • The article includes many of the items on this document, but with more detail.
• Review this document written by Bob Beauchemin for Microsoft.
  • This word document is titled SQL Server 2005 Security Best Practices - Operational and Administrative Tasks.
• Enable C2 Audit Trace:
  • This article explains the value of the C2 Audit Trace.
• Enable the Common Criteria Compliance server option (2008 Enterprise Edition):
  • Many government, military, and financial organizations require this.
• Enable SQL Server Auditing (2008 Enterprise Edition)
  • For organizations with strict auditing and compliance regulations, SQL Server Auditing can stop all SQL activity when a server is not compliant.
• Disable Cross Database Ownership Chaining:
  • This article explains cross database ownership chains and how they can lead to unexpected security violations.

Security in Application Development

• Learn the Microsoft Security Development LifeCycle.
  • The LifeCycle is a collection of best practices to help developers write code that is secure, not just for SQL Server, but in general.
  • The LifeCycle recommends these tools to assist developers to find possible security problems in their code.
• Minimize the risk of SQL Injection. This article from Microsoft is one of thousands on the Internet with similar recommendations.
  • Use parameterized queries to prevent hackers from running their own SQL commands in your database.
  • Use HTML Encoding to prevent hackers from creating javascript that executes when rendered on your web pages.
• Use SSL to encrypt data in transit.
  • If you are concerned that some of your data could be intercepted on the network, use SSL to encrypt the data. This should always be done with credit card information. SSL is configured in the web server.
• Don't show detailed error messages on web pages.
  • People can use this information to recognize SQL Injection opportunities, identify table and query structures, and possibly use that information to break into your server. Once case of this occurred recently:
    • 9/20/2010: http://weblogs.asp.net/scottgu/archive/2010/09/18/important-asp-net-security-vulnerability.aspx
• Encrypt the entire database using Transparent Database Encryption (TDE) in SQL Server 2008 (Enterprise Edition).
  • This article explains how to implement TDE and its advantages, but this Microsoft article provides information with more depth.
• BitLocker may be used to encrypt an entire volume, but Transparent Data Encryption (TDE) is probably a better choice.
  • BitLocker and TDE use similar encryption technology, but TDE provides better performance.
  Encrypt sensitive data. If you can't use TDE in SQL 2008, consider the encryption options in SQL 2005.
  • Credit card data
  • Passwords
  • PII (Personally Identifiable Information).
• Don't write your own encryption algorithms.
  • SQL Server, Windows and .Net provide strong encryption algorithms you can use.
• Do not embed SQL Server logons and passwords in code.
  • .Net code can be easily viewed with tools like Reflector unless obfuscation is used; but obfuscation is often not an option. Embedded logons are more difficult to change. If you create applications for resale under no circumstance should you embed logons and require clients to implement those logons on SQL Server. Doing so provides the software provider, and other clients that run the same application, knowledge that can be used for unauthorized access to SQL Server.
  • In September 2010 a researcher exposed a problem similar to this in OAuth, specifically in the twitter client for Android.
• Use Windows Authentication for Connection strings.
  • Questions to ask about database connection strings:

Security in Database Design

• Don't store unencrypted passwords in the database
  • Anyone that can see those passwords may discover that the same user will use that same password on more sensitive sites like their bank's web site.
  • Consider using OAuth for simple public web sites where all authenticated users have the same permission.
• Create a layer of abstractions around the database tables by using stored procedures and views.
  • This can allow you to avoid granting insert, update, delete permissions directly on tables.
  • This also makes it easier to change database structures.
• Do not put any stored procedures in the master database or create stored procedures starting with sp_.
• Do not enable .Net CLR in the database, especially if it is not being used.
• Understand ownership chains.
  • When a user can execute a stored procedure that modifies a table, table permissions will not be checked.
• Use views to control column level permissions instead of column level permissions.
  • Most DBAs find views easier to manage
  • Consider placing sensitive columns in separate tables
• Assign permissions to groups, not individuals, even if the group will only have one member.
  • It is generally easier to deduce an individuals permissions when all permissions are derived through groups and it is generally easier to manage and track the permission changes.
• Don't use the guest account, or public group in the databases.
• Don't allow users to create and own their own database objects
  • If a user must own an object, use schemas.
• Create a schema for each application.
  • Don't use the dbo schema.
• Create Proxy accounts for jobs and tasks requiring escalated privileges.
  • Proxy accounts are a safer technique for allowing users to execute commands like xp_cmdshell.
• Use Signed Procedures to grant a permission to a user, but only when performing the action through a specific stored procedure.
  • This article provides an example of using signed procedures.
• Secure the developer databases. Don't allow developers to use the sa account for application connections even during development.
  • The risk is that you won't try to eliminate use of the sa account until it is almost time to go live; and then you will discover something does not work, so you will end up going live using the sa account.

Security During Operations

• Use password protected screen savers.
  • Some compliance rules require this to reduce the risk of unauthorized access to unattended computers.
• Use two accounts for administrators that are applications users.
  • People that are administrators should only use their administrator privileged account when performing actions that require administrative permission.
• Secure the database backups
  • Column level encryption introduced in SQL 2005 does not keep the data in the backups encrypted.
  • Make sure your backups are encrypted if they contain sensitive data.
  • Make sure you have restricted permissions on the folders the backups are placed in.
  • Make sure all backup media is securely locked up.
• Secure data used for testing and development.
  • Production systems provide a good source for test data, but the production data may be sensitive. You should obfuscate this data before using it in test and QA environments. This may be done by replacing names with random characters, or replacing credit card numbers with simulated credit card numbers.
• Secure data in transit to StandBy servers, hot backups, and replicated servers.
  • Whether the mechanism is log shipping or replication, this production data may temporarily live on hard drives external to SQL Server, in network traffic, or in intermediate databases. Make sure that you appropriately secure this data in transit from production to the destination SQL Server.
• Secure Filestream data.
  • FileStream allows you to store documents external to SQL Server on the OS file system. Make sure you maintain the strict OS permissions on the folders containing the FileStream files.
• Secure FullText Index data.
  • FullText Index data is also stored external to SQL Server on the OS file system. Make sure you maintain the necessary OS permissions on the folders containing those index files.
• Provide security training to your users.
  • Disable the USB ports. Don't allow people to plug jump drives into the USB ports on the server.
  • Don't surf the web from the servers running SQL Server.
  • Don't click on links in email from the servers running SQL Server.
  • Don't install software from the Internet.
  • Don't give out passwords over the phone.

Ongoing Security Audits and Reviews

• Review the permissions in place on groups and accounts.
  • You are primarily looking for permissions that have been granted that should not have been granted.
• Review the members of each group and role. Log changes to group memberships.
  • Look for members that should not be in the group or role.
• Create an audit log of changes to permissions in place on groups and accounts.
  • This may be used to help determine why permission changes were made.
• Create an audit log of changes made by administrator/sysadmin accounts.
  • You may be used to identify unauthorized changes.
• Review the accounts in use.
  • Look for accounts still active that could be deactivated.
• Log all failed logon attempts, and possibly all logon attempts.
  • This helps identify attempts to guess passwords and possibly cases where someone is successfully using another persons credentials to connect.
• Review for accounts with blank passwords.
  • You can run a script to find accounts with blank passwords.
• Review the stored procedures that run on SQL Server startup.
  • Hackers sometimes install stored procedures to run on SQL Server startup.
• Look for and remove orphaned database accounts.
  • This article offers some scripts to find and fix orphaned users.
• Make an inventory of what you have (instances, databases, tables, accounts, etc.).
  • This will help you discover when changes have been made.
• Run Microsoft Baseline Security Analyzer to look for invalid security configurations.
  • Link to Microsoft Security tools.
• Run Microsoft Baseline Configuration Analyzer to look for invalid configurations.
  • Link to Microsoft Baseline Configuration Analyzer for SQL Server 2008 R2.
  • Microsoft SQL Server 2008 R2 Best Practices Analyzer Whitepaper.
• Use tools in SQL Server to insure compliance with your business rules
  • Use policy based management in SQL Server 2008.
  • Use SQL Server Surface Area Configuration in SQL Server 2005.
• Apply the latest service packs and security fixes.
  • This applies to SQL Server, the operating system, computer BIOS, routers, firewalls, etc.
• Don't grant the CONTROL SERVER permission to anyone.
  • The user could use this to grant themselves any permission.
• Don't assign the SecurityAdmin role to anyone.
  • The user could use this to grant themselves any permission.
• Discover and review all the regulations that affect your SQL Server.
  • HIPAA, PCI, SOX, GLBA, Military, etc.
• Review who has permission to run SQL profiler.
  • Users with the Alter Trace permission can run SQL Profiler.
• Stay aware of new vulnerabilities.
  • 9/20/2010: http://weblogs.asp.net/scottgu/archive/2010/09/18/important-asp-net-security-vulnerability.aspx
• Microsoft's SQL Server Auditing and Compliance.
• Microsoft's Security Considerations for SQL Server 2005.
• Microsoft's Security Considerations for SQL Server 2008.

Security Tasks for Operating System Administrators

Windows administrators are responsible for many of the steps taken to secure SQL Servers. Windows administrators should work with SQL Server administrators to:

• Define the accounts used by SQL Server related services,
• Assign permissions to groups in active directory,
• Implement secure password policies
• Insure directories containing SQL Server data are properly protected,
• Insure network access to the servers hosting the SQL Server instances are appropriately secured,
• Implement server service packs and security fixes
• Perform installation, configuration, and upkeep of antivirus software,
• Monitor spikes in logon attempts, network traffic, hard disk utilization, slow response times (often with Performance Monitor).
  • Configure Performance Monitor to send alerts when suspicious activity occurs.
• Implement audit and review policies for all the items in this section.

Security Tasks for Network Administrators

Network administrators play a vital role to secure servers ultimately accessed by the Internet. Security administrators and network administrators should work together to:

• Implement and configure firewalls,
• Configure routers to prevent/reduce Denial of Service attacks,
• Configure IIS and tools to reduce the risk of attempts to hack into SQL Server from SQL Injection and other techniques,
• Restrict access to the server hosting SQL Server through subnets and DMZs,
• Configure routers, ports and networks to only allow necessary traffic, ports, and data transfer,
• Monitore for unauthorized network access, particularly when wireless networks are deployed,
• Limit VPN access,
• Encrypt data "on the wire", often by using SSL.

Physical Security

• Lock the server room.
  • Require card swipes, PINs, two-factor authentication, etc. for server room access
• Log every entry and exit of the server room.
• Maintain a list of people authorized to the server room. Review the list occasionally.
• Use video cameras to record activity in the server room.
• Prey is one tool to assist you in tracking down stolen computers. It is free.
  • http://preyproject.com

More to consider

• What, if any, additional security concerns exist for SQL Server Integration Services (SSIS) ?
• What, if any, additional security concerns exist for Reporting Server?
• What, if any, additional security concerns exist for SQL Server in Sharepoint ?
• What, if any, additional security concerns exist for mobile applications?
  • Is data in transit secure?
  • Is data on the devices secure?
• What, if any, additional security concerns exist for data on clusters?
  • Do all the security and auditing features you need work on clustered servers?
• Microsoft SQL Server Compact Edition 4.0
  • A file-based version of SQL Server. No services required. Good for hosting small web sites. A single user DBMS.
• SQL Server in the cloud (SQL Azure)
  • What new considerations for SQL Azure? If industry or government regulations apply to your SQL Server, can they be applied and audited on SQL Server in the cloud?
  • Do you need to keep the data within the continental United States for compliance?
• Brad McGehee is working on a Security Checklist
  • http://www.bradmcgehee.com/2010/09/sql-server-security-checklist/#more-1800
• SQL Server security blogs from Microsoft: http://blogs.msdn.com/sqlsecurity
• SQL Server security newsgroups: http://social.msdn.microsoft.com/forums/en-US/sqlsecurity/threads/

Links to more SQL Server resources.