SQL Server – Fun with certificates

So, you have a PKI or access to certificates and want to encrypt SQL connections. Easy!

NOTE: We are not discussing encrypting the database or anything like that. Just the connection from the application (in this case the application is SQL Server Management Studio (SSMS) to the SQL server.

In my lab environment I have an Enterprise PKI consisting of an offline root and a subordinate issuing CA.

 

Creating the SQL Server certificate

 

In my lab my subordinate CA (or issuing CA) is on my DC. DON’T do that in production! First we need to open the Certificate Authority. This can be found in Administrative Tools.

 

Once the Certificate Authority is open, right click Certificate Templates and select Manage

 

That will open the Certificate Template Console. Navigate to the Web Server certificate template, right click and select Duplicate Template

 

Provide a Template Display Name. You might also want to change the validity period and renewal period, but that is completely up to you and your organizational policies.

In this example I am naming my template SQL Server – TEE-SQL-1. You will not want to use the same cert for all your SQL servers. Thus, you can see I have a naming convention of “SQL Server – ” and then the SQL Server name, which in my case is TEE-SQL-1.

 

The only other step is to go to the Security tab and remove Enroll rights from whatever groups you want. Then add the machine name of the SQL server and give it Read and Enroll rights.

 

All set. Click OK to close and save the template

Close the Certificate Template Console

In the Certificate Authority, right click Certificate Templates and select New > Certificate Template to Issue. NOTE: You will need to wait for Active Directory to replicate the template in order to issue it.

 

Find the template you just created and select OK

 

 

The SQL Server certificate has now been created and issued.

 

 

Install the certificate on the SQL server

 

On the SQL server that the certificate is to be used open an MMC. Select File > Add Remove Snap-In

 

Select Certificates and click Add

 

Select Computer Account and click next

 

Select Local Computer and Finish

 

Click OK to close Add Remove Sanp-ins

Expand Certificates (Local Computer) > Personal

Right click Personal and select All Tasks > Request New Certificate

 

Select Next and select Active Directory Enrollment Policy.

Find the certificate that was created previously and click on More information is required to enroll……

 

In the Subject Name select Common Name

 

Enter the FQDN of the SQL Server and select Add

 

In the Alternative name select DNS

Enter both the FQDN and the Netbios name of the SQL Server. NOTE: If you have availability groups you will want to enter those names too.

 

Select the General tab and enter a friendly name. NOTE: without a friendly name certificates can be hard to find!

Select OK and Enroll

You should now see the certificate under Personal > Certificates

 

But wait…..there’s more! Security…..

If you were to try and configure SQL Server to use the cert at this point, it wouldn’t show up. Unless you are running SQL as local system, in which case you can close this article. You have bigger things to worry about!!

We need to give the SQL Server service account rights to read the certificate.

To find out what account SQL server is running under open SQL Server Configuration Manger and select SQL Server Services

In my case I am running SQL Server under an AD Group Managed Service Account named gmsaSQLServer$

Return to the Certificates MMC and right click the certificate and select properties.

 

Add the account that is running the SQL services and provide it read permissions.

 

You have now enrolled the SQL Server certificate and given the SQL Server service account rights to read it.

 

Using PowerShell to install and configure the certificate

 

That last section had a lot of steps. There must be an easier way!

The following PowerShell script will do the same as above.

 

$UserName
=
“EmptyGarden\gMSASqlServer$”

$permission
=
“read”

 

$Cert
=
Get-Certificate
-Template
SQLServer-TEE-SQL-1
-SubjectName
“CN=TEE-SQL-1.EmptyGarden.btls” `


-CertStoreLocation
Cert:\LocalMachine\My `


-DnsName
“TEE-SQL-1.EmptyGarden.btls”, “TEE-SQL-1”

 

$rule
=
new-object
security.accesscontrol.filesystemaccessrule
$userName, $permission,
allow

 

$root
=
“c:\programdata\microsoft\crypto\rsa\machinekeys”

$keyname
=
$cert.Certificate.PrivateKey.CspKeyContainerInfo.UniqueKeyContainerName

$p
=
[io.path]::combine($root, $keyname)

if ([io.file]::exists($p))

{


$acl
=
get-acl
-path
$p


$acl.addaccessrule($rule)


set-acl
$p
$acl

}

 

$cert
=
$cert.Certificate.FriendlyName =
“SQL Server”

 

Configure SQL Server to use the certificate

 

Open SQL Server Configuration Manager and select SQL Server Network Configuration > Protocols for MSSQLServer.

NOTE: On my SQL Server I have two instances. One is the Default (MSSQLServer) and the other is a Named Instance (CONFIGMGRTP)

Right click Protocols for MSSQLServer and select Properties

 

Select the Certificate tab and find the friendly name of your certificate

 

Select OK. You will be prompted to restart the SQL services. The services need to be restarted for SQL Server to bind to the cert.

 

Once the SQL services are back to running open SSMS

Connect to the NETBIOS name of the SQL server, select Options and select Encrypt connection. Click connect

Run the following query:

SELECT
@@SERVERNAME
AS instance, encrypt_option

FROM
sys.dm_exec_connections

WHERE session_id =
@@spid

 

 

You have now enabled the ability for encrypted connections to SQL Server