Configuring network encryption for SQL Server.

[ACALVETT]

Introduction

SQL Server uses the TLS (Transport Layer Security) cryptographic protocol to encrypt the data across a network between the SQL Server instance and the client applications. If you decide to encrypt your SQL Server connections, you will need to obtain a certificate that meets specific conditions. Many conditions are available, so we won’t list them all here, but we will highlight a few of the most important ones along the way.

To explain how TLS will help SQL Server communications be more secure, we’ll refer to Microsoft’s documentation:

• Encryption and Certificate validation with SQL Server
• SSL/TLS Configuration Overview with SQL Server
• Certificate requirements for SQL Server
• CSS SQL Networking Tools

TLS can be used for server validation when a client connection requests encryption. If the instance of SQL Server is running on a computer that has been assigned a certificate from a public certification authority, identity of the computer and the instance of SQL Server is vouched for by the chain of certificates that lead to the trusted root authority. Such server validation requires that the computer on which the client application is running be configured to trust the root authority of the certificate that is used by the server.

Modern clients are now being updated to be secure by default which means that if we do not provision certificates for our SQL Servers you may encounter an error "The target principal name is incorrect" or "The certificate chain was issued by an authority that is not trusted" or "The received certificate has expired". Examples of such updates are:

• Beginning in .NET Framework 4.5 the sqlClient was updated so that, by default TrustServerCertificate is false and Encrypt is true, these settings require a SQL Server certificate.
• Azure Data Studio has the same issue (but not SSMS for now) and the team has written about it here: Azure Data Studio

dbaTools commands

dbaTools provides the following commands to manage the certificate process which we will walk through.

• New-DbaComputerCertificate
• New-DbaComputerCertificateRequest (if infra doesn't allow for New-DbaComputerCertificate)
• Add-DbaComputerCertificate
• Set-DbaNetworkCertificate
• Enable-DbaForceNetworkEncryption
• Test-DbaCertificateExpiration

Provisioning a new certificate

Option A

If your SQL Server is part of an Active Directory domain with its own certificate authority, you can use the command New-DbaComputerCertificate to generate a computer certificate that is appropriate for SQL Server’s use, as shown in the next listing.

Note in both case when populating the DNS list you should provide all DNS aliases, for example with AlwaysOn you must include the listener.

$splatCert = @{
  ComputerName = "sqlVM1"
  Dns = "sqlVM1.mywork.local", "sqlVM1"
}
New-DbaComputerCertificate $splatCert

ComputerName : SQLVM1
Store        : LocalMachine
Folder       : My
Name         : SQL Server
DnsNameList  : {sqlVM1.mywork.local, sqlVM1}
Thumbprint   : C2C04E463D5699C7CC1A30087445A7C8CAD44842
NotBefore    : 1/9/2023 12:00:00 AM
NotAfter     : 1/9/2025 12:00:00 AM
Subject      : CN=sqlVM1.mywork.local
Issuer       : CN=ad-DC1-CA, DC=mywork, DC=local
Algorithm    : sha256RSA

This will both create and install your certificate in the appropriate certificate store. For this command to work, your user account must have permission within Active Directory to create new certificates.

Option B

If you do not have permission to create a certificate due to company policy then dbatools can generate a certificate signing request (CSR) for you to give to your PKI administrator for approval. Generating a CSR is also useful when your certificate authority is an external third party such as DigiCert.

$splatCSR = @{
    ComputerName = "sqlVM1"
    Dns = "sqlVM1.mywork.local", "sqlVM1"
    keylength = 4098
}
 New-DbaComputerCertificateSigningRequest $splatCSR

Directory: C:\Users\dba\Documents\DbatoolsExport\sqlVM1.mywork.local
 
Mode                LastWriteTime         Length Name
----                -------------         ------ ----
-a----         1/9/2023  12:00 AM            529 request.inf
-a----         1/9/2023  12:00 AM           1018 sqlvm1.mywork.local.csr

Installing the certificate

Once you receive a certificate back, follow the instructions provided by the certificate administrator to finish importing your certificate to the Local Machine\My certificate store. A possible option available is to use the following command.

Add-DbaComputerCertificate -Path C:\temp\cert.cer

Next we need to assign the certificate to the SQL Instance.

Get-DbaComputerCertificate | Out-GridView -PassThru |
    Set-DbaNetworkCertificate -SqlInstance sql1

Set-DbaNetworkCertificate not only sets the specified certificate, it also goes a step further and adds permissions for the SQL Servers service SID to read the certificate’s private key otherwise SQL Server will not start. Note we add the service SID because if you ever change the service account manually you will not lose access to the certificate. See Service sids.

Forcing encryption

At this point configuration is complete and clients can specify encryption in their connections but you may want to force all client/server communication to be encrypted, and clients that cannot support encryption (e.g., legacy drivers) are denied access. To achieve this, we need to set the ForceEncryption option to true.

Enable-DbaForceNetworkEncryption -SqlInstance sql1

Once your SQL Server instance has been restarted, your new settings will be in effect and your connections will be encrypted. Although all encryption has a performance impact, network encryption’s impact is negligible, and you will likely not see any difference.

Managing certificates

Once you have installed your certificates you still need to manage them as eventually they will expire. For this purpose we provide Test-DbaComputerCertificateExpiration

 Test-DbaComputerCertificateExpiration 
 

Bringing it all together

So the complete script to install a network certificate is below based on requesting a certificate.

$splatCSR = @{
    ComputerName = "sql1"
    Dns = "sql1.ad.local", "sql1"
    keylength = 4098
}
 New-DbaComputerCertificateSigningRequest $splatCSR
##Now give csr to admin and once receive .cer continue.
Add-DbaComputerCertificate -Path C:\temp\cert.cer
Get-DbaComputerCertificate | Out-GridView -PassThru |
    Set-DbaNetworkCertificate -SqlInstance sql1
Enable-DbaForceNetworkEncryption -SqlInstance sql1

[potatoqualitee]

Thank you, @ACALVETT! Your post has been pinned to our Discussions 🙌🏼

[mikeblas]

The certreq link is 404.

[wsmelton]

@ACALVETT not sure what link was intended. Updated the links and added a few more on the topic.