Using SSL to Connect to SQL Server

By default, Keyfactor Command connects to SQL using an encrypted connection using an SSLClosed TLS (Transport Layer Security) and its predecessor SSL (Secure Sockets Layer) are protocols for establishing authenticated and encrypted links between networked computers. certificate configured on your SQL server.

You can check whether your SQL server has been configured with an SSL certificate in one of two ways:

  1. On the SQL server, open the SQL Server Configuration Manager and drill down under SQL Server Network Configuration to find Protocols for [YOUR INSTANCE NAME].
  2. Right-click on Protocols for [YOUR INSTANCE NAME] and choose Properties.
  3. Check the Certificate tab of the Properties dialog to see if a certificate has been configured and is still valid. If your certificate has a friendly name, it will appear here listed in the dropdown by its friendly name.
    4. Important:  A certificate will only appear here if it has a CNClosed A common name (CN) is the component of a distinguished name (DN) that represents the primary name of the object. The value varies depending on the type of object. For a user object, this would be the user's name (e.g. CN=John Smith). For SSL certificates, the CN is typically the fully qualified domain name (FQDN) of the host where the SSL certificate will reside (e.g. servername.keyexample.com or www.keyexample.com).1, usually the FQDN of the SQL server. If a certificate has been configured without this, it will not appear to be configured through this UI.

    Figure 483: SQL Server Configuration Manager View Active SSL Certificate

  1. On the SQL server, open the registry editor and browse to (where [MSSQL15.MSSQLSERVER] is the correct version of SQL server for your server):

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\[MSSQL15.MSSQLSERVER]\MSSQLServer\SuperSocketNetLib
  2. In the SuperSocketNetLib registry key, look for a Certificate value.
  3. Validate that the Certificate value has a thumbprint configured. This should match the thumbprint of an active certificate with a Server Authentication EKU in the Local Machine certificate store.

    4. Figure 484: Registry View Active SSL Certificate

To acquire a new certificate for your SQL server:

  1. On the SQL server open the Services.msc MMC and scroll down to locate the SQL Server ([YOUR INSTANCE NAME]) service.

  2. Check the Log On As column for the name of the service account that the service is running as.

    Figure 485: View SQL Server Services

  3. Identify a templateClosed A certificate template defines the policies and rules that a CA uses when a request for a certificate is received. with a Server Authentication EKU (a typical web server template).

  4. On the SQL server, do one of following:

    • Using the GUI:

      1. Open an empty instance of the Microsoft Management Console (MMC).
      2. Choose File->Add/Remove Snap-in….
      3. In the Available snap-ins column, highlight Certificates and click Add.
      4. In the Certificates snap-in popup, choose the radio button for Computer account, click Next, accept the default of Local computer, and click Finish.
      5. Click OK to close the Add or Remove Snap-ins dialog.

    • Using the command line:

      1. Open a command prompt using the “Run as administrator” option.

      2. Within the command prompt type the following to open the certificates MMC:

        certlm.msc

  5. Enroll for the certificate using your preferred method, being sure to give the certificate a CN (it will not appear in the configuration tool without this) and add subject alternative names (SANs) to it for all the IP addresses, server names, and FQDNs that you might use to reference the SQL server when communicating with it, including DNSClosed The Domain Name System is a service that translates names into IP addresses. aliases. Install it, along with its private keyClosed Private keys are used in cryptography (symmetric and asymmetric) to encrypt or sign content. In asymmetric cryptography, they are used together in a key pair with a public key. The private or secret key is retained by the key's creator, making it highly secure., into the Local Machine certificate store on the SQL server. One way to do this is in the certificates MMC:

    1. Drill down to the Personal folder under Certificates for the Local Computer, right-click, and choose All Tasks->Request New Certificate….
    2. Follow the certificate enrollmentClosed Certificate enrollment refers to the process by which a user requests a digital certificate. The user must submit the request to a certificate authority (CA). wizard, selecting the template you identified for this purpose, and providing appropriate SANs along with any required information.

    Figure 486: SQL Server SSL Certificate Details

  6. Drill down to the Personal folder under Certificates for the Local Computer, locate your certificate, right-click, and choose All Tasks->Manage Private Keys….

  7. In the Permissions for private keys dialog, click Add, add the SQL service account, and grant that service account Read but not Full control permissions. If the SQL server is running as NT Service\[YOUR INSTANCE NAME] as shown in Figure 485: View SQL Server Services, be sure to change the location to your local machine and enter the object name as “NT SERVICE\[YOUR INSTANCE NAME]” as shown in Figure 487: Grant Private Key Permissions for SQL Server.

    Figure 487: Grant Private Key Permissions for SQL Server

  8. Click OK to save.
  9. Configure the SSL certificate in SQL using either the SQL Server Configuration Manager or registry as shown above for checking whether there is an existing certificate configured (see SQL Server Configuration Manager).
  10. After you've acquired a new certificate, made the private key permission changes, and associated it in SQL, you'll need to restart the SQL Server (Instance Name) service (see Figure 485: View SQL Server Services) before these changes will take effect.