Hardening SQL Server Installation
SQL Server is a repository of sensitive information for organizations, and that is why, it is important to ensure that only authorized users have access to this sensitive information. However, securing SQL Server in a way that is not likely to errors is not an easy task, and as database administrators (DBAs), we have to perform a series of additional steps to harden security configuration of our SQL Server implementation. In this article, I’ll discuss the series of key tasks, which database administrators (DBAs) must perform to secure SQL Server installation, and prevent it from internal and external attacks.
Authentication
Microsoft SQL Server supports two modes of authentication: Windows Authentication and Mixed Mode Authentication. In accordance with the recommendation of SQL Server security best practice document, always prefer Windows Authentication for your SQL Server installation, unless legacy applications requires Mixed Mode Authentication for backward compatibility and access reasons. Windows Authentication is more secure than Mixed Mode Authentication, and when enabled, Windows credentials (that is Kerberos or NTLM authentication credentials) are trusted to log on to the SQL Server. Windows logins use a number of encrypted messages to authenticate to SQL Server, and under no means the passwords of these logins are passed across the network during the authentication process. Moreover, Active Directory provides an additional level of security with the Kerberos protocol. As a result, the authentication method is more reliable and robust; therefore, management can be reduced by leveraging Active Directory groups for role-based access to SQL Server. In comparison to Windows Authentication mode, Mixed Mode Authentication supports both Windows accounts and SQL Server-specific accounts to log in to the SQL Sever. The logon passwords of SQL logins are passed over the network for authentication, which makes SQL logins less secure than Windows logins. For more information about viewing and changing SQL Server Authentication Mode, read Microsoft Book Online article: Change Server Authentication Mode.
Secure sa account
The sa account is vulnerable when it exits unchanged, as potential SQL Server attackers are aware of this powerful account, and this makes hacking one step easier if they take control of this powerful account. To prevent attacks on the sa account by name, rename sa account to a different account name. To do that, in Object Explorer expand Logins, then right-click sa account and choose Rename from the menu. Alternatively, execute the following T-SQL script to rename sa account:
USE [master]
GO
ALTER LOGIN sa WITH NAME = [
GO
In addition to this, disable sa account on your SQL Server instance.
Use complex passwords for sa and SQL Server-specific logins
When Mixed Mode Authentication is used, ensure that complex passwords are used for sa and all other SQL Server-specific logins on SQL Server. This can be done by checking the "Enforce password expiration" and "Enforce password policy" options for sa and all other SQL logins. These two options ensure that and all other SQL Server-specific logins are abide by the login policies of the underlying operating system. In addition to this, set MUST_CHANGE option for any new SQL logins. This ensures that login have to change its password on first logon.
Membership of sysadmin fixed-server role and CONTROL SERVER permission
Carefully choose the membership of sysadmin fixed-server role because members of this role can do what ever they want on SQL Server. Moreover, do not explicitly grant CONTROL SERVER permission to Windows logins, Windows Groups logins and SQL logins because logins with this permission gets full administrative privileges over a SQL Server installation SQL Server. By default, sysadmin fixed-server role has this permission granted explicitly.
SQL Server Administration
Avoid managing SQL Server instances using sa or any other SQL login account that has been granted CONTROL SERVER permission, or is a member of sysadmin fixed-server role. Instead, institute dedicated Windows Logins for DBAs, and assign these logins sysadmin rights on SQL Server for administration purposes. Moreover, to grant permissions to users, use built-in fixed server roles and database roles, or create your own custom server roles and database roles that meet your needs of finer control over permissions.
Revoke Guest user access
By default, guest user exists in every user and system database, which is a potential security risk in a lock down environment because it allows database access to logins who don’t have associated users in the database. Due to this potential security risk, disable guest user access from all user and system databases (excluding msdb). This ensures that member of public server role are not able to access user databases on SQL Server instance, unless they have been assigned explicit access to these databases.
Limit permissions assigned to a public role
Due to potential security risk, revoke public role access on the following extended stored procedures:
Furthermore, do not explicitly assign permissions to a public role on user and system stored procedures. To list the store procedures that are available to a public role, execute the following query:
SELECT o.[name] AS [SPName]
,u.[name] AS [Role]
FROM [master]..[sysobjects] o
INNER JOIN [master]..[sysprotects] p
ON o.[id] = p.[id]
INNER JOIN [master]..[sysusers] u
ON P.Uid = U.UID
AND p.[uid] = 0
AND o.[xtype] IN (‘X’,’P’)
Reduce SQL Server Surface Area
Configure SQL Server installation with only required features, and disable unwanted features after installation using SQL Server system’s surface area. You can also use Policy Based Management feature to create system policies for implementing granular configuration settings for one or more SQL Server systems.
Hardening SQL Server Ports
Change the default ports associated with SQL Server installation by using SQL Server Configuration Manager. Furthermore, use specific TCP ports instead of dynamic ports. In addition, make sure that common TCP ports, such as 1433 and 1434 are not used for the client’s requests and communication because, these ports are well known that makes them a common target for hackers.
Disable SQL Server Browser service
Make sure that SQL Server Browser service is only running on SQL Servers where multiple instances of SQL Servers are running on a single server. This is because SQL Server Browser service enumerates SQL Server Information on the network, which is a potential security threat in a lock down environment.
SQL Server service accounts
Create dedicated low privilege domain accounts to run SQL Server services. In addition to this, regularly review the membership of SQL Server service accounts, and ensure that they are not member of any domain users group or local groups that would grant them unnecessary permissions. For more information on what permission each SQL Server service account require, see Configure Windows Service Accounts and Permissions.
Secure SQL Server ErrorLogs and Registry keys
Secure SQL Server ErrorLogs and Registry Keys using NTFS permissions because they can reveal a great deal of information about SQL Server instance and installation.
Encryption
Use SQL Server encryption features to encrypt sensitive information within databases and backups. Read my article (SQL Server Encryption) on SQL Server Pro, where I gave an overview of the SQL Server encryption model, and described the encryption options available in SQL Server and how you can use them to encrypt sensitive information stored inside your SQL Server databases.