Certificate requirements for SQL Server
This article describes certificate requirements for SQL Server and how to check if a certificate meets these requirements.
Certificate requirements for SQL Server encryption
For using TLS for SQL Server encryption, you need to provision a certificate (one of the three digital types) that meets the following conditions:
The certificate must be in either the local computer certificate store or the SQL Server service account certificate store. We recommend local computer certificate store as it avoids reconfiguring certificates with SQL Server startup account changes.
The SQL Server service account must have the necessary permission to access the TLS certificate. For more information, see Configuring SQL Server for Encryption.
The current system time must be after the value of the property Valid from and before the value of the property Valid to of the certificate. For more information, see Expired Certificates.
The certificate must be meant for server authentication. This requires the Enhanced Key Usage property of the certificate to specify Server Authentication (184.108.40.206.220.127.116.11.1).
The certificate must be created using the
AT_KEYEXCHANGE. This requires a certificate that uses a legacy Cryptographic Storage Provider to store the private key. Usually, the certificate's key usage property (KEY_USAGE) also includes key encipherment (
CERT_KEY_ENCIPHERMENT_KEY_USAGE) and a digital signature (
The Subject property of the certificate must indicate that the common name (CN) is the same as the host name or fully qualified domain name (FQDN) of the server computer. When using the host name, the DNS suffix must be specified in the certificate. If SQL Server is running on a failover cluster, the common name must match the host name or FQDN of the virtual server, and the certificates must be provisioned on all nodes in the failover cluster. For example, if you have a two-node cluster, with nodes named
test2.*<your company>*.com, and you have a virtual server named virtsql, you need to install a certificate for
virtsql.*<your company>*.comon both nodes. For more information on SQL clusters, see Before Installing Failover Clustering.
When connecting to an availability group listener, the certificates that are provisioned for each participating server node in the failover cluster should also have a list of all availability group listeners set in the Subject Alternate Name of the certificate. For more information, see Listeners and TLS/SSL certificates. For more information on SQL Always On, see Connect to an Always On availability group listener.
The Subject Alternate Name should include all the names your clients may use to connect to a SQL Server instance.
The client must be able to verify the ownership of the certificate used by the server. If the client has the public key certificate of the certification authority that signed the server certificate, no further configuration is necessary. Microsoft Windows includes the public key certificates of many certification authorities. If the server certificate was signed by a public or private certification authority for which the client doesn't have the public key certificate, you must install the public key certificate of the certification authority that signed the server certificate on each client that is going to connect to SQL Server.
SQL Server will not start if a certificate exists in the computer store, but only meets some requirements in the above list and if it is manually configured for use by SQL Server Configuration Manager or through registry entries. Select another certificate that meets all the requirements or remove the certificate from being used by SQL Server till you are able to provision one that meets requirements or use a self-generated certificate as discussed in SQL Server generated self-signed certificates.
Check if a certificate meets the requirements
Starting with SQL Server 2019 (15.x), SQL Server Configuration Manager automatically validates all certificate requirements during the configuration phase itself. If SQL Server successfully starts after you configure a certificate, it's a good indication that SQL Server can use that certificate. But some client applications may still have other requirements for certificates that can be used for encryption, and you may experience different errors depending on the application being used. In that scenario, you need to check the client application's support documentation for more information on the subject.
You can use one of the following methods to check the validity of the certificate for use with SQL Server:
sqlcheckis a command-line tool that will examine the current computer and service account settings and produce a text report to the Console window that is useful for troubleshooting various connection errors. The output has the following information regarding certificates:
Details for SQL Server Instance: This Certificate row in this section provides more details regarding the certificate being used by SQL Server (Self-generated, hard-coded thumbprint value, etc.). Certificates in the Local Computer MY Store: This section shows detailed information regarding all the certificates found in the computer certificate store.
For more information on the tool's capabilities and for download instructions, see Welcome to the CSS_SQL_Networking_Tools wiki.
certutil.exeis a command-line program, installed as part of Certificate Services. You can use certutil.exe to dump and display certificate information. Use the
-voption to get detailed information. For more information, see certutil.
Certificates snap-in: You can also use the Certificates snap-in window to view more information about certificates in various certificate stores on the computer. But this tool doesn't show
KeySpecinformation. For more information on how to view certificates with the MMC snap-in, see How to: View certificates with the MMC snap-in.
SQL Server only checks the validity of the certificates at the time of configuration. For example, you won't be able to use Configuration Manager on SQL Server 2019 (15.x) and later versions, to provision an expired certificate. SQL Server will continue to run without problems if the certificate expires after it has already been provisioned. But some client applications like Power BI check the validity of the certificate on each connection and will error out if the SQL Server instance is configured to use an expired certificate for encryption. We recommend that you don't use an expired certificate for SQL Server encryption.
Skicka och visa feedback för