SQL Server: Securing Network Communications

This article is intended as a guide to securing network communications between SQL Server clients and servers. It will also provide basic configuration guidance on native Windows Server features (and some smaller, common add-ons) capable of using remote SQL Server repositories.

 

What is (and isn't) in scope in this article?

• This article will focus primarily on products within mainstream support. Products in the early stage of extended maintenance may be covered as a secondary consideration.
• Details on topics such as authentication and cryptography are only referenced at a high-level suited to operational contexts. There is no intent to explore the inner workings of protocols.
• This isn't an all-encompassing guide to securing SQL Server or dependent applications. There's other great articles on how to configure the product and supporting elements like firewalls, service accounts, permissions and so on. This article is primarily concerned with securing data as it travels over-the-wire.

 

What are the objectives of this article?

Simply put: To give you a working understanding of some of the concepts behind securing your SQL Server over-the-wire traffic as well as the practical steps for doing so. The process for this looks like:

• Overview
• Identify the limitations of current SQL Server and client installations
• Establish secure authentication using Kerberos
• Configuring TLS and SSL
• Encrypt traffic using TLS
• Enumerate Windows Server components and their compliance statuses

 

Tools used in this article

• Microsoft Message Analyzer 1.4 (download): For capturing network traffic as a means for demonstrating the success or failure of Kerberos and TLS.
• SQL Server Management Studio (2012 with SP3) (download): For running the queries listed in this article.
• SQL Server Configuration Manager (2012 with SP3): For configuring network-related settings, which for this article relates primarily to encryption. This will be installed as part of the above Management Studio package by default.
• klist.exe: Included with more recent operating systems. The version with Windows Server 2012/Windows 8 and later has a few more options but either version will suffice for this article.
• ldp.exe: Included with the Remote Server Administration Tools (also known as RSAT; download). Useful for quickly checking and resolving Kerberos SPN (including attribute permissions issues).
• Active Directory PowerShell module: Also included in RSAT. Useful for quickly checking and resolving Kerberos SPN issues.

 

↑ Back to top

1. Overview

Performing a default installation of Windows Server and SQL Server will result in a functional service but not necessarily the most secure. One facet of security is the data sent over the network between the server and client, and how well protected it is. This can be further broken down into two categories:

1. Authentication traffic
2. SQL payload traffic

For this discussion, it doesn't matter what Kerberos is as much as what it does for you in comparison to the older NTLM authentication packages. In short, Kerberos authentication is considered more secure^[1] which is why Microsoft has been recommending it ahead of NTLM since at least as early as SQL Server 2005^[2] (though it was still achievable under SQL Server 2000).

Kerberos is likley to be functioning well even on a default Windows Server installation. However, once you start adding application layers on top - such as SQL Server or even out-of-the-box features like AD FS, some additional work can be required to get it working with these additional services. This article will look at the basic configuration tasks and reasons for failing.

Once authentication is being securely handled by Kerberos, you're still left with securing your SQL Server payload as it traverses the network. Much as with web traffic, this is handled through encryption using protocols such as TLS and SSL. Though, unlike Kerberos which more or less either works or doesn't, there's a lot of different configurations available for TLS and SSL, and as you might expect, some are better then others.

 

↑ Back to top

2. Identify the limitations of current SQL Server and client installations

SQL Server provides some informative views that quickly identify the protocols used in client connections. The T-SQL query below is a good general purpose tool for determining which applications on which hosts are not using Kerberos (for authentication) or encryption (for data). It's important to note that there's no detail on what the encryption configuration looks like. That has to be obtained separately through mechanics such as SCHANNEL event logging or a network trace tool such as Message Analyzer - which is covered later on.

SELECT
  [ses].session_id,
  [ses].login_time,
  [ses].host_name,
  [ses].program_name,
  [ses].host_process_id,
  [ses].client_interface_name,
  [ses].login_name,
  [ses].status,
  [con].net_transport,
  [con].protocol_type,
  [con].encrypt_option,
  [con].auth_scheme,
  [con].client_net_address
FROM
  sys.dm_exec_connections AS  [con]
  INNER JOIN sys.dm_exec_sessions AS  [ses] ON  [con].session_id = [ses].session_id
ORDER BY
  [ses].host_name,
  [ses].session_id

Adding a WHERE clause may help to include or exclude particular rows as part of identifying problems or tracking remediation progress.

The key columns to focus on are [encrypt_option], [auth_scheme] and [client_interface_name].

https://7mjfkq-dm2305.files.1drv.com/y4mnYF5e4Z0UXXHWMrjPqZp93px3RE4A7Cr0kK_1kUq5ea9rc21YHsuKOlQO04atunGTXtWFbQc4glTDMKzAeOi7zqtPwE8vIt3w9IyRbbKVjCVYjBXfEgogm26Sok87r3WGCJx_KUqHbJZBpgPeMrquyezYOTKITtzvSMKVxiUthrsPUuiVyU074so31enFVVihPl8TqGU2Y-9pDb5k-ntHA?width=1171&height=121&cropmode=none

Figure 1: SQL Server results shown in Management Studio.

Here, we can see that this SQL Server host has been correctly configured for Kerberos authentication - which we'll still cover later in the article. We can also see that encryption is present but not being enforced as some connections have a value of TRUE while others are FALSE for the [encrypt_option] column.

We can also see different SQL providers listed in the [client_interface_name] of the next screenshot.

https://7mgvew-dm2305.files.1drv.com/y4mYYX87ny6KY9gHLZJJkKeqg6hfh2NU7PaFZUlKNiDIz9owgOZEyJaSgLIMIBomON6kAZsaSv9MGauAFCFJgQx8qDfsdM6G8ovzwsEybyVdMszkGgnLZUjAz06Mv5dUcR1vGNIvlPV-NyJa7eflWZw2jB2Z-ZAfUlqYY7yQIy9fPjTz_WQSKVk7JPhyLjavb3bRvrzJ86dFNVhvN-WtbLBaw?width=1286&height=83&cropmode=none

Figure 2: SQL Server results shown in Management Studio.

 

 

↑ Back to top

3. Secure authentication using Kerberos

Before encrypting the SQL data traffic, it's prudent to ensure that the SQL Server client-to-server authentication is strong. Kerberos achieves that. To summarize some of the key benefits of using Kerberos ahead of NTLM:

1. Mutual authentication: In NTLM, the server verifies the client is who it claims to be, but the client does not verify the server is legitimate. Kerberos usues mutual authentication meaning both parties validate the other, reducing the ease with which a man-in-the-middle compromise can be realised.
2. Secure authentication tickets: These tickets negate the need to send a password over the wire.

It's worth clarifying that Kerberos isn't part of SQL. Kerberos is part of the underlying Windows operating system and SQL Server just happens to be capable of making use of it.

As a by-product of getting Kerberos up and running, you will also then be able to leverage something called Kerberos Constrained Delegation (often shortened to KCD), but how much benefit that is depends on variables outside the scope of this article. You can read more on KCD here.

 

3.1 Correct SPN values haven't been registered

While in-depth documentation can be found here on default SPN registration, a commonly found example of an issue in modern SQL Server out-of-the-box installations revolves around SQL Server not registering the SPN of the MSSQLSvc/<dnsHostname>:<port> format. An example of what correctly registered SPNs might look like is:

MSSQLSvc/SQL01.mydomain.com
MSSQLSvc/SQL01.mydomain.com:1433

 

3.2 SPNs registered under the wrong account

SPNs must be registered under the account under which the database service has been configured to run. As a guide to which account the SPN(s) should be added:

Service account type	Examples	Corresponding Active Directory account
Built-in	Local System, Network Service	AD Computer account, i.e. CN=SQL01
Domain account	mydomain\svcSqlAccount	Domain user account, i.e. CN=svcSql-db
Managed Service Account (MSA)	mydomain\svcSqlMSA	CN=svcSql-db
Virtual account	NT SERVICE\MSSQLSERVER	AD Computer account, i.e. CN=SQL01; Usable with Kerberos but unusable in a cluster (including AlwaysOn).
Local user account	sql01\mySvcAccount	Incompatible with Kerberos

Table 1: Different database service account types and SPN applicability.

 

3.3 Duplicate SPN values

An SPN must be unique across the directory service. If duplicate SPN entries exist then Kerberos won't work for the duplicated service.

One common scenario that results in duplicate SPN entries is changing the database service account. In principle, it shouldn't be an issue as SQL Server registers SPNs when the database service starts and de-registers them when it stops. However, in practice, if either the service account doesn't have the ability to modify the servicePrincipalName attribute or additional SPN entries have been added manually, then this contributes to SPN entry duplication.

setspn.exe makes the reporting of duplicate SPN values simple, though a domain or forest-wide search can take a while. To perform a duplicate SPN check in the current domain, run the following from a command prompt or PowerShell session:

setspn -x -t *

setspn.exe has more examples on adding and deleting SPNs inline, which you can show by running:

setspn /?

 

3.4 Service account cannot update the servicePrincipalName attribute

When the SQL Server database service starts, it attempts to register its service principal name (SPN). This can fail if the account used for the database service does not have the ability to write to its own servicePrincipalName attribute in Active Directory. The failure - or success, in updating the servicePrincipalName attribute is recorded in the SQL Server logs.

Fortunately, it's easy enough to resolve this issue using the ldp.exe utility - which as a side note works on both GUI and Server Core installations.

1. Launch ldp.exe.
2. Connection menu -> Connect.
3. Specify the DNS domain name or DNS domain controller name in the "Server" field, along with the appropriate port (389 for encrypted and 636 for encrypted - which will also require the SSL checkbox to be checked) and click OK.
4. Connection menu -> Bind. Choose the appropriate "Bind type" and "Encrypt traffic" values, but if you're using a domain administration account, you can simply click OK to accept the defaults and bind.
5. View menu ->Tree.
6. Use the "Base DN" drop-down list to select the domain root (or you can manually type in a full distinguished name to go to a specific location.
7. Browse to the location of the SQL Server service account.
8. Right-click the service account and choose Advanced -> Security descriptor.
9. Leave the SACL and Text dump check boxes unchecked, and click OK.
10. Click on the "Trustee" column heading to sort by the account name and scroll down to find the entries listed as "NT AUTHORITY\SELF".
11. Check that there is no "SELF" entry where the "Right" is set to "Write property (Validated write to service principal name)". There shouldn't be, but if there is then you're not likely to be facing a permissions issue on the servicePrincipalName after all and you can stop here.
12. Click the Add button.
13. Fill out the ACE dialog window as shown below and use the OK then Update button to commit the change to the directory service.

https://hz22bg-dm2305.files.1drv.com/y4msMLHH7HAHzQfdzVa5rstdqWv_gTgQFssSL2YLx744_sTGiOCCqyLvQmUKYgVp0_TQSqV5DqBtTlOzIKpbH95u47MHqZ9fN5l-EtXzSARamo7BMesqazH5nzXiGhrYgSF3l_mBBFacS12fCgQ_xgSYfoEgM8j5-gOjEZQ5Mi1M3bGTpoNc5pSD_OHwNtlDVhmKM5Hlpj4XQ8UfjP1E_FfaA?width=599&height=618&cropmode=none

Figure 3: Using LDP to grant the database service account the right to write to the servicePrincipalName attribute.

 

 

↑ Back to top

4. Configure TLS and SSL

This section relates to the configuration of the TLS and SSL protocols, which as mentioned already, is an operating system-level task. Configuring SQL Server to make use of the resulting configuration is in the next section, "Encrypt traffic using TLS".

4.1 Obtain a certificate for TLS

There are a number of methods for obtaining a certificate, however, as this isn't a discussion about certificates or PKI, only the basic requirements will be discussed.

The certificate can be self-signed, from an internal PKI like Active Directory Certificate Services (AD CS) or even a public certificate, as the type of certificate is your stock-standard web server certificate.

I'm going to use PowerShell for the following examples. Windows Server 2012/Windows 8 with RSAT and above include a PowerShell module named PKI which contains two useful commands for quickly obtaining a certificate of either the self-signed or internal PKI. Respectively, these are:

• New-SelfSignedCertificate
• Get-Certificate

 

PowerShell: Creating a new self-signed certificate

New-SelfSignedCertificate -DnsName sqlserver.mydomain.com -CertStoreLocation Cert:\LocalMachine\My

PowerShell: Enrolling for a certificate from an internal PKI service

Get-Certificate -Template WebServer -SubjectName CN=sqlserver.mydomain.com -DnsName sqlserver.mydomain.com -CertStoreLocation Cert:\LocalMachine\My

Confirmation of a successful enrolment can be achieved through running:

Get-ChildItem -Path Cert:\LocalMachine\My | fl Thumbprint,DnsNameList,Subject,Issuer

Depending on your SQL Server service account configuration, you may need to grant "Read" access to the certificate's private key. This will be covered in the section below, "Encrypt traffic using TLS".

 

4.2 TLS defaults per operating system

The table below summarizes the default configurations of more recent/current operating systems taken from the excellent write-up on the Directory Services blog, here. There are also a number of support articles and blogs which illustrate which SQL Server platforms and client components are already or capable of being made TLS 1.1 and 1.2 compliant:

• TLS 1.2 support for Microsoft SQL Server
• TLS 1.2 Support for SQL Server 2008, 2008 R2, 2012 and 2014

It's worth noting briefly that TLS 1.3 is currently in draft form. Additionally, Microsoft does not appear to have made any formal announcements as to which platforms and client components may be refreshed to incorporate TLS 1.3.

Operating system	SSL 3.0		TLS 1.0		TLS 1.1		TLS 1.2
Client	Server	Client	Server	Client	Server	Client	Server
Windows Server 2012 and 2012 R2	Enabled	Enabled	Enabled	Enabled	Enabled	Enabled	Enabled	Enabled
Windows 2016	Enabled	Enabled	Enabled	Enabled	Enabled	Enabled	Enabled	Enabled

Table 2: Default SCHANNEL settings per operating system.

 

4.3 What's the difference between the Client and Server registry keys?

Put simply, "Client" registry keys govern outbound protocol availability. For example, if an application host is making a connection to a remote SQL Server then it will governed by which protocols are enabled under the "Client" registry keys.

It follows then that the "Server" registry keys govern which protocols are available for inbound connections. So, continuuing with the same example, if TLS 1.0 is disabled under the "Server" registry key on the SQL Server then the application server won't be able to negotiate TLS 1.0 with the SQL Server. What you have to be extremely mindful of with server-side changes is that no form of client; not a computer, printer, POS device, hardware load balanacer - nothing, will be able to connect to that server using TLS 1.0 for any service at all (which includes SQL Server, of course).

Of course, it's possible to mix the "Server" and "Client" settings as they operate completely independently of each other. Again, using the same example and focusing on the SQL Server, if TLS 1.0 is enabled under the Server key but disabled under the Client key, then remote clients will be able to connect in to the SQL Server using TLS 1.0 just fine, but the SQL Server won't be able to initiate connections out to other servers using TLS 1.0.

 

4.4 So, what exactly are we trying to achieve here?

The objective is to see that only TLS 1.1 and above are enabled, meaning SSL 3.0 and TLS 1.0 should be disabled where possible. As noted above, one key indicator as to whether it's going to be possible or not is to determine if any client connections have a value of "OLEDB" in the [client_interface_name] column. If you do, then SSL 3.0 (everything below that is disabled by default) can be disabled but TLS 1.0 must remain enabled on both the SQL Server host (under the Server registry key) and the client host (under the Client registry key).

Borrowing from the Directory Services' team post above, here's what the registry tree below the root of [HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\Schannel\Protocols] should look like after configuration. You'll likely find that few to none of these exist by default meaning you'll have to create them:

https://msdnshared.blob.core.windows.net/media/TNBlogsFS/prod.evol.blogs.technet.com/CommunityServer.Blogs.Components.WeblogFiles/00/00/00/58/02/metablogapi/clip_image005_70C66D2C.png

Figure 4: Registry tree where the encryption protocols can be configured.

Below each of these Client and Server nodes are anywhere from zero to two DWORD entries, as shown below:

https://7mgoza-dm2305.files.1drv.com/y4mpGQdqiqVf8q1TSphvaASgn4SpuKQrQsTL4i0VjpIJc9gzvE5J1AZ3QGEJc7zE6R9b0PoxeW1mty_4D_Luqp3hOB__3JhHJP0JneJYUc4iJ5Elo_ZumxGcb1XJYy8bKj6PsXAqPvsadIb2oeT_AWC2jmRJkhuoR3Tk3IXy55ckFZJWzbARndNAzWcItYAvXQM-mitciSR3zsNi1bbNx97tw?width=395&height=71&cropmode=none

Figure 5: Example of an enabled protocol.

4.5 Making the SSL and TLS changes

Rather than worrying about which protocol keys only need none, one or both of these values, it's easier to work on the premise that both values will be created, in which case the table below illustrates the values required to fully enable TLS 1.1 and 1.2, enable TLS 1.0 on the server side (remember the OLEDB caveat above?) but disable it on the client, and fully disable the rest.

SSL 2.0 is included for completeness, however, in all recent platforms it's disabled by default - and rightly so!

Protocol	Server		Client
DisabledByDefault	Enabled	DisabledByDefault	Enabled
SSL 2.0	1	0	1	0
SSL 3.0	1	0	1	0
TLS 1.0	0	1	1	0
TLS 1.1	0	1	0	1
TLS 1.2	0	1	0	1

Table 3: An example configuration of the encryption protocols.

Once you're happy with your configuration, you'll need to restart the server/client for those changes to take effect.

↑ Back to top

5. Encrypt traffic using TLS

This section covers the additional changes required to allow (or require) SQL Server to use encryption. It provides GUI and command line instructions to cater for Server Core configurations.

Note the registry paths in the following examples are taken from SQL Server 2012.

5.1 Configure SQL Server to listen for encrypted connections

Using the GUI tools

1. Run the SQL Server Configuration Manager MMC as an administrator. If you don't see the shortcut for it in the Start menu, you can launch a blank MMC and simply add the snap-in.
2. Expand the "SQL Server Network Configuration" node.
3. Right-click on the "Protocols for <instance name>" node and choose Properties.
4. Select the Certificate tab.
5. Select the appropriate certificate from the drop down list. Setting the certificate isn't strictly required as SQL Server will auto-generate a self-signed certificate if one isn't specified here. However, using the self-signed approach presents increased risk through the client not trusting the certificate.
6. The Flags tab contains the optional settings for forcing encryption. Unless you're entirely confident that all of your client applications/sessions support TLS encryption then it would be prudent to leave this set to "no". It's a simple matter to revisit the setting and enforce compliance at a later date.
7. Once the settings are configured to your specification, restart the SQL Server instance service to apply the changes.

Using the command line

1. Launch PowerShell as an administrator.

2. Run the following and copy the thumprint of the appropriate certificate.

   Get-ChildItem -Path Cert:\LocalMachine\My\ | ft Thumbprint,Subject,NotBefore,NotAfter
   

3. Run regedit.

4. Navigate to HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.<instance name>\MSSQLServer\SuperSocketNetLib

5. Create/modify the REG_SZ setting named "Certificate" and paste the thumbprint value from above into it (as shown below).

6. Create/modify the REG_DWORD setting named "ForceEncryption" and set it as follows: 0 = do not force encryption; 1 = force encryption.

7. Once the settings are configured to your specification, restart the SQL Server instance service to apply the changes.

https://hz1ykg-dm2305.files.1drv.com/y4m1aZBsSL0Ic_u9ch33aOru4-8MdGIyRdhZHGXGiJodjDEIxovn0duei_JqBIgKNGiGsFvocBrTmjECZcvNv6wWHdHvZ8C1vj2AGo-t2cLhR9pldWh6iYaSCveXOxomD7y68aiqvsnQMZjs1L2EMujrKD7X-0_4o0s0Gdbo2U2CNkcPXQtuY45cKcSPcAKr6dXBqa7azCLFUnyOJFRUI5ZDQ?width=1081&height=404&cropmode=none

Figure 6: Registry location for SQL Server certificate settings.

Note: Make sure the SQL Server database service account has READ access to the certificate's private key. If it doesn't, the SQL Server database service will fail to start.

5.2 Optional: Ensuring the SQL Server database service account has READ access to the certificate's private key

Using the GUI tools

1. Launch a blank MMC console as an administrator.
2. Add the "Certificates" snap-in, focused on the "Computer" context.
3. Navigate to the "Certificates\Personal\Certificates" node.
4. Right-click the appropriate certificate in the right-hand pane and choose "All Tasks" followed by "Manage Private Keys".
5. Add the SQL Server database service account with READ access. Do not add "FULL CONTROL" as this isn't required and will only weaken your overall security posture.

As the .NET Framework X509Certificate2.PrivateKey property only works with RSA and DSA keys, two command line methods are presented - the second being for ECDSA keys. More information on the .NET limitation can be found here.

Using the command line (for RSA and DSA keys)

1. Launch PowerShell as an administrator.

2. Run the following and copy the thumprint of the appropriate certificate.

   Get-ChildItem -Path Cert:\LocalMachine\My\ | ft Thumbprint,Subject,NotBefore,NotAfter
   

3. Run the following block of commands:

   $cert = Get-Item -Path Cert:\LocalMachine\My\<thumbprint>$keyPath = (Get-ChildItem -Path C:\ProgramData\Microsoft\Crypto\ -Filter ($cert.PrivateKey.CspKeyContainerInfo.KeyContainerName) -Recurse).FullNameicacls $keyPath /grant "domain\account:(RX)"
   

4. Confirm the change by running:

   $cert.PrivateKey.CspKeyContainerInfo.CryptoKeySecurity.Access
   

Using the command line (for ECDSA keys)

1. Launch PowerShell as an administrator.

2. Run the following and copy the thumprint of the appropriate certificate.

   Get-ChildItem -Path Cert:\LocalMachine\My\ | ft Thumbprint,Subject,NotBefore,NotAfter
   

3. Run the following block of commands:

   $cert = Get-Item -Path Cert:\LocalMachine\My\<thumbprint>$filename = [System.Security.Cryptography.X509Certificates.ECDsaCertificateExtensions]::GetECDsaPrivateKey($cert).Key.UniqueName$keyPath = (Get-ChildItem -Path C:\ProgramData\Microsoft\Crypto\ -Filter ($filename) -Recurse).FullNameicacls $keyPath /grant "domain\account:(RX)"
   

4. Confirm the change by running:

   icacls $keyPath
   

5.3 SQL client configuration

Unlike the server-side configuration above, the client can be configured in multiple places - as discussed here. That said, the server-side configuration does help dictate the final configuration used by the client. In priority order from highest to lowest, the winning decision comes from:

Priority	Location	Affects
1	SQL Server	All client applications on all client hosts that talk to that SQL Server instance.
2	Client SNAC	All client applications on the client host where SNAC has been configured.
3	Application	The specific application on the specific client to which the setting is related. Within this broad location category, it's not uncommon to find these settings in locations such as .config files or the registry, however, it's important to understand that an application may provide no means of customization at all.

Table 4: Points of configuration for over-the-wire data encryption.

Client SQL Server Native Client (SNAC)

If you do not have the SQL Server Native Client installed then this section may not be relevant to your application.

1. Run regedit.exe as an administrator.
2. Navigate to [HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\SNI11.0\GeneralFlags].
3. Two keys should exist below this: Flag1 and Flag2. Beneath each of these in turn is a Label and Value pair of items.
4. Flag1 relates to enabling protocol encryption. Setting the child Value to 1 will force encryption for all applications and services on the host that use SNAC providers.
5. Flag2 relates to whether or not the client validates the server certificate. Setting the child Value to 0 will force a normal certificate validation process to occur, which includes validating the certificate chain and CRL status. Setting the value to 1 will direct the client to trust self-signed and other untrustworthy certificates, which may be useful for non-production testing purposes.

https://hz0bpg-dm2305.files.1drv.com/y4mpzAeJLBkSWTka1yJcPSgjosKdwSjqFPxyi2ScvFGTDIa6PZp9WWOhH8a8BNsgBxDR-zjMqewqpEXHJtqb-SLuptLtpPhfD0GoLx5Q__AwnadP-jeUvznuK3j-uHGLORPml-oPY2fE7YQ7pGPSpSwjcsBaa1nYWCpA1Dyrjz3xjCe-OsyVSRDB9DrDgeoFIH2DjC8N87U5qSSSWCOzXQ3NQ?width=678&height=149&cropmode=none

Figure 7: Registry location for SQL Server Native Client (SNAC) configuration of forced encryption (currently not enforced on account of Value=0).

Application

The configuration of an application can be somewhat harder to navigate. First of all, the application may not even allow configuration if the connection string is largely contained in the application code. For example, it may read only the remote SQL Server hostname from the registry or file but contain important elements such as the provider and encryption options within the code itself, meaning there's no avenue for customisation.

Conversely, more flexible applications may let you configure the entire connection string, meaning you'll have complete access to control encryption settings as you see fit.

Because of this variety, it's not possible to provide exact guidance on a per application, or per Windows feature basis. That being said, there is a reference table below to help you secure what Windows features lend themselves to over-the-wire encryption.

In short though, if you do find you have access to the connection string then what you're looking to include is the "Encrypt=yes" option. An example connection string taken from AD FS is below:

<policyStore connectionString="Data Source=sqlhost.mydomain.com;Initial Catalog=AdfsConfiguration;Integrated Security=True;Min Pool Size=20;``Encrypt=true``" administrationUrl="net.tcp://localhost:1500/policy" />

 

↑ Back to top

6. Confirming the changes have (or haven't) worked

If you're not seeing the results you expected from the [encrypt_option] column in the T-SQL query results from section 2, it's possibly time to break out the network tracing tool and an SQL client of some sort (which could just be the application you're trying to troubleshoot). As mentioned in the Tools section, this article will use Microsoft Message Analyzer 1.4 for the network trace and SQL Management Studio for the SQL client but you can clearly use whichever tools you're comfortable with.

For context, these steps were generated with SQL Server 2012 SP3 running on Windows Server 2012 R2 as the back end, and Windows 10 with the above tools on the client. You might experience some minor variations depending on your platform and tool choice.

1. Launch SQL Server Management Studio or any other tool/application you might want to trace the SQL traffic from.

2. Launch Message Analyzer with administrative priviliges.

3. Click the "New Session" button.

4. Click the "Live Trace" button.

5. Click the "Select Scenario" and choose the "Local Network Interfaces" option.

6. While it's not labelled, below the library button is the message filter textbox. Type the following value into it:

   TDS || TLS
   

7. If you want to add additional criteria, go for it (i.e. to filter on server IP or filter out noisy TLS consumers such as the RDP protocol). Click the Start button once you're happy with the filter. There can be a modest delay before results start appearing on the screen and don't worry too much if you see some module loading errors.

8. Switch to SQL Server Management Studio.

9. Bring up the "Connect to database engine" dialog window, then:

10. Enter the SQL Server hostname.

11. Select the "Options" button and ensure that the "Encrypt connection" checkbox is clear. This will provide the initial unencrypted point of reference.

12. Click the "Connect" button to connect to the SQL Server.

13. Open a new query window and enter something basic like:

    SELECT * FROM sys.servers
    

14. Run your query.

15. Switch back to Message Analyzer and stop the trace.

16. If there's too many TDS rows to comfortably browse, you can apply the following filter to focus on the client-side queries:

    TDS.PacketType == 1
    

17. Look for a TDS SQLBatchOperation row as shown in the example below:

    https://7mhzvw-dm2305.files.1drv.com/y4m8N5nNEVmyxEJBuwWvQLijsEM7VUiEx6nbpAnOupRsOO2n8aDU5RQlLJxBSPIWMSlf_-MgC8zGoDUwDOXPVXYg_4U_MGRj4_hwYMKI0QhZKeXi1Z9pLbey7j5T4H_BYJXSg-MD81HfTLLUc2mZbtF2WEgJxbicaMP5OQNbetiQSjC7Jhr5xP6iPfyUmkuR8PKvShYhHeXjZA6HrewX3AvQw?width=1294&height=56&cropmode=none

    Figure 8: Network capture row without encryption.

    https://7mhetq-dm2305.files.1drv.com/y4mSpDA2ffX3BWEu0rocGR-isgHF_gvbNrxEi-RQ8eS4CIEtlFbY_IdH3JBs4xnkRfjmdj-J6k4cPiHq2dKiLImiYFakxAJBI4uHWM9wggHt9QjFZc8a2XouLCeVyPBbMCFZGVvwObiD52xc8kvqIj4n52ltQcUAUiS0hEMeibcgBJS4DaOdv7TyJZLt5RfqnydHQR7EpusrpEgwdP7RTICDA?width=457&height=270&cropmode=none

    Figure 9: Network capture detail without encryption.

18. Close the query window and disconnect from the server (as an existing connection in Management Studio will retain it's original encryption setting).

19. Re-connect to the server as per step 10, however, this time make sure that the "Encrypt connection" checkbox is checked.

20. Open a new query window and paste in/re-type the same query as before.

21. Switch back to Message Analyzer and re-start the trace (the green-coloured "play" icon).

22. Switch back to SQL Management Studio and run the query.

23. Switch back to Message Analyzer and stop the trace.

24. What you should find this time is that only TLS traffic exists between the client and SQL Server. An example of the rows within Message Analyzer can be found below (note the "payload" is now encrypted):

    https://7mipqa-dm2305.files.1drv.com/y4mZn9_a3SnlVVa3QEG-yBrPNPl6ZtvAg6s9pbvH8dGVGLuNYo-zv0FIh6Sjt5pFbT6ZxrYqLI2tfTUYM_9Y3Vs25oO0_oJTEBy9Kqa-UnfU_7HCI1AA-NMVPyF-nIBb8pxBKIK8AJWUlb9_BuXHU1meRcz_8A-hiaFjs8Ovi4gaezfrw5lmMpmGJC2K5uUrc1oSt6NULYY3N1Rv6brz5gcZg?width=543&height=251&cropmode=none

    Figure 10: Network capture detail with encryption.

7. Windows feature/application reference

This section is a work in progress and will be updated as new configuration options are discovered.

Windows feature/application	Operating system	TLS limit	Configuration point
Forefront Identity Manager 2010 R2	Windows Server 2012 R2	1.0	Client host
As per subsection Client SQL Server Native Client (SNAC) in section 5.3.
Active Directory Federation Services (AD FS)	Windows Server 2012 R2	1.2	Application
Modify %windir%\ADFS\Microsoft.IdentityServer.Servicehost.exe.config Look for the line beginning with <policyStore connectionString= and add the string Encrypt=yes;
Active Directory Rights Management Server (AD RMS)	Windows Server 2012 R2	1.2	Application
Modify registry [HKLM\SOFTWARE\Microsoft\DRMS\ConnectionString\ConfigDatabaseConnectionString] Edit the value to include Encrypt=yes;
Azure AD Connect v1.0.8667.0	Windows Server 2012 R2	1.0	Client host
As per subsection Client SQL Server Native Client (SNAC) in section 5.3.
Network Policy Server	Windows Server 2012 R2	1.0	Application
When configuring SQL logging, goto the All tab and set "Use encryption for data" to "true".
Windows Server Update Services (WSUS)	Windows Server 2012 R2	1.2	Server
As per section 5.1.

 

↑ Back to top

References

1	Kerberos Authentiction Overview
2	How to make sure that you are using Kerberos authentication when you create a remote connection to an instance of SQL Server 2005
3	Using Encryption Without Validation
4	Encrypting Connections to SQL Server
5	Guidance on the Deprecation of Internal Server  Names and Reserved IP Addresses provided by the  CA/Browser Forum
6	Deprecations and Removals in Chrome 58