Where Does SQL Server Store Its Certificates

By:   |   Comments   |   Related: > Encryption


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?


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:

Microsoft Built-In Certificates

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:

User Created Certificate

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:

Can't back up certificate

And with the Dedicated Administrator Connection:

Can't back up certificate with DAC

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author K. Brian Kelley 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

Comments For This Article

get free sql tips
agree to terms