Most Microsoft-based Hybrid Identity implementations use Active Directory Federation Services (AD FS) Servers, Web Application Proxies and Azure AD Connect installations. In this series, labeled Hardening Hybrid Identity, we’re looking at hardening these implementations, using recommended practices.

Implementing AD FS with SQL Server provides access to Artifact Resolution and Replay Detection. Implementing Azure AD Connect with SQL Server removes the SQL Server Express Edition scalability limit.

While these features add to the overall information security level of the organization, you might want to consider the insider risk associated with this setup. Therefore, in this blogpost, we’re looking at encrypting the traffic between the AD FS Servers and servers running Azure AD Connect on one side and the SQL Server(s) hosting the AD FS databases and Azure AD Connect database(s) on the other side.

Note:
This blogpost assumes you use Microsoft SQL Server 2019 for hosting the AD FS databases and Azure AD Connect database(s).

Why encrypt traffic to SQL Servers?

As you may have noticed, it is a recommended practice to use Windows Authentication from the AD FS server(s) and servers running Azure AD Connect to gain access to the AD FS Databases on the SQL Server(s). This makes sense as AD FS Servers need to be domain-joined. Azure AD Connect work best when domain-joined. Also, for SQL Server Always-on Availability Groups, SQL Server needs to run on a Windows Server Failover Cluster. This works best with Active Directory.

When using Windows Authentication, Kerberos is used to authenticate. This protects the authentication and authorization data from replays, etc. However, by default, SQL Server will then clear-text send and receive data.

For a malicious insider it’s trivial to find domain-joined SQL Servers on the network, by scanning SPNs. A malicious insider might find information in the traffic stream between the application servers and the SQL Server that prove interesting and may feed further compromise. This is especially true for AD FS servers communicating to SQL Servers and servers running Azure AD Connect communicating to SQL Servers.

What could go wrong?

When encryption is used, more CPU will be consumed on both ends of the connection. Encrypting traffic between AD FS Servers, servers running Azure AD Connect and SQL Servers hosting their  databases will result in a higher load on AD FS servers, servers running Azure AD Connect and SQL Servers.

Improper capacity planning might lead to degraded performance, although it doesn’t have to be that way when TLS offloading is available on the Network Interface Card (NIC) level.

Note:
For virtual servers running with multiple vCPUs on VMware vSphere, looking into Receive Side Scaling might prove useful.

Getting ready

SQL Encryption leverages Transport Layer Security (TLS). Therefore, the first thing we need is a TLS certificate. You can issue a self-signed certificate on the SQL Server, but this is not a recommended practice.

The recommended way is to use a certificate that is issued by your organization’s public key infrastructure (PKI). The certificate must meet the following prerequisites:

1. The current system time must be after the Valid from property of the certificate and before the Valid to property of the certificate.
2. The certificate must be meant for server authentication. This requires the Enhanced Key Usage property of the certificate to specify Server Authentication (1.3.6.1.5.5.7.3.1).
3. The certificate must be created by using the KeySpec option of AT_KEYEXCHANGE. Usually, the certificate's key usage property (KEY_USAGE) will also include key encipherment (CERT_KEY_ENCIPHERMENT_KEY_USAGE).
4. The Subject property of the certificate must indicate that the common name (CN) is the same as:
   1. The fully qualified domain name (FQDN) of the host running SQL Server, or
   2. The fully qualified domain name (FQDN) of the virtual server, if SQL Server is running on a failover cluster.

How to encrypt traffic

To encrypt traffic between AD FS Servers, servers running Azure AD Connect and SQL Servers hosting the AD FS and Azure AD Connect databases, perform these steps:

Install the certificate on SQL Server hosts

The certificate must be in either the local computer certificate store or the current user certificate store. the certificates must be provisioned on all nodes in the failover cluster.

Use the following lines of Windows PowerShell to import the certificate into the local machine certificate store on (each of) the Windows Server installation(s) running SQL Server hosting the AD FS amd Azure AD Connect databases:

$CertFile = "C:\SQL.pfx"

$CertPassword = "Your Password Here"

Import-PfxCertificate -FilePath $CertFile -Password (ConvertTo-SecureString -String $CertPassword –AsPlainText -Force) -CertStoreLocation cert:\LocalMachine\My

Remove-Item -Path $CertFile

Make sure the service account has the right permissions

The SQL Server Service Account must have the necessary permission to access the TLS certificate. Use the following lines of Windows PowerShell to provide read access for the private key of the certificate to the SQL Server service account.

$CertSubject = "sqlserver.domain.tld"

$SQLServiceAccount = "DOMAIN\SA-SQL"

$Cert = Get-ChildItem -path cert:\LocalMachine\My | Where-Object {$_.Subject –match $CertSubject}

$path = "C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\$($cert.PrivateKey.CspKeyContainerInfo.UniqueKeyContainerName)"

$acl = Get-Acl –path $path

$rule = New-Object security.accesscontrol.filesystemaccessrule $SQLServiceAccount, "Read", allow

$acl.AddAccessRule($rule)

Set-Acl –path $path -AclObject $acl

Configure SQL Server to force encrypted connections

To configure the SQL Server to force encrypted connections, follow these steps:

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

Concluding

Most of the blogposts in this Hardening Hybrid Identity series cover protections , preventing malicious people from gaining access from outside of the network to the network. This particular protection prevents eavesdropping network traffic when a malicious person is already on the network.