The SQL Server Defensive Dozen Part 1 - Hardening SQL Network Components

  • Article

Introduction

In order to properly secure and harden SQL Server, one should review many items related to the server communications, network library settings, and ports/protocols configurations. As a reminder from our Introduction to this series, hardening can be thought of as the process of applying Administrative and Technical controls to a system. Every article in this series will review both Administrative Controls and Technical Controls needed to properly managed and harden a system. This article will discuss network component and network-related settings for hardening SQL Server.

Administrative Controls

The primary administrative control for network communications related security is documenting the network communication settings and PKI components. Settings include, documenting SQL ports, IP addresses, anticipated or regular communication paths/routes, communication certificate, and machine names. As well, anything of importance related to network communication (possibly including any reliance on other network devices or components) should be documented. The documentation should define and reflect the current baseline (all settings) of the system.

Sidebar: three of the most important concepts for properly managing and hardening a server are:

  1. Documenting (and understanding) everything about the system baseline.
  2. Limit functionality (settings/authentication/authorization) to allow only authorized workloads.
  3. Extensive Auditing and logging (reviewing all logs and enable alerting).

Technical Controls

Below are common technical controls for review and compliance settings. Often, for a system to pass an audit (such as a Cyber Readiness Inspection) these items and settings need to be specifically addressed and documented.

Set Ports and Protocols

SQL Server can be configured to use static, non-standard ports. Dynamic ports should not be used as they require a SQL Browser service and additional UDP broadcast traffic and listener to point users to the service. In a hardened environment, no listening or browsing services (network enumeration services or network enumeration protocols) should be enabled. All connections and ports should be explicitly configured; broadcast traffic is typically blocked.

SQL can be configured to communicate over non-standard ports (not 1433), as many notorious SQL attacks (such as the SQL Slammer Worm) exploit the use of standard 1433 SQL port to search for any SQL Service on a network. It should be noted in certain organizations most services have assigned and approved ports for communication. SQL Server’s designated port is 1433 and network traffic inspected for appropriate Tabular Data Stream (TDS) content on this port, therefore TCP settings should be configured to run across all interfaces on port 1433 or per the enclave posture documentation.

Below are some Microsoft recommended best practices for network settings.

  • Enable Windows Firewall and limit the network protocols supported.
  • Do not enable network protocols unless they are needed.
  • Disable NETBIOS and SMB protocol unless specifically needed.
  • Do not expose a server that is running SQL Server to the public Internet.
  • Configure named instances of SQL Server to use specific port assignments for TCP/IP rather than dynamic ports.
  • Use extended protection in SQL Server 2012 if the client and operating system support it.
  • Grant CONNECT permission only on endpoints to logins that need to use them. Explicitly deny CONNECT permission to endpoints that are not needed by users or groups.

Use the Designated and Trusted PKI of the Organization to Identify the Server

The server computer must have a certificate provisioned, and the client machine must be set up to trust the certificate's root authority in the PKI domain (the DISA .mil domain). Provisioning is the process of installing a certificate by importing it into Windows. The certificate must be issued for server authentication. The name of the certificate must be the fully qualified domain name (FQDN) of the computer. Many organizations have processes for requesting PKI (DISA in the DoD for example). Please review the customer’s processes for more information on obtaining the proper PKI certificated for the servers and services.

Install Certificates

PKI certificated are essential for guarantee of the validity of a server in the DoD PKI infrastructure. The certificate will also be used to secure the data-in-transit communication. Request the certificate from your organizations PKI, once the certificate is received back from the request process, install it on the nodes or member servers.

To provision (install) a certificate on the server follow the directions in the article "How to enable SSL encryption for an instance of SQL Server by using Microsoft Management Console". For SQL Server Failover Cluster Instance (FCI) see the guidance in "Enable a certificate for SSL on a SQL Server clustered installation" section of the article.

Just a note, one could walk through the Certificate Import Wizard to import the certificate or use PowerShell to import it.

Note, if the service account is not a local administrator and Force Encryption is enabled, the service will not start. The service SID (or service account, not preferred) will need permissions to the machine certificate in the Certificate Store. Please refer to this article for more information. https://support.microsoft.com/kb/318605 (older article but still helpful for troubleshooting and configuration advise).

Configure SSL or TLS for Encryption-in-Transit

Protecting data in transit is an essential part of the data protection strategy or "System Posture". Since data will be moving between many locations, the general recommendation is that you always use SSL/TLS protocols to exchange data across different locations (to secure data in-transit). To configure the server to accept encrypted connections

  1. In SQL Server Configuration Manager, expand SQL Server Network Configuration, right-click Protocols for <server instance> , and then select Properties.
  2. In the Protocols for <instance name> Properties dialog box, on the Certificate tab, select the desired certificate from the drop down for the Certificate box, and then click OK.
  3. On the Flags tab, in the Force Encryption box, select Yes, and then click OK to close the dialog box.
  4. Restart the SQL Server service.

To configure the client to request encrypted connections

  1. Copy either the original certificate or the exported certificate file to the client computer.
  2. On the client computer, use the Certificates snap-in to install either the root certificate or the exported certificate file.
  3. In the console pane, right-click SQL Server Native Client Configuration, and then click Properties.
  4. On the Flags page, in the Force protocol encryption box, click Yes.

To encrypt a connection from SQL Server Management Studio

  1. On the Object Explorer toolbar, click Connect, and then click Database Engine.
  2. In the Connect to Server dialog box, complete the connection information, and then click Options.
  3. On the Connection Properties tab, click Encrypt connection.

Several known vulnerabilities have been reported against SSL and earlier versions of Transport Layer Security (TLS). DISA recommends customers upgrade and configure TLS 1.2 for secure communication.

If you have an errors after you configure it, check here.

Review SQL Alias Configurations to Verify Network Names

An Alias is an alternate name that can be used to make a connection to a data source. Please see the following links for more information Create or Delete a Server Alias for Use by a Client (SQL Server Configuration Manager) and Set a SQL Server Alias for the SQL Server Agent Service (SQL Server Management Studio).

Please note, a SQL Server with a static, non-standard port will need to have clients create aliases or advanced connection string configurations in order to connect to the instance Please see the following for more information SQL Alias for SharePoint .

Establishing the Identity SQL Server for Secure Authentication Using Kerberos

A Service Principal Name (SPN) must be registered for the SQL Server service account to allow clients to identify and authenticate the service using secure Kerberos authentication. The SetSPN utility can be used to register an SPN for the site database server SQL Server service account. The SetSPN utility must be run on a computer that resides in the SQL Server's domain and it must be run using Domain Administrator credentials. To properly configure an SPN for the SQL Server service account using the SetSPN utility, follow the steps in these procedures. One can also use the Kerberos Configuration Manager to set the SPNs in a single Domain Forest. Note the service account should not have the checkbox set that states “Account is sensitive and cannot be delegated". How to use Kerberos authentication in SQL Server, contains information about how to grant read or write permission to an SPN for an account that is not a Domain Administrator.

When the Database Engine service starts, it attempts to register the Service Principal Name (SPN). If the account starting SQL Server doesn’t have permission to register a SPN in Active Directory Domain Services, this call will fail and a warning message will be logged in the Application event log as well as the SQL Server error log. To register the SPN, the Database Engine must be running under a built-in account, such as Local System (not recommended), or NETWORK SERVICE, or an account that has permission to register an SPN see "Configure the SQL Server service to create SPNs dynamically for the SQL Server instances". Security Note: Always run SQL Server services by using the lowest possible user rights. As well, one can note the permission need to allow the computer to register it's SPN. When SQL Server is running on the Windows 7 or Windows Server 2008 R2 operating system, you can run SQL Server using a virtual account or a managed service account (MSA). Both virtual accounts and MSA’s can register an SPN. If SQL Server is not running under one of these accounts, the SPN is not registered at startup and the domain administrator must register the SPN manually.

Consider Disabling Dynamic Ports and the SQL Browser Service

As mentioned above, In a hardened environment, no listening or browsing services (or enumeration protocols) should be enabled. All communication and port access should be explicitly configured; broadcast traffic should be limited or blocked. When an instance of SQL Server starts, if the TCP/IP protocol is enabled for SQL Server, the server is assigned a TCP/IP port. If the named pipes protocol is enabled, SQL Server listens on a specific named pipe. This port, or "pipe," is used by that specific instance to exchange data with client applications. During installation, TCP port 1433 and pipe \sql\query are assigned to the default instance, but those can be changed later by the server administrator using SQL Server Configuration Manager. Because only one instance of SQL Server can use a port or pipe, different port numbers and pipe names are assigned for named instances, including SQL Server Express. By default, when enabled, both named instances and SQL Server Express are configured to use dynamic ports, that is, an available port is assigned when SQL Server starts. If you want, a specific port can be assigned to an instance of SQL Server. When connecting, clients can specify a specific port; but if the port is dynamically assigned, the port number can change anytime SQL Server is restarted, so the correct port number is unknown to the client.

Upon startup, SQL Server Browser starts and claims UDP port 1434. SQL Server Browser reads the registry, identifies all instances of SQL Server on the computer, and notes the ports and named pipes that they use. When a server has two or more network cards, SQL Server Browser returns the first enabled port it encounters for SQL Server. SQL Server Browser support ipv6 and ipv4.

When SQL Server clients request SQL Server resources, the client network library sends a UDP message to the server using port 1434. SQL Server Browser responds with the TCP/IP port or named pipe of the requested instance. The network library on the client application then completes the connection by sending a request to the server using the port or named pipe of the desired instance. See the following article SQL Server Browser Service for more information.

Conclusion

This completes the facet and considerations related to securing SQL Server network configurations related to SQL STIG Compliance. Please stay tuned for the next topic in the series discussing encryption, key management, and data-at-rest security for SQL Server.