Step-by-Step Guide to Enabling SQL Server Encryption: Installing Active Directory Certificate Services for DBAs

Edition: Saturday, March 30th, 2024

Most new DBAs aren't familiar with certificates, or generating certificate requests, to be used by SQL Server. 

I know I wasn't.  Early in my career, I had worked in environments that didn't require encrypted connections to SQL Server so I struggled a bit the first time.

It took several certificate requests to get it right. 

The process consists of:

• Identify certificate requirements for SQL Server TLS encryption.
• Creating a certificate request.
• Generating the certificate.
• Install the certificate.
• Configure SQL Server to use the certificate and enable encryption.
• Test and verify connections are encrypted.

Seeing the complete process, including generating the certificate, is helpful.  However, most DBAs won't have access to generate the certificate themselves and usually need to submit the request to a domain admin.  

Today, I'll show you how to install Active Directory Certificate Services.  Throughout April we'll build our environment so that by the end you can complete all steps required to enable encryption using a trusted certificate.  I encourage you to follow along in your sandbox.

Here's what you'll need (your server names will vary):

• DC1 - Windows Server 2022 VM serving as your domain controller.  If you're unfamiliar with how to set up a DC, check out this post.  There's a demo video as well.
• CA1 - One Windows Server 2022 VM joined to the domain.  We'll install ADCS here.  Configure this server to use a static IP address.
• SRV2 - One Windows Server 2022 VM with SQL Server 2022 Developer edition installed.  Join this server to your domain.
• SRV3 - One Windows Server 2022 VM.  We'll use this one to install SQL Server Management Studio and Wireshark.  Join this server to your domain as well.

Let's dive in.

What is Active Directory Certificate Services?

ADCS is a Microsoft technology that provides public key infrastructure (PKI) functionality to help secure applications, services, and users within an enterprise network. It enables the creation, management, and distribution of digital certificates.  These certificates are used for a variety of security protocols and features, such as TLS encryption, secure email (S/MIME), VPN access, and smart card authentication.  Our primary focus will be on TLS encryption.

Core Functions:

• Certificate Authority (CA): Acts as the trusted entity that issues and manages digital certificates.  CAs can be arranged in a hierarchy, including root CAs and subordinate CAs, to provide a chain of trust.
• Certificate Enrollment: Facilitates the process by which users, computers, and services request and receive certificates from a CA.
• Certificate Revocation Lists (CRLs): Publishes a list of certificates that have been revoked before their scheduled expiration date, indicating they should no longer be trusted.
• Certificate Templates: Pre-defined settings that determine the purpose and properties of a certificate, such as validity period, usage constraints, and renewal policies.
• Online Responders: Respond to certificate status queries (e.g., checking if a certificate is valid or has been revoked) as part of the Online Certificate Status Protocol (OCSP), providing an efficient alternative to CRLs.

Installing ADCS:

Step 1: Assign static IP address

Configure the virtual machine with a static IP address.

Step 2: Join the server to the domain.

Before installing ADCS, the server must be joined to the domain.  For example, my sandbox domain is HOMELAB.LOCAL and the server where I'm installing ADCS is named CA1.

Step 3: Create VM snapshots.

This is an optional step.  I usually create a VM snapshot of the domain controller and the server I'm building as the CA.  This makes it easy to perform the steps multiple times or recover easily if things don't go as planned.  If you're using Vagrant to manage your sandbox, this is as easy as running "vagrant snapshot save DC1 SnapshotName" and "vagrant snapshot save CA1 SnapshotName."

Step 4: Log on using a domain admin account.

Step 5: Installing ADCS

You have two options.  Either use Server Manager to install ADCS, along with the Certificate Authority and Certification Authority Web Enrollment services, manually or download and modify the ADCS_DeploymentConfigTemplate.xml file. 

Search for "CA1" in the file and replace it with your computer name. 

To see the manual steps, check out Install the Certification Authority | Microsoft Learn.  I don't want to bore you with click next, next, finish instructions.

After you've updated the config template and saved it on your VM, open PowerShell as an admin, and run the following.  

• install-windowsfeature -ConfigurationFilePath C:\PathToTemplateFile\ADCS_DeploymentConfigTemplate.xml

When complete, you should see the output below stating the installation was successful.

Step 6: Open Server Manager and verify Certificate Authority is listed under tools.

FYI, if you click Certificate Authority now you'll receive an error.  The CA role still needs to be configured.

You'll also notice a warning icon next to the flag.  This indicates ADCS is ready to be configured. 

Conclusion:

Installing Active Directory Certificate Services (ADCS) may not be a routine task for you as a Database Administrator (DBA). However, acquiring the ability to interact with a certificate authority and gaining experience in submitting certificate requests can enhance your skill set.  This hands-on experience equips you with the knowledge to effectively communicate with those who manage these systems directly.

Next week we'll configure the Certificate Authority and Certificate Authority Web Enrollment services.

That's it for today.  Have a great week!