Skip to content

0770 SQL Server Certificates

Jump to bottom
Malcolm Stewart edited this page Jun 21, 2022 · 7 revisions

0770 SQL Server Certificates

About SQL Certificates and Troubleshooting Various Certificate Scenarios

Note: When talking in general terms, TLS and SSL may be used interchangeably.

​In a Client-Server environment, encryption of data transferred over the wire is very important. Without encryption, logins, passwords and other sensitive information is transferred in cleartext and can be vulnerable for man in the middle attacks.

Starting with SQL Server 2005, login packets for connections using SQL authentication were encrypted by default using a self-signed certificate that is created during the startup of SQL Server service. You will notice the following entry in the SQL Server ERRORLOG.

      A self-generated certificate was successfully loaded for encryption.

Since this is a self-signed certificate, it offers a weak encryption, and the data can still be sniffed using man in the middle attacks and hence it is important to provision a stronger certificate and use SSL for encrypting communication.

Note: The self-signed certificate is used only when a certificate is not provisioned explicitly for SSL.

Secure Socket Layer (SSL) is a security protocol used to encrypt communication (login and data) between a client and a server. SQL Server supports communication over SSL protocol. Here is a high level overview of how encryption works with SSL using a browser and webserver as client-server.

  1. Browser connects to a web server (website) secured with SSL (https). Browser requests that the server identify itself.
  2. Server sends a copy of its SSL Certificate, including the server’s public key.
  3. Browser checks the certificate root against a list of trusted CAs and that the certificate is unexpired, unrevoked, and that its common name is valid for the website that it is connecting to. If the browser trusts the certificate, it creates, encrypts, and sends back a symmetric session key using the server’s public key.
  4. Server decrypts the symmetric session key using its private key and sends back an acknowledgement encrypted with the session key to start the encrypted session.
  5. Server and Browser now encrypt all transmitted data with the session key.

Essentially, three keys are used to set up the SSL connection: the public, private, and session keys. Anything encrypted with the public key can only be decrypted with the private key, and vice versa. Because encrypting and decrypting with private and public key takes a lot of processing power, they are only used during the SSL Handshake to create a symmetric session key. After the secure connection is made, the session key is used to encrypt all transmitted data.

Reference: https://www.digicert.com/ssl

Note: SQL Server also supports the TLS 1.2 protocol as discussed here. Steps to enable encryption when TLS 1.2 protocol is enabled is same as SSL which is being discussed in this post.

Enabling SSL for SQL Server involves following steps.

  • Obtaining a certificate from Certificate Authority or CA. The certificate must meet the requirements outlined at Certificate Requirements section of the article Encrypting Connections to SQL Server. Failure to meet any of these requirements will prevent SQL Server from starting successfully when the certificate is provisioned for SSL. The certificate must be stored in the certificate store of the machine running SQL Server.
  • Configuring the certificate for use by SQL Server using SQL Server configuration. This can be done by following steps from To configure SSL section of the article Encrypting Connections to SQL Server.

For more detailed steps on how to request for a certificate, refer to the page Using SSL with SQL

Note: SQL Server needs to be restarted to enable encryption using SSL after a certificate has been provisioned.

Configuration manager cannot be used to provision the certificate when the instance of SQL Server is clustered. This is a design limitation. To enable a clustered instance of SQL Server for SSL, the following steps must be followed.

  • Install the server certificate with the fully qualified DNS name of the failover clustered instance on all nodes in the failover cluster.
  • Navigate to the certificate store where the FQDN certificate is stored. On the properties page for the certificate, go to the Details tab and copy the thumbprint value of the certificate to a Notepad window.
  • Remove the spaces between the hex characters in the thumbprint value in Notepad.
  • Save the notepad file in ANSI format. You will see the following warning. Click OK. 
    This file contains characters in Unicode format which will be lost if you save this file as an ANSI encoded text file. To keep the Unicode information, click Cancel below and then select one of the Unicode options from the Encoding drop down list. Continue?
  • Open the saved notepad file and a special character character usually a “?’ is seen at the beginning. Skip this special character and copy the rest of the thumbprint value to the following registry key.
      HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\<instance>\MSSQLServer\SuperSocketNetLib\Certificate
  • If the SQL virtual server is currently on this node, failover to another node in your cluster, and then reboot the node where the registry change occurred.
  • Registry key must be copied to each node of the failover cluster followed by a reboot.

Reference: https://blogs.msdn.microsoft.com/jorgepc/2008/02/19/enabling-certificate-for-ssl-on-a-sql-server-2005-clustered-installation

Once a certificate is provisioned for SQL Server, the following message in the errorlog indicates that certificate has met all the requirements and certificate is ready for use.

     The certificate [Cert Hash(sha1) "<Certificate Thumbprint>"] was successfully loaded for encryption.

Provisioning a certificate for SSL alone does not enable encryption of the login and data packets automatically for all clients connecting to SQL Server. Additional configuration is required both on the server and the client application or client machine to enable encryption. Encryption can be achieved by implementing one of the following 3 procedures.

  1. Client application initiated Encryption: An individual client application can request encryption by setting "encrypt=true" in it's connection string. This is often used with "trust server certificate" setting.
  2. Client machine initiated encryption: All the client applications originating from a machine can request encryption by setting "force protocol encryption" to true. This is often used with "trust server certificate" setting.
  3. Server initiated encryption: Set FORCE ENCRYPTION setting to TRUE on the SQL Server machine using configuration manager. This setting will force all connections to SQL Server to be encrypted regardless of client settings.

We shall look into all these options in detail here.

SHA512 Certificates for Windows 2012R2, 2012, 2008R2, 8.1, 7

This happens to older windows version connecting to a newer SQL servers that have a certificate encryption of SHA512 applied to them .

Error Message: A connection was successfully established, but then an error occurred during the login process. (Provider: SSL Provider, error: 0 - The Token supplied to the function is invalid.) (.Net SqlClient Data Provider).

Cause: Windows versions do not have support for SHA512 Certificates.

Resolution: Update the windows version with the appropriate windows update.

Reference
https://support.microsoft.com/en-us/topic/sha512-is-disabled-in-windows-when-you-use-tls-1-2-5863e74e-e5b6-cc3b-759b-ece8da875825

SHA1 Certificates on Azure Clients

This happens when the client is on an Azure VM or other Azure service, such as Azure Data Factory, and the server presents a SHA1 certificate to the client.

Error Message: Provider: SSL Provider, error: 0 - The Token supplied to the function is invalid.

Cause: Azure clients do not support SHA1 Certificates. If SQL Server is using a self-generated certificate, SQL 2017 and above use SHA256 for the self-generated certificate. Earlier versions use SHA1 for the self-signed certificate. Run SQL Connectivity Check (SQLCHECK) on the server and read the report near the end, or read the SQL Server ERRORLOG file, or take a network trace for direct confirmation.

Resolution: Replace the SQL Server certificate with a SHA256 certificate.

Reference: https://docs.microsoft.com/en-us/azure/security/fundamentals/ocsp-sha-1-sunset

Server initiated encryption

The Force Encryption setting under SQL Server Network Configuration -> Protocols for controls the server side encryption setting. The default value for this setting is set to No. When this setting is set to Yes, it indicates that all the clients connecting to SQL Server must be encrypted. Clients have no control on whether communication between them and the server are encrypted. This setting should be used carefully since encryption involves additional overhead during the pre-login handshake resulting in connection timeouts.

Client application initiated encryption

Often, clients may want to have control on whether they want to request encryption. For example, if there are two applications originating connections to server from the same client machine, application 1 may be more sensitive and desires to request encryption whereas application 2 doesn’t need any encryption. For this scenario, the following two connection string parameters control the encryption.   Encrypt=true: Using this parameter in the connection string indicates that the client is explicitly requesting the server to encrypt the communication. This parameter is often combined with another parameter trust server certificate.   Trust Server Certificate:

  • When Trust Server Certificate is set to YES, this indicates that the client is trusting the server certificate and skips server certificate validation.
  • When the encrypt property is set to true and the trustServerCertificate property is set to true, the client will not validate the SQL Server SSL certificate.
  • When the encrypt property is set to true and the trustServerCertificate property is set to false, the client will validate the SQL Server SSL certificate. Validating the server certificate is a part of the SSL handshake and ensures that the server is the correct server to connect to.
  • When the client is not able to validate the SQL Server SSL certificate, connection fails with the following error.

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The target principal name is incorrect.) (Microsoft SQL Server, Error: -2146893022)   For additional information, refer to Selectively using secure connection to SQL Server.  

Client machine initiated encryption:

If a client chooses to enforce encryption for all the applications originating from the client machine, Force protocol encryption setting can be used. To set the encryption on a per-machine basis, use the SQL Server Configuration Manager (right-click "SQL Native Client Configuration" and select "Yes" in the drop-down box next to "Force protocol encryption"). The option ‘Trust server certificate’ behaves the same way as described earlier.   The following table summarizes different combinations and the net encryption result when a certificate is provisioned.  

Force Protocol Encryption client setting Trust Server Certificate client setting Connection string/connection attribute Encrypt/Use Encryption for Data Connection string/connection attribute Trust Server Certificate Result
No N/A No (default) Ignored No encryption occurs. 
No N/A Yes No (default) Encryption occurs only if there is a verifiable server certificate, otherwise the connection attempt fails.
No N/A Yes Yes Encryption always occurs but may use a self-signed server certificate. 
Yes No Ignored Ignored Encryption occurs only if there is a verifiable server certificate, otherwise the connection attempt fails.
Yes Yes No (default) Ignored Encryption always occurs but may use a self-signed server certificate. 
Yes Yes Yes No (default) Encryption occurs only if there is a verifiable server certificate, otherwise the connection attempt fails.
Yes Yes Yes Yes Encryption always occurs but may use a self-signed server certificate.

  Reference: Encrypting connections in SQL Server 2005 & SQL Native Client with SSL

The column encrypt_option in sys.dm_exec_connections DMV shows whether the communication between the client that made the connection to the server is encrypted. Network monitor may also be used to validate that the communication between a client and server is encrypted.

Creating a Certificate for test purposes:

For testing purposes, we can use the following Powershell command to create a certificate.

New-SelfSignedCertificate -DnsName hostname.domain.com -CertStoreLocation cert:\LocalMachine\My -FriendlyName SQLSSLCert -KeySpec KeyExchange

**Note: **When SQL Server detects a certificate in the certificate store that meets all the requirements to be used for SSL, the server automatically loads the certificate even if the certificate is not explicitly specified for SSL through Configuration Manager.

Troubleshooting Scenarios where SSL certificate does not show up in SQL Server Configuration Manager

  1. If yours is a clustered instance of SQL Server, then the CN in your certificate will contain the FQDN of your virtual server name of the instance and not the node names. SQL Configuration Manager does a direct match between the current machine name and the CN name in the certificate [i.e. certificates that match the machine name are only displayed], which will never match in case of a clustered instance. So you can ignore this and use the registry method to load the certificate.
  2. The certificate has expired.
  3. The Server Authentication property or the AT_KEYEXCHANGE property is not set.
  4. The Certificate is using Subject Alternate Names (SAN), which is not understood by SSCM.

Reference: https://thesqldude.com/tag/openssl

Troubleshooting Scenarios where SQL Server fails to start after a certificate has been provisioned for SSL

Scenario 1:

The server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x8009030e. Check certificates to make sure they are valid.
Error: 26014, Severity: 16, State: 1.
Unable to load user-specified certificate [Cert Hash(sha1) "2F5DA1D4550DFEFG0IFL8D58E0AC5F266EC7"]. The server will not accept a connection. You should verify that the certificate is correctly installed. See "Configuring Certificate for Use by SSL" in Books online
Error: 17182, Severity: 16, State: 1.
TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.
Error: 17182, Severity: 16, State: 1.
TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason:
Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property.

Reason:

Certificate does not have a Private key. To validate this, right click on certificate tasks “Manage Private Keys”, you will not see the string “You have a private key that corresponds to this certificate”.

Fix:

Recreate the certificate with a private key.

Scenario 2:

The server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x8009030d. Check certificates to make sure they are valid.
Error: 26014, Severity: 16, State: 1.
Unable to load user-specified certificate The server will not accept a connection. You should verify that the certificate is correctly installed. See "Configuring Certificate for Use by SSL" in Books Online.
Error: 17182, Severity: 16, State: 1.
TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.
Error: 17182, Severity: 16, State: 1.
TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property.
Error: 17826, Severity: 18, State: 3.
Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
Error: 17120, Severity: 16, State: 1.
SQL Server could not spawn FRunCM thread.

Reason:

SQL Server service account does not have necessary permissions to one of the following folders or registry locations:

  • C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\
  • C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys
  • HKLM\System\CurrentControlSet\Services\WinSock2\Parameters

Fix:

Grant service account full control on the above folders.

Scenario 3:

Invisible character that may have been inadvertently added to the certificate's Thumbprint value.   Error: 17182, Severity: 16, State: 1.
TDSSNIClient initialization failed with error 0xd, status code 0x38.
Error: 17182, Severity: 16, State: 1.
TDSSNIClient initialization failed with error 0xd, status code 0x1.
Error: 17826, Severity: 18, State: 3.
Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
Error: 17120, Severity: 16, State: 1.
SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Fix:

Refer to KB2023869. This is also covered earlier in this article.

References

Clone this wiki locally