For some time now, SHA-1 has not been allowed to be issued from CA’s with few exceptions. This was due to a large catalog of evidence suggesting that SHA-1 was less secure than acceptable. Starting with SQL 2016 self-signed certificates are issued using SHA-2, however, we still have many installations of older versions that are still actively supported by Microsoft. As a result, to comply with standards set by regulating agencies, or your own internal security audits, we may be required to update the certificate that SQL will use when a connection to SQL is encrypted. If you would like to do this in your environment be sure to do it in a nonproduction environment to validate steps and test before executing in production.

I am going to assume that you know how to either generate a self-signed certificate or get one via a CA either private or public. If you need help the link has some good approaches, but I have not validated or tested them all. https://blogs.msdn.microsoft.com/sqlserverfaq/2016/09/26/creating-and-registering-ssl-certificates/

When requesting or generating your new certificate it is important to remember that SQL Server certificates do have some requirements. For SQL Server to load a SSL certificate, the certificate must meet the following conditions:

• The certificate must be in either the local computer certificate store or the current user certificate store.
• The current system time must be after the Valid from property of the certificate and before the Valid to property of the certificate.
• The certificate must be meant for server authentication. This requires the Enhanced Key Usage property of the certificate to specify Server Authentication (1.3.6.1.5.5.7.3.1).
• The certificate must be created by using the KeySpec option of AT_KEYEXCHANGE. Usually, the certificate’s key usage property (KEY_USAGE) will also include key encipherment (CERT_KEY_ENCIPHERMENT_KEY_USAGE).
• The Subject property of the certificate must indicate that the common name (CN) is the same as the hostname or fully qualified domain name (FQDN) of the server computer. 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.

Starting in SQL 2008R2 security was enhanced by forcing an exact match of the subjects for certificates. Applications that use a short name and not a FQDN may have trouble connecting in later versions. To facilitate the connection, there are a few options, but the easiest and most effective is to include Subject Alternative Names (SAN) in the certificate request. By using this approach, you can include the FQDN, the short name, any Virtual Network Names, and any aliases.

Now that we have generated the certificate request, received the certificate from whichever authority (or self-signed), and installed it in the local computers certificate store. We need to make SQL aware of it, but before we leave the certificate store we need to make sure that the SQL Server service account has permissions to read this certificate. We can do this by managing the private key of the certificate. To do this right click on the certificate All Tasks > Manage Private Keys. Here you can add the read permission to the SQL Server service account. Full control is not needed. Failing to ensure that the service account has permissions to this certificate will result in SQL failing to start as it will unable to load the user-specified certificate.

My requirement was only that the certificate that SQL was using was SHA-2 or above so I did not require encryption or worry about the certificate coming from a trusted source. If you intend to encrypt your connections to SQL with this certificate, you will want to be sure that is from a trusted local CA or some other trusted certificate authority.

Once the certificate is installed in the local computer certificate store and SQL will be able to read it we only need to tell SQL to start using the new certificate. This can be done in the SQL Server Configuration Manager by going to the properties of the protocols under the network configuration.

This change will require a restart of the SQL service to take effect. Once the service has been restarted, you can test the certificate by connecting to management studio. On the connection properties tab expand options then choose to encrypt your connection. Now if your new certificate was self-generated like I did with mine, it is not going to be from a trusted authority and you will get an error saying that it does not trust the cert. You can add a flag in the additional connection parameters tab to tell management studio to trust the certificate with TrustServerCertificate=True or by clicking the box to trust the server certificate. While this can be appropriate in some cases, I would highly recommend getting your certificate from a trusted source so you don’t have to take additional steps to connect.

Once you are connected, you can verify that your connection is encrypted by running the query below, provided you have view server state permissions.

SELECT session_id, connect_time, net_transport, encrypt_option, auth_scheme, client_net_address FROM sys.dm_exec_connections where session_id = @@spid

If you intend on requiring encrypted connections, I recommend testing all of the connections before forcing encrypted connections. That way you know your certificate will work for IP address and any aliases you may have in place as well as the FQDN. Additional information on encryption options can be found at: https://technet.microsoft.com/en-us/library/ms189067(v=sql.105).aspx