Database - Connect to MS SQL Over TLS 1.2

This section is important if you are planning to run MS SQL Server remotely or you have an existing MS SQL Server running remotely with a lesser protocol (SSL 3.0, TLS 1.0-1.1, for example). Many Data Security Standards (DSS), PCI 3.1, for example, require TLS 1.2 use to ensure data privacy and integrity all the way to the presentation layer.

Before you proceed! The procedure that follows also includes steps you or your MS SQL administrator perform outside of MOVEit Transfer. Ipswitch is not responsible for changes to documentation, online resources, and hosted software that originate from Microsoft or other third-party vendors. The documentation that follows was accurate at the time of this MOVEit Transfer release.

Purpose

PCI 3.1 requires end-to-end TLS 1.2 with no backward-compatible or 'fall-back' connections. For example, 'fall back' connections can be negotiated between a client and server if the server supports a range of SSL/TLS versions and the client is running at an earlier protocol version that is less secure than TLS 1.2.

When you run a database server (such as MS SQL Server) that is on a different host than the MOVEit Transfer Server, you should ensure that the appropriate level of security is applied to your transactions with MS SQL Server.

To ensure that MOVEit Transfer database transactions with MS SQL Server are run over TLS 1.2, you must:

• Enable MOVEit Transfer to use TLS 1.2 from its Native SQL Client. (This is done using settings at the MOVEit Transfer Server.)
• Configure the MS SQL Server host for strict use of TLS 1.2. (Note: If applicable, this could affect other applications/middleware using your remote SQL Server.)
• Apply periodic monitoring or configuration audits to ensure the expected level of cipher support is maintained during operations (using an SQL Active Monitor or PowerShell Monitor in Whats Up Gold, for example).

Overview

Running SQL Server over a TLS 1.2 connection involves the following steps:

Note: Always consider your security-availability tradeoffs, timing, and impact of these changes. Applying strict endpoint security policies can enable the possibility of refused connections to non-compliant or out-of-spec clients.

1. Apply changes on the MOVEit Transfer Server to enable TLS 1.2 connections. (It is best practice to support TLS 1.2 at the MOVEit Transfer Server first before you require your end-user clients to connect with it).
2. Require MOVEit Clients to use TLS 1.2. You can use the MOVEit Transfer Configuration Utility to select TLS 1.2. This setting will be enforced on TCP connections initiated with the MOVEit Transfer Server.
3. Apply and restrict TLS 1.2 at the MS SQL Server. This enforces TLS 1.2 connections (encrypted transactions with MOVEit Transfer).
4. Verify and monitor changes. It is best practice to verify cipher use with a port scanner such as Nmap and a traffic capture tools such as Wireshark. You can monitor regularly using tools such as WhatsUp Gold.

MOVEit Transfer Configured with a Remote MS SQL Server

Step 1: Enable TLS 1.2 on the MOVEit Transfer Server

The following will enable the MOVEit Transfer Server to connect with TLS 1.2 to the remote MS SQL Server (using its SQL client). This is not a restrictive measure. (Applying this setting first ensures that clients will still be able to connect after Step 3 -- once you restrict communication at the MS SQL Server host).

MOVEit Transfer Server requires Windows Server, which by default uses the Schannel LIbrary (Schannel.dll) as part of its Security Support Provider Interface (SSPI). These instructions detail how to apply registry key values that Schannel expects for runtime configuration (TLS/SSL connections).

1. On your Windows Server running MS SQL Server, backup your Windows Registry. (Backup and restore instructions from Microsoft Support).
2. Startup the Windows Server Registry editor (regedit).
3. Locate the following registry key:

   HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL

4. Enable TLS 1.2 and make it a default. Look at the Schannel protocol subkeys. For example:

   

Step 2: Require MOVEit end-user clients to use TLS 1.2

To limit MOVEit end-user client connections to use HTTPS and FTPS over TLS 1.2, you can use the MOVEit Transfer Configuration Utility. Ensure that TLS 1.2 is enabled, but you can unselect lesser protocols (The MOVEit Transfer Configuration Utility runs on the MOVEit Transfer Server Host).

Step 3: Enable and force TLS 1.2 on the MS SQL Server and Host

• Configure default connection settings for Schannel
• Configure SQL Server network configuration to force encryption

Configure connection settings for Schannel

MS SQL Server requires Windows Server, which by default uses the Schannel LIbrary (Schannel.dll) as part of its Security Support Provider Interface (SSPI). These instructions detail how to apply registry key values that Schannel expects for runtime configuration (TLS/SSL connections).

1. On your Windows Server running MS SQL Server, backup your Windows Registry. (Backup and restore instructions from Microsoft Support)
2. Startup the Windows Server Registry editor (regedit).
3. Locate the following registry key:

   HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL

4. Look at the Schannel protocol subkeys. For example:
   • SCHANNEL\Protocols\TLS 1.2\Client
   • SCHANNEL\Protocols\TLS 1.2\Server

   On the MS SQL host machine, ensure the value for the DWORD DisabledByDefault entry is set for the "TLS 1.2\Server" subkey to zero (0x0)--meaning false, to denote Schannel will use TLS 1.2 as a default. For the TLS 1.2\Server\DisabledByDefault subkey.

5. Registry entries for protocol versions other than TLS 1.2 (TLS 1.1, TLS 1.1, SSL 2.0, ...) should be configured so that DisabledByDefault = 1 and Enabled = 0.

Note: When a range of SSL protocols are enabled at the MS SQL Server host, an encrypted connection on one side can re-negotiate and downgrade or a per-connection basis to accommodate a lesser protocol running on the client host. To prevent this behavior, you must require encryption at the MS SQL server host and explicitly default the use of lesser security protocols to a false state.

Full Microsoft KB article that details enabling TLS 1.2 can be found here.

Configure SQL Server network configuration to force encryption (and restart the DB Server)

Next, open SQL Server Configuration Manager on the remote database host machine and change the connection policy to force SQL clients to connect to it using encryption.

Microsoft SQL Server Configuration Manager (version 2014 shown)

On the MS SQL Server Host machine:

1. Open the SQL Server Configuration Manager.
2. Select SQL Server Network Configuration.
3. Select and right click Protocols for <my-sql-server-db> and then choose Properties.

   --Where <my-sql-server-db> is the name of your Microsoft SQL Server DB Server.

4. Click the Flags tab, and on the Force Encryption drop down list, choose Yes and then click Apply.

   

   After you click Apply, a pop-up dialog reminds you that you must restart the database server for the changes to take effect.

5. In the left pane, select SQL Server Services, right click SQL Server (<my-sql-server-db>) and choose Restart.

Step 4: Verify traffic between SQL client (running on the MOVEit Transfer Server) and remote MS SQL Server

For the last step, use a MOVEit Transfer client to generate request traffic with the MOVEit Transfer Server and send transactions to the remote MS SQL database.

1. Open a MOVEit Transfer client (REST API, Web UI, MOVEit Client, for example).
2. Install Wireshark on the MOVEit Transfer server or a desktop on the same subnet as the MOVEit Transfer server.
3. In Wireshark, filter on the MS SQL Server's IP address, for example:

   ip.addr == <transfer-server-ip-address>

4. Use a MOVEit Transfer client to perform an action that requires the MOVEit Transfer Server to open a connection with the MS SQL Server database, for example, upload a file.
5. Check that the presentation layer protocol listed is TLS 1.2.

   TDS exchanges (such as an SQL Query) between MOVEit Transfer Server host and the MS SQL Server should show TDS traffic with TLS protocol data unit information (SQL queries and batch queries should not be readable).

After you ensure your TDS traffic is encrypted with TLS, you can check that the ciphers available to the MS SQL Server host are strictly TLS version 1.2. You can do this with port scanners such as Nmap. For example, this command: nmap --script ssl-enum-ciphers -p 1433 <my-sql-server-host> returns the version of TLS along with warnings (if applicable).