Hardening SQL Server

SQL Server applications rely on data, and protecting that data has never been more important than it is today. SQL Server is a popular target for hackers, so your data is at risk of being intentionally compromised. In addition, your data is at risk of being accidentally compromised. You can minimize these risks by hardening SQL Server, which involves reducing its surface area and controlling access to it.

Related: Probing for Vulnerabilities to Harden SQL Server

Reducing the Surface Area

The pathways that can be exploited to gain access to or elevate privileges in SQL Server are referred to as the surface area. To reduce the surface area of SQL Server, apply the following best practices.

Install only the required SQL Server components. Installing SQL Server is often looked at as little more than a mundane task, but that’s when you need to begin your hardening efforts. When installing SQL Server, you shouldn’t include SQL Server Analysis Services (SSAS), SQL Server Integration Services (SSIS), or the Full-Text Engine and its Filter Daemon Launcher. It’s easy to add these components later if they’re required. 

Don’t install SQL Server Reporting Services (SSRS) on the same server as the database engine. If you install SSRS on the same server as the database engine, Web services will open a hole in your security layer. Historically, IIS and Web services have had a lot of vulnerabilities, which allowed hackers to take control of the server, thus putting anything hosted on the server at risk. You can avoid this risk by not installing SSRS on the database server.

Disable the SQL Server services that won’t be immediately used. You should disable (or leave disabled) the services that aren’t being used. For example, you should:

Don’t use the default TCP/IP ports. After installation, you should configure SQL Server to use nondefault numbers for the TCP/IP ports. The default ports are known entry points into SQL Server, and they have been exploited in the past. This is less of a concern if your server isn’t accessible through the Internet, but viruses, Trojan horses, and hackers have other ways to exploit these holes. This approach doesn’t remove an attack pathway; it merely conceals it. Thus, it’s known as security by obscurity.

Disable the network protocols that aren’t required. You should disable (or leave disabled) the network protocols that aren’t needed. In most cases, both Named Pipes and TCP/IP aren’t needed for connecting to SQL Server, so you should determine the best network protocol for your server and disable the other one. For example, Figure 1 shows Named Pipes disabled and TCP/IP enabled but configured to listen on a nondefault port.

Figure 1: Disabling the network protocols that aren’t required

In Figure 1, note the status of the Shared Memory and Virtual Interface Adapter (VIA) protocols. You can usually leave Shared Memory enabled because this protocol is used by only local connections on the server. However, you should leave the VIA protocol disabled unless you’re using VIA hardware.

Make sure that the antivirus and firewall software versions are current and configured correctly. To be properly protected, the server should be running a current version of the antivirus and firewall programs. In addition, they should be configured to close any ports that might have been left open by default. For example, you need to make sure that the firewall isn’t leaving the default port for SQL Server open.

Manage the surface area configurations. After you initially reduce the surface area, you need to manage its configurations. In SQL Server 2005, Microsoft introduced the Surface Area Configuration Manager (and its command-line counterpart sac.exe) for this purpose. However, this tool performs only the most common management tasks (e.g., enabling remote connections and supported protocols). To perform the less common tasks (e.g., designating service accounts and authentication mode), you need to use the SQL Server Configuration Manager, the system stored procedure sp_configure, or a Windows tool (e.g., Windows Management Instrumentation—WMI).

In SQL Server 2008, Microsoft replaced the Surface Area Configuration Manager with the Policy-Based Management system. With this system, you can manage the configurations for the full surface area—it’s a one-stop shop for your surface area needs.

Figure 2 shows examples of the surface area policies used by the Policy-Based Management system. Each policy contains a condition and the target to which that condition applies. The system monitors the target, making sure it stays in compliance with the condition. You can even have the system roll back changes that violate the condition.

Figure 2: Using the Policy-Based Management system to manage the surface area configurations

Controlling Access

Controlling access to SQL Server is more than just granting permissions to users. Access control also encompasses the following.

Configuring authentication. When possible, you should use Windows Authentication only.

Configuring administrative accounts. You should delete the BUILTINAdministrators account and disable the systems administrator (sa) account. Typically, you want to heavily restrict which users get systems administrator privileges and instead use server roles for privileged users who need to perform specific server-level functions, such as backup operators or security administrators. It isn’t yet possible to restrict permissions of users with systems administrator privileges, but you can use SQL Server auditing to monitor them.

You should also restrict membership in the Local Administrators group. It’s possible for a local administrator to gain access to a SQL Server machine and grant systems administrator rights to himself or herself. It would be difficult to do so unnoticed, but it can be done. The same strict protocols must be taken to protect the server hosting SQL Server as the SQL Server instance itself.

Provisioning service accounts. The mistake I see most often with SQL Server service accounts is granting them more permissions than are required. Before I explain how to avoid that mistake, you should be aware of the best way to create them.

When SQL Server is hosted on an OS earlier than Windows Server 2008 or Windows Vista, your best option is to use non-user domain accounts for service accounts. A non-user domain account isn’t linked to a real user. (Real users often have permissions granted on many different machines, and it’s important to minimize the permissions granted to service accounts.) A non-user domain account has a password that must meet password requirements and be managed, but the account typically isn’t associated with a mailbox.

You shouldn’t use built-in Windows system accounts (e.g., LocalService, LocalSystem) for SQL Server service accounts because the built-in system accounts inherit certain elevated rights in Active Directory (AD) that aren’t required by SQL Server. For example, the Network Service account is allowed to authenticate across the network using the machine account, and the LocalSystem account has permissions to create and delete its own Service Principal Names (SPNs).

When SQL Server is hosted on Windows Server 2008 or later or Windows Vista or later, SQL Server will use per-service SIDs by default to provision local resources. A per-service SID is a security identifier assigned to the service that’s created to manage access to objects for isolated services. Per-service SIDs aren’t tied to separate accounts and can’t be used by any service other than the specific service for which it was created. Certain features rely on the configured service account instead of the per-service SID (e.g., xp_cmdshell, SQL jobs executing under systems administrator rights) for accessing remote resources such as another server. As a result, it’s still important to appropriately configure service accounts for the services.

When assigning permissions to SQL Server service accounts, you should always follow the rule of least privilege. In general, you should add the service account to the appropriate local group created specifically for each SQL Server component. This allows the service account to inherit any required permissions at the Windows level. Granting permissions to the group instead of the service account ensures that the permissions don’t get lost if the service account changes. It also prevents the old service account from retaining permissions it no longer needs.

If additional permissions are required for the service account, they should be granted to the appropriate SQL Server local group rather than the service account directly. Rights that are often granted to service accounts are Lock Pages in Memory permissions to prevent trimming of memory managed by SQL Server and Perform Volume Maintenance Tasks permissions to enable instant file initialization. These two common permissions can be set using Group Policy Editor (GPE).

SQL Server service accounts don’t require any escalated permissions in Windows or NTFS other than those they inherit through membership in the appropriate group. The service accounts should never be added to the Local Administrators group.

The SQL Server Agent service account requires membership in the systems administrator server role in SQL Server. For this reason, the SQL Server Agent should use a different service account than the other SQL Server components.

Creating security groups. You shouldn’t grant individual users access to SQL Server. Instead, you should create security groups in AD for specific servers and sets of permissions, then add individual users to the appropriate groups as required. The DBA or operations team should manage the security groups. Often, organizations use a tool that lets team members make a change (e.g., add a user) to a security group but requires the group owner to approve the change or roll it back. You should never allow a group that isn’t under the control of the team to be added to one of the security groups, because doing so removes the ability to readily see the group members and control who is added to or removed from the group.

Setting up schemas and object ownership. When users without systems administrator privileges need to directly manage certain portions of a production database, you need to separate the objects they’re allowed to manage from all the others. The best way to do this is to place those objects into a specific schema and grant permissions at the schema level. Individual users and groups shouldn’t own the dbo schema or the objects in it.

Cross-database ownership chaining shouldn’t be enabled. If it’s enabled, certain situations could arise that allow a user to unintentionally get permissions to objects in a different database than the current one.

Databases shouldn’t be owned by individual users. Allowing a user to be the owner of a database automatically maps that user to the dbo database user, which bypasses permissions checking in the database. This level of privileges can’t be revoked or denied unless you change the owner of the database. In addition, if the database’s owner is an invalid user, you can receive unexpected and hard-to-troubleshoot permission failures in the database. As long as you disable the systems administrator account and cross-database ownership chaining, the systems administrator account can be made owner of all databases without security concerns.

One database option that you should always disable is the Trustworthy option. This option is often used to simplify running CLR code with EXTERNAL_ACCESS or UNSAFE permissions in the database. It is also used to allow procedures and functions that use impersonation to perform server-level functions. The Trustworthy option basically tells the server instance that the systems administrator trusts the database’s owner. You can achieve these actions without enabling the Trustworthy option—you use a certificate to sign the code or module. Figure 3 shows the Database Properties dialog box with the Trustworthy option and cross-database ownership chaining disabled.

Figure 3: Disabling the Trustworthy option and cross-database ownership chaining

The Top Priority

A DBA’s number one priority is protecting the SQL Server instances and the data they contain. Hardening SQL Server by reducing the surface area and controlling access is a big part of protecting the server. Using the practices I described will help you protect your SQL Server instance and your data.