| title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic |
|---|---|---|---|---|---|---|---|---|
Configure SQL Server Database Engine for encryption |
This article describes how to configure a SQL Server instance to enable encrypted connections. |
VanMSFT |
vanto |
sureshka, randolphwest |
10/11/2024 |
sql |
configuration |
how-to |
[!INCLUDE sql-windows-only]
You can encrypt all incoming connections to [!INCLUDE ssnoversion-md] or enable encryption for just a specific set of clients. For either of these scenarios, you first have to configure [!INCLUDE ssnoversion-md] to use a certificate that meets Certificate requirements for SQL Server before taking extra steps on the server computer or client computers to encrypt data.
Note
This article applies to [!INCLUDE ssnoversion-md] on Windows. To configure [!INCLUDE ssnoversion-md] on Linux for encrypting connections, see Specify TLS settings.
This article describes how to configure [!INCLUDE ssnoversion-md] for certificates (Step 1) and change encryption settings of the [!INCLUDE ssnoversion-md] instance (Step 2). Both steps are required to encrypt all incoming connections to [!INCLUDE ssnoversion-md] when using a certificate from a public commercial authority. For other scenarios, see Special cases for encrypting connections to SQL Server.
To configure [!INCLUDE ssnoversion-md] to use the certificates described in Certificate requirements for SQL Server, follow these steps:
- Install the certificate on the computer that's running [!INCLUDE ssnoversion-md].
- Configure [!INCLUDE ssnoversion-md] to use the installed certificate.
Depending on the version of [!INCLUDE ssnoversion-md] Configuration Manager you have access to on the [!INCLUDE ssnoversion-md] computer, use one of the following procedures to install and configure the [!INCLUDE ssnoversion-md] instance.
In [!INCLUDE sssql19-md] and later versions, certificate management is integrated into [!INCLUDE ssnoversion-md] Configuration Manager, and can be used with earlier versions of [!INCLUDE ssnoversion-md]. To add a certificate on a single [!INCLUDE ssnoversion-md] instance, in a failover cluster configuration, or in an availability group configuration, see Certificate management (SQL Server Configuration Manager). The Configuration Manager greatly simplifies certificate management by taking care of installing the certificate and configuring [!INCLUDE ssnoversion-md] for using the installed certificate with just a few steps.
Certificates are stored locally for the users on the computer. To install a certificate for [!INCLUDE ssnoversion-md] to use, you must run [!INCLUDE ssnoversion-md] Configuration Manager with an account that has local administrator privileges.
You can temporarily install an Express edition of [!INCLUDE sssql19-md] or a later version to use [!INCLUDE ssnoversion-md] Configuration Manager, which supports integrated certificate management.
If you use [!INCLUDE sssql17-md] or an earlier version, and [!INCLUDE ssnoversion-md] Configuration Manager for [!INCLUDE sssql19-md] isn't available, follow these steps to install and configure the certificate on the [!INCLUDE ssnoversion-md] computer:
- On the Start menu, select Run, and in the Open box, type MMC and select OK.
- In the MMC console, on the File menu, select Add/Remove Snap-in....
- In the Add or Remove Snap-ins dialog box, select Certificates, and then select Add.
- In the Certificates snap-in dialog box, select Computer account, and then select Next > Finish.
- In the Add or Remove Snap-ins dialog box, select OK.
- In the MMC console, expand Certificates (Local Computer) > Personal, right-click Certificates, point to All Tasks, and select Import.
- Complete the Certificate Import Wizard to add a certificate to the computer.
- In the MMC console, right-click the imported certificate, point to All Tasks, and select Manage Private Keys. In the Security dialog box, add read permission for the user account used by the [!INCLUDE ssnoversion-md] service account.
- In SQL Server Configuration Manager, expand SQL Server Network Configuration, right-click Protocols for <server instance>, and select Properties.
- In the Protocols for <instance name> Properties dialog box, on the Certificate tab, select the desired certificate from the dropdown list for the Certificate box, and then select OK.
- If you require all the connections to [!INCLUDE ssnoversion-md] to be encrypted, see Step 2: Configure encryption settings in SQL Server. If you only want to enable encryption for specific clients, restart the [!INCLUDE ssnoversion-md] service and see Special cases for encrypting connections to SQL Server.
Note
To install certificates in the availability group configuration, repeat the previous procedure on each node in your availability group, starting with the primary node.
Important
The [!INCLUDE ssnoversion-md] service account must have read permissions on the certificate used to force encryption on the [!INCLUDE ssnoversion-md] instance. For a non-privileged service account, read permissions must be added to the certificate. Failure to do so can cause the [!INCLUDE ssnoversion-md] service restart to fail.
The certificate used by [!INCLUDE ssnoversion-md] to encrypt connections is specified in the following registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\SuperSocketNetLib\Certificate
This key contains a property of the certificate known as a thumbprint, which identifies each certificate in the server. In a clustered environment, this key is set to Null even though the correct certificate exists in the store. To resolve this issue, you must take these extra steps on each of your cluster nodes after you install the certificate to each node:
-
Navigate to the certificate store where the fully qualified domain name (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.
-
Start Registry Editor, navigate to the following registry key, and paste the value from Step 2:
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\<instance>\MSSQLServer\SuperSocketNetLib\Certificate -
If the SQL virtual server is currently on this node, fail over to another node in your cluster and restart the node where the registry change occurred.
-
Repeat this procedure on all the nodes.
Warning
Incorrectly editing the registry can severely damage your system. Before making changes to the registry, we recommend you back up any valued data on the computer.
Note
[!INCLUDE sql2008r2-md] and [!INCLUDE sql2008r2-md] Native Client (SNAC) support wildcard certificates. SNAC has since been deprecated and replaced with the Microsoft OLE DB Driver for SQL Server and Microsoft ODBC Driver for SQL Server. Other clients might not support wildcard certificates.
Wildcard certificate can't be selected by using [!INCLUDE ssnoversion-md] Configuration Manager. To use a wildcard certificate, you must edit the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer\SuperSocketNetLib registry key, and enter the thumbprint of the certificate, without spaces, to the Certificate value.
Note
To use encryption with a failover cluster, you must install the server certificate with the fully qualified DNS name of the virtual server on all nodes in the failover cluster. You can set the value of the ForceEncryption option on the Protocols for virtsql property box of SQL Server Network Configuration to Yes.
When creating encrypted connections for an Azure Search indexer to [!INCLUDE ssnoversion-md] on an Azure Virtual Machine, see Indexer connections to a SQL Server instance on an Azure virtual machine.
The following steps are only required if you want to force encrypted communications for all the clients:
- In [!INCLUDE ssnoversion-md] Configuration Manager, expand SQL Server Network Configuration, right-click Protocols for <server instance>, and then select Properties.
- On the Flags tab, in the ForceEncryption box, select Yes, and then select OK to close the dialog box.
- Restart the [!INCLUDE ssnoversion-md] service.
Note
Some certificate scenarios might require you to implement additional steps on the client computer and in your client application to ensure encrypted connections between the client and server. For more information, see Special cases for encrypting connections to SQL Server.
At a high level, there are two types of packets in the network traffic between a [!INCLUDE ssnoversion-md] client application and [!INCLUDE ssnoversion-md]: credential packets (login packets) and data packets. When you configure encryption (either server-side or client-side), both these packet types are always encrypted. But, even when you don't configure encryption, the credentials (in the login packet) that are transmitted when a client application connects to [!INCLUDE ssnoversion-md] are always encrypted. [!INCLUDE ssnoversion-md] uses a certificate that meets the certificate requirements from a trusted certification authority if available. This certificate is either manually configured by the system administrator, using one of the procedures previously discussed in the article, or present in the certificate store on the [!INCLUDE ssnoversion-md] computer.
[!INCLUDE ssnoversion-md] uses a certificate from a trusted certification authority if available for encrypting login packets. If a trusted certificate isn't installed, [!INCLUDE ssnoversion-md] generates a self-signed certificate (fallback certificate) during startup and use that self-signed certificate to encrypt the credentials. This self-signed certificate helps increase security, but it doesn't protect against identity spoofing by the server. If the self-signed certificate is used, and the value of the ForceEncryption option is set to Yes, all data transmitted across a network between [!INCLUDE ssnoversion-md] and the client application is encrypted using the self-signed certificate.
When you use a self-signed certificate, [!INCLUDE ssnoversion-md] logs the following message to the error log:
A self-generated certificate was successfully loaded for encryption.
[!INCLUDE sssql16-md] and earlier versions use the SHA1 algorithm. However, the SHA1 algorithm and many older algorithms are deprecated beginning with [!INCLUDE sssql16-md]. For more information, see Deprecated Database Engine features in SQL Server 2016 (13.x).
In these environments, if you're using the automatically generated self-signed certificate generated by [!INCLUDE ssnoversion-md], either just for the prelogin handshake or for encrypting all server-client communications, your vulnerability detection software or security software or company policies might flag this use as a security issue. You have the following options for these scenarios:
- Create a new self-signed certificate or a third-party certificate that uses stronger encryption algorithms and configure [!INCLUDE ssnoversion-md] to use this new certificate.
- Since you now understand the reason for the flag, you can ignore the message (not recommended).
- Upgrade to [!INCLUDE sssql17-md] or a later version that uses a stronger hash algorithm (SHA256) for self-signed certificates.
The following code snippet can be used to create a self-signed certificate on a computer running [!INCLUDE ssnoversion-md]. The certificate meets requirements for encryption for a stand-alone [!INCLUDE ssnoversion-md] instance and is saved in the local computer's certificate store (PowerShell must be launched as an administrator):
# Define parameters
$certificateParams = @{
Type = "SSLServerAuthentication"
Subject = "CN=$env:COMPUTERNAME"
DnsName = @("$($env:COMPUTERNAME)", $([System.Net.Dns]::GetHostEntry('').HostName), 'localhost')
KeyAlgorithm = "RSA"
KeyLength = 2048
HashAlgorithm = "SHA256"
TextExtension = "2.5.29.37={text}1.3.6.1.5.5.7.3.1"
NotAfter = (Get-Date).AddMonths(36)
KeySpec = "KeyExchange"
Provider = "Microsoft RSA SChannel Cryptographic Provider"
CertStoreLocation = "cert:\LocalMachine\My"
}
# Call the cmdlet
New-SelfSignedCertificate @certificateParamsTo verify that network encryption is configured and enabled successfully, run the following Transact-SQL query:
USE [master];
GO
SELECT DISTINCT (encrypt_option)
FROM sys.dm_exec_connections
WHERE net_transport <> 'Shared memory';
GOThe encrypt_option column is a Boolean value indicating whether encryption is enabled for this connection. If the value is TRUE, the connection is securely encrypted. If the value is FALSE, the connection isn't encrypted.
The SQL Server service detects and uses the certificate automatically for encryption if all of the following conditions are true:
- The certificate has a subject that contains the FQDN of the machine
- The certificate is installed in the Local Computer's certificate store
- The SQL Server service account is granted access to the certificate's private key
This use happens even if the certificate isn't selected in SQL Server Configuration Manager.
To override this behavior, either:
-
Configure another certificate to be used in the SQL Server Configuration Manager
or
-
Remove the SQL Server service account permissions to the undesired certificate