Where Does SQL Server Store Its Certificates


By:   |   Updated: 2016-04-26   |   Comments   |   Related: More > 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:

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


Last Updated: 2016-04-26


get scripts

next tip button



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

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools