Where Does SQL Server Store Its Certificates


By:   |   Updated: 2016-04-26   |   Comments   |   Related: More > Encryption

Secure, Simple and Affordable SQL Server Database Encryption

Free MSSQLTips Webinar: Secure, Simple and Affordable SQL Server Database Encryption

Ensuring the security of your SQL Server database is priority number one. It can be a costly and time consuming project with upgrades, code changes, additional licensing and more. Nevertheless, as a SQL Server Professional you are still responsible for the protection of client data as a best practice and to meet various security regulations.


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





Comments For This Article





download


Recommended Reading

Storing passwords in a secure way in a SQL Server database

SQL Server Column Level Encryption Example using Symmetric Keys

Encrypting and Decrypting SQL Server Stored Procedures, Views and User-Defined Functions

Updating an expired SQL Server TDE certificate

SQL Server Database Encryption for GDPR Compliance with DbDefence





get free sql tips
agree to terms


Learn more about SQL Server tools