Where Does SQL Server Store Its Certificates

By: K. Brian Kelley   |   Updated: 2016-04-26   |   Comments (1)   |   Related: > Encryption

Problem

My security team was recently performing security scans and they noticed that when they attempted a connection to my SQL Servers, they were getting self-signed certificates. Where does SQL Server store these certificates? Also, why were they seeing self-signed certificates?

Solution

Let's start with your first question. If you connect to SQL Server with administrative rights, execute the following query in the master database:

SELECT * FROM sys.certificates;

You'll see something akin to this:

Note that there are several certificates listed, including one that looks like it's for authentication (##MS_SQLAuthenticatorCertificate##). This is where SQL Server stores its certificates, in the master database. Obviously, if you create an additional certificate in the master database (such as for use with Transparent Data Encryption), you'll see them as well. An example:

The Self-Signed Certificate

With respect to the second question, the answer is simple: SQL Server encrypts the logon process. Older versions of SQL Server (2000 SP 2 and below) did not and, with respect to SQL Server logins, the encryption was trivial to break. Therefore, an attacker with the ability to see the network traffic had the ability to capture the username and the encrypted password, apply a simple algorithm to decrypt the password, and then log in to SQL Server using that username/password combination.

If a certificate has not been provided for SQL Server to use, it'll use its own, self-generated certificate to protect the connection during the login process. Since it's a self-signed certificate, meaning SQL Server generated it, any security scanner is going to flag an issue because SQL Server is using a certificate the scanner doesn't trust unless you've somehow captured the certificate and imported it into the security scanner.

I say somehow because SQL Server doesn't let you backup the certificates it creates for itself. For instance:

And with the Dedicated Administrator Connection:

Therefore, there's no way within SQL Server to get the certificate. As a result, if you want to avoid the errors with your security scanner, you'll want to get a certificate that the scanner will trust issued to the computer where SQL Server is installed. There are some basic rules to be able to do this, according to Books Online:

• The certificate must be for Server Authentication.
• The certificate must be the fully qualified domain name for the server (server.mycompany.com as opposed to just server).
• The certificate must be stored under the computer account's certificate store.
• The client should be able to trust the certificate (meaning it was issued from a trusted certificate authority chain).

This isn't as hard as it sounds. See the next steps section for a step-by-step tutorial on how to obtain and install such a certificate.

Next Steps

• Learn how to install an SSL certificate for your SQL Server step-by-step.
• Read how to troubleshoot SSL encryption issues if you do choose to use a certificate.
• Understand how certificates are used as part of Transparent Data Encryption.

About the author

K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips