Enable TLS in Microsoft SQL Server 2022

In a recent post, I described some of the security benefits of using Transport Layer Security (TLS) with Microsoft SQL Server. Configuration changes are required to take full advantage of these capabilities. By default, SQL Server uses an unmanaged, self-signed certificate, which provides little security value. The best practice is to use a certificate issued by the organization’s enterprise PKI. In this guide, I’ll demonstrate how to prepare and deploy a certificate template for SQL server using Active Directory Certificate Services (AD CS), enroll for the certificate, and configure SQL server to use the new certificate for TLS connections.

Note: I have recorded a video demonstration for enabling TLS in Microsoft SQL Server 2022 on my YouTube channel here. Enjoy!

Certificate Requirements

The minimum recommended requirements for a TLS certificate for SQL Server 2022 are:

  • Subject Name = Server’s fully qualified domain name or the alias name of the cluster
  • 2048-bit RSA key with SHA256
  • Server Authentication EKU (1.3.6.1.5.5.7.3.1)

Certificate Template

Administrators must prepare a certificate template in Active Directory (AD) adhering to the requirements listed above. On an issuing certification authority (CA) or an administrative workstation with the Remote Server Administration Tools (RSAT) installed, open the Certificate Templates management console (certtmpl.msc) and perform the following steps.

  1. Right-click the default Web Server template and choose Duplicate Template.
  2. Select the Compatibility tab.
    1. In the Compatibility Settings section, select the latest version of Windows Server supported by your issuing CA servers from the Certification Authority drop-down list.
    1. Select Windows 10/Windows Server 2016 from the Certificate recipient drop-down list.
  3. Select the General tab.
    1. Enter a descriptive name in the Template display name field.
    1. Select a validity period of 1 year with a renewal period of 6 weeks.
  4. Select the Cryptography tab.
    1. Select Key Storage Provider from the Provider Category drop-down list.
    1. Select RSA from the Algorithm name drop-down list.
    1. Enter 2048 in the Minimum key size field.
    1. Select SHA256 from the Request hash drop-down list.
  5. Select the Issuance Requirements tab.
    1. Check the box next to CA certificate manager approval.
  6. Select the Subject Name tab.
    1. Select Supply in the request.
  7. Select the Extensions tab.
    1. Select Application Policies.
    1. Ensure that Server Authentication is the only application policy listed.
  8. Select the Security tab.
    1. Click Add.
    1. Grant Read and Enroll permissions to the SQL Server security group or the SQL server’s computer account.
    1. Ensure no other users/groups have enroll permission.

Once complete, publish the certificate template on all issuing CA servers in the organization.

Enroll Certificate

The certificate enrollment process involves several steps.

Request Certificate

To enroll for a new TLS certificate, open the computer certificate management console (certlm.msc) on the SQL server and perform the following steps.

  1. Right-click on the Personal folder and choose All Tasks > Request New Certificate.
  2. Click Next.
  3. Click Next.
  4. Check the box next to the SQL server certificate template.
  5. Click the More information is required to enroll for this certificate. Click here to configure settings link.
  6. Select the Subject tab.
  7. In the Subject Name section, select Common Name from the Type drop-down list.
  8. Enter the SQL server’s fully qualified domain name (FQDN) or the alias name of the SQL cluster in the Value field.
  9. Click Add.
  10. In the Alternative name section, select DNS from the Type drop-down list.
  11. Enter the SQL server’s fully qualified domain name (FQDN) or the alias name of the SQL cluster in the Value field.
  12. Click Add.
  13. [OPTIONAL] Enter the SQL server’s single-label hostname in the Value field.

Note: Adding the single-label hostname to the Subject Alternative Name list allows administrators or applications to connect to the SQL server using its short name (NetBIOS name) without resulting in a subject name mismatch error.

  1. Click Add.
  2. Click Ok.
  3. Click Enroll. The status should indicate that enrollment is pending.
  4. Click Finish.

Approve Certificate

Once the certificate request is made, the request must now be approved. On an issuing certification authority (CA), or an administrative workstation with the Remote Server Administration Tools (RSAT) installed, open the Certification Authority management console (certsrv.msc) and perform the following steps.

  1. Expand the CA.
  2. Select Pending Requests.
  3. Note the request ID for the pending request. After approval, the request ID will be required later to retrieve the certificate.
  4. Right-click the pending request and choose All Tasks > Issue.

Important Note: I am performing the above tasks in a test lab environment. On a properly configured CA in a production environment, the requestor should not be able to approve their own request. In your environment, you may need to request that a CA administrator review and approve your request.

Install Certificate

Once the certificate has been approved and issued, open an elevated PowerShell or command window on the SQL server and perform the following steps.

  1. Enter certreq.exe -retrieve <request ID>.
  2. Select the CA where the certificate was issued.
  3. Click Ok.
  4. Select a location and enter a name for the file in the File name field.
  5. Click Save.
  6. Enter certreq.exe -accept <path to certificate file>.

Configure Certificate

Once the certificate has been enrolled on the SQL server, expand Personal > Certificates and refresh the view to confirm certificate enrollment. Next, perform the following steps.

  1. Right-click the SQL server certificate and choose All Tasks > Manage Private Keys.
  2. Click Add.
  3. Enter the name of the SQL server domain service account and click Check Names.
  4. If using the default SQL server service account, perform the following steps.
    1. Click on Locations.
      1. Select the local server.
      1. Click Ok.
      1. Enter NT Service\MSSQLSERVER and click Check Names.
  5. Click Ok.
  6. Uncheck Full control. The only permission required is Read.
  7. Click Ok.

SQL Configuration

Next, the new certificate must be assigned to the SQL Server service. Open the SQL Server Configuration Manager (sqlservermanager16.msc) and perform the following steps.

  1. Expand SQL Server Network Configuration.
  2. Right-click Protocols for MSSQLSERVER and choose Properties.
  3. Select the Certificate tab.
    1. Select the new certificate from the Certificate drop-down list.
  4. Select the Flags tab.
    1. Select Yes next to Force Strict Encryption.
  5. Click Ok.

Restart the SQL Server service for the changes to take effect.

Important Note: Selecting Force Strict Encryption will force encryption and certificate validation for all clients connecting to the SQL server. It will override any settings to bypass encryption or certificate checks. Force Strict Encryption may not be compatible with older applications or drivers. Please test thoroughly before enabling this setting.

Video

I’ve published a demonstration video for configuring TLS on Microsoft SQL Server 2022 on YouTube. You can find the video here.

Summary

After completing the configuration steps above, administrators can be assured that all communication between clients and the SQL server is fully protected with TLS using modern cryptography and their enterprise-managed certificate. With TLS enabled for SQL server communication, security is enhanced by encrypting data in transit, ensuring authentication, and protecting sensitive information from interception. In addition, this configuration helps meet compliance requirements.

Additional Information

TLS and Microsoft SQL Server 2022

Always On VPN and SQL Target Principal Name Incorrect

Inbox Accounting Database Management

The Routing and Remote Access Service (RRAS) role in Windows Server is a popular VPN server choice for administrators deploying Windows Always On VPN. It is easy to configure, scales well, and is cost-effective. After installing RRAS, administrators can optionally enable inbox accounting to log historical data and generate user access and activity reports as described in Always On VPN RRAS Monitoring and Reporting.

Inbox Accounting Database

A Windows Internal Database (WID) is automatically installed and configured for data storage when inbox accounting is enabled.

WID is nothing more than a basic instance of Microsoft SQL Server. As such, the database will require periodic maintenance to perform optimally.

Inbox Accounting Database Management Scripts

I have created a series of PowerShell scripts to address the inbox accounting database management requirements for organizations using Windows Server RRAS. Scripts are available to perform the following inbox accounting database management tasks.

  • Optimize the inbox accounting database.
  • View the size of the inbox accounting database files.
  • Compress the size of the inbox accounting database.
  • Back up the inbox accounting database to a file on disk.
  • Restore the inbox accounting database from a backup file.
  • Move the inbox accounting database file to a different location.
  • Remove the inbox accounting database.

Optimize Database

A known issue with the inbox accounting database can result in high CPU and memory utilization for very busy RRAS VPN servers. Specifically, a crucial index is missing from one of the tables in the logging database. This issue persists in Windows Server 2022. To correct this issue, download and run the following PowerShell script on each RRAS VPN server in the organization.

Optimize-InboxAccountingDatabase.ps1

View Database Size

The database can grow rapidly depending on how busy the RRAS server is. Administrators can view the current database file sizes by downloading and running the following PowerShell script on the RRAS server.

Get-InboxAccountingDatabaseSize.ps1

Compress Database

Over time, the database can become fragmented, decreasing performance. Compressing the database can improve performance and result in significant recovery of disk space. To compress the inbox accounting database, download and run the following PowerShell script on each RRAS server in the organization.

Compress-InboxAccountingDatabase.ps1

In this example, compressing the database reduced its size by more than 8MB, resulting in a nearly 70% reduction in disk space usage.

Backup Database

Administrators may wish to back up the inbox accounting database before purging older records from the inbox accounting database. Also, backing up the database preservers access records when migrating to a new server. To back up the inbox accounting database, download and run the following PowerShell script on each RRAS server in the organization.

Backup-InboxAccountingDatabase.ps1

Restore Database

Naturally, to restore the inbox accounting database from a previous backup, administrators can download and run the following PowerShell script.

Restore-InboxAccountingDatabase.ps1

Restoring a database from backup will erase all records in the current database. It does not append. Proceed with caution!

Move Database Files

Inbox accounting database and log files are located in C:\Windows\DirectAccess\Db by default.

However, storing database and log files on the system drive is not ideal. A better alternative is to place the inbox accounting database and log files on a separate disk for optimum performance. To move the inbox accounting database, download and run the following PowerShell script on each VPN server in the organization.

Move-InboxAccountingDatabase.ps1

Moving inbox accounting files may not be formally supported by Microsoft. Use caution when making this change.

Remove Database

Occasionally an inbox accounting database becomes corrupt and can no longer be managed. If this happens, completely removing the database is required. It is essential to know that simply disabling and re-enabling inbox accounting on the VPN server does not delete the database. To delete the database completely, download and run the following PowerShell script.

Remove-InboxAccountingDatabase.ps1

PowerShell Module

To simplify things, the PowerShell scripts described in this article are available in a PowerShell module that can be installed from the PowerShell gallery using the following command.

Install-Module InboxAccountingDatabaseManagement

Additional Information

Windows Always On VPN RRAS Inbox Accounting Database Management PowerShell Module

Windows Always On VPN RRAS Monitoring and Reporting

Windows Always On VPN PowerShell Scripts on GitHub

DirectAccess Inbox Accounting Database Optimization

DirectAccess Inbox Accounting Database OptimizationRecently I wrote about an issue with DirectAccess servers exhibiting high SQL Server CPU usage. In that article I demonstrated a way to resolve the issue by adding a crucial index to a table in the remote access inbox accounting database. The process was a bit involved and required downloading third-party tools to make configuration changes on the DirectAccess server.

Going forward, making these changes will now be much easier. Microsoft has published guidance for optimizing the remote access inbox accounting database using PowerShell. They’ve also provided scripts to back up the database and to confirm that optimization has been implemented.

For more information and to download the remote access inbox accounting database optimization PowerShell scripts, click here.