Enabling Encrypted Connections with SQL Server Configuration

  • Article

Hello Readers,

Here is another post arriving from Norm Eberly.

 

My colleague Sean Gallardy and I were asked about the process for enabling encrypted connections for SQL Server as outlined here when using a Group Managed Service Account (gMSA).

NOTE: an update to the next four italicized sentences regarding the content of the reference.  Based on the research and testing by the author, documentation changes were made to reflect the identified findings .  The following four sentences are left for the sake of completeness, in case you’re visiting this post after seeing the older content.

According to the reference, in order to install the certificate, you need to be running SQL Server Configuration Manager under the same user account as the SQL Server service.

The same reference goes on to say if you are running as one of the local virtual accounts (LocalSystem, NetworkService or LocalService), then use an administrative account to run SQL Server Configuration Manager.

But this is not possible if we are running the SQL Server service with a Managed Service Account (MSA) or gMSA, we can’t run SQL Server Configuration Manager (SSCM) as one of those accounts – we do not know the password of the MSA/gMSA account.

Does that mean if we are running SQL Server using an MSA or gMSA account, we should use an administrative account to run SQL Server Configuration Manager?

This post is centered on describing our investigation of these three questions regarding encrypted connection setup and usage by SQL Server Configuration Manager (SSCM).

  • If we are running SQL Server using an MSA or gMSA account, should we use an administrative account to run SSCM? 
  • Will using an administrative account work well in all cases, even for a standard domain account? 
  • Are there any special tasks required to enable Encrypted Connections when using gMSA accounts?

We tested SQL Server 2016 for both stand-alone instances and failover cluster instances (FCI), using local virtual accounts, standard domain accounts and MSA/gMSA accounts as appropriate for the architecture. 

We also tested the same steps for SQL Server 2012 and SQL Server 2014.

 

Prerequisite Assumptions

·         We have MSA and gMSA accounts properly created and deployed to the servers we will be testing with.  See https://blogs.msdn.microsoft.com/markweberblog/2016/05/25/group-managed-service-accounts-gmsa-and-sql-server-2016/ for details and help.

·         We have a certificate created and available that meets the requirements outlined in the initial reference on enabling encrypted connections. 

We will need the following certificates

1.       One for the stand-alone instance which has the fqdn of the server hosting the instance under test.

I tested with W12R2-S16.contoso.com as the DNS fqdn.

2.       One for the FCI instance.  This will have the fqdn of the FCI server name, not the individual node names, nor the fqdn of the WSFC cluster name.  My SQL Server 2016 Virtual Server name is C1-S16-I01.

I tested with C1-S16-I01.contoso.com as the DNS fqdn.

We used New-SelfSignedCertificate to generate the test certificates.  Be aware that in order for this to work, we found it necessary use PowerShell v5.0 in order to be able to set the -KeySpec parameter.  Prior versions of PowerShell do not have this parameter for the New-SelfSignedCertificate.  Here is an example command we used for this test:

New-SelfSignedCertificate -DnsName W12R2-S16.contoso.com -CertStoreLocation cert:\localmachine\My -friendlyname MSATestCert-S16 -KeySpec KeyExchange

a.       In the testing I performed, I created each test certificate needed on a Windows Server 2016 server, exported the certificate and imported it on Windows Server 2012 R2 servers that were running the SQL Server instances I was using for testing.

b.       Powershell v5.0 is installed by default with Windows Server 2016.  It can also be downloaded and installed on earlier Windows versions, see https://msdn.microsoft.com/en-us/powershell/scripting/whats-new/what-s-new-in-windows-powershell-50 for more details

·         We used a domain account that is a member of the Local Administrators group on the nodes of the cluster and the stand-alone server.

 

Step-by-Step for a Stand-Alone Instance using an MSA account

Provision or Install the certificate

I followed the steps outlined here, under “To provision (install) a certificate on the server” and provide further details or information to help clarify the process.

Follow steps 1 through 7 to get the Certificate management snap-in open.

At step 8, this is where you will import the certificate to be used, if not already installed.  Walk through the Import wizard to install the certificate, if necessary.

At step 9, we add permissions for the MSA account.  Note that the only permissions required here are Read permissions.  Add the account, then set Read permissions for the account.  In the Select Users, Computers, Service Accounts, or Groups dialog box, you may have to click Object Types and add Service Accounts as one of the types.

To Configure SQL Server to Accept Encrypted Connections

Follow the steps outlined here, under “To configure the server to accept encrypted connections”.

To validate, once you restart the SQL Server service, look at the SQL Server errorlog and you should find an entry similar to the following:

                spid11s     The certificate [Cert Hash(sha1) "FF005EB2E103B63498B6AD1ADDF6B89D226196BE"] was successfully loaded for encryption.

 

Step-by-Step for a Failover Clustered Instance using a gMSA account

Provision or Install the certificate

As per the reference, the certificate needs to be installed on each node of the cluster that will be able to run that instance of SQL Server.

In all other respects, the process is the same as a standalone instance with respect to installing the certificate and setting the Private Key permissions.

To Configure SQL Server to Accept Encrypted Connections

When using SQL Server Configuration Manager to set the Certificate to use, you will notice that the certificate is not listed.

This is a known issue for failover clustered instances.  This is due to SQL Server Configuration Manager searching the local computer nodes’ certificate store.   The certificate we used has the FCI Virtual Server DNS fqdn, not a cluster node fqdn.  Since the installed certificate fqdn is not associated with the local node, it is not listed. 

For FCI instances, you will need to follow the steps in KB Article 316898, under “Enable a certificate for SSL on a SQL Server clustered installation”, and set the registry key to the certificate thumbprint value.

I have a three-node cluster that I am testing with.  I started with the instance of SQL Server running on node 1.  I installed the certificates, set the private key permissions for the service account, then set the registry key thumbprint value on nodes 2 and 3 first, then did a failover to node 2 and completed the steps on node 1. 

Summary

Our testing shows that if using MSA or gMSA accounts, running the SQL Server Configuration Manager with local administrator level privileges does allow the encryption certificate settings to be modified.

We can also run SQL Server Configuration Manager with local administrator level privileges when using local virtual accounts and standard domain accounts.

 

Completely Off Topic

Pollarding is a pruning system for the upper branches of trees in order to manage tree height as well as provide for a sustainable harvest of wood for domestic purposes.  It has been practiced since at least Roman times and is still widely practiced world wide. View of trees that have been pollarded.