Step-by-Step Guide to Enabling SQL Server Encryption: Certificate Authority Configuration

Edition: Saturday, April 6th, 2024

In the last edition, we concluded after installing Active Directory Certificate Services.  

Today, we'll configure the new Certificate Authority as an Enterprise CA.  We're installing an Enterprise CA because they're integrated with Active Directory Domain Services and automatically added to the trusted root store of each server in the domain.

If you're following along in your sandbox (I hope you are), create a quick snapshot of your virtual machines.

Let's jump in.

AD Certificate Services Configuration

Open Server Manager and click on the flag in the upper right corner.

Next, select Configure Active Directory Certificate Services.

This will open the AD CS Configuration wizard.

Click next and then select both "Certificate Authority" and "Certificate Authority Web Enrollment."

Verify "Enterprise CA" is selected and click next.

On the next screen, select "Root CA."  This is our first CA in the domain.

Click next.  Select "Create a new private key" and select Next.  You can adjust the options on this screen but I went with the defaults using SHA256 for the hash algorithm and 2048 for the key length.

Next, make sure the common name is correct.  I went with the defaults here.  

Next, select a validity period.  I went with 5 years.

On the next screen, select the location for the certificate database.  I left the defaults as we're working in a sandbox.  If this was production you may choose a different location other than C and ensure it's backed up.

On the confirmation page, click Configure.

After a second or two, you should see the page below.  Success!

Certificate Authority Tool

Let's look at the Certificate Authority tool.

Jump back into the Server Manager, click Tools on the menu bar, and select Certificate Authority.

It's empty right now, but get familiar with each folder and look at the properties of the CA.  As a DBA, you won't spend much time here but it's advisable to understand the use of each folder at least at a high level.

Web Enrollment

We've installed the Web Enrollment role and you'll notice Internet Information Services was installed too.  However, we're not quite ready to use it yet.  Go ahead and open it, in my example it's HTTP://CA1.HOMELAB.LOCAL/certSrv.  Next week we'll complete the following tasks:

• Generate a certificate that can be used for Server Authentication.
• Reconfigure the CertSrv site to use SSL and the certificate created above.
• Open the site again, using HTTPS, and ensure the certificate is trusted.
  • FYI, I encountered an issue when working with this site using Microsoft Edge and Chrome.  The site needs to be opened using Internet Explorer mode and we'll cover that next week as well.

Conclusion:

Join me next week as we'll finish up the configuration of the CA.  Take this week and look around and feel free to proceed with configuring HTTPS for your enrollment site.  If you run into issues, no worries, revert to your snapshot and give it another try.

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