Finding Encrypted Data in a SQL Server Database

By: K. Brian Kelley   |   Updated: 2015-01-14   |   Comments (2)   |   Related: > Encryption

Problem

I've recently taken over a SQL Server and I have been asked if there is encrypted data in any of the user databases. Is there an easy way to determine if this is the case?

Solution

The easiest way to determine if there is encrypted data in a database is to get that information from whoever wrote the application. Sometimes this is through documentation and other times it's by contacting the development team or the vendor. In reality, this is the only way to be completely sure. However, barring this method, there are a few things you can look for which would suggest that you have encrypted data in a given database.

SQL Server Built-In Encryption Markers

A marker for the possibility of encrypted data are cryptographic objects within the SQL Server database. SQL Server supports three specific things to look for:

• Symmetric Keys
• Asymmetric Keys
• Certificates

As you might guess, if we query the correct catalog views, we should be able to determine if built-in encryption is being used.

Symmetric Keys

If the SQL Server database master key has been created, it will appear in the symmetric keys catalog views. The database master key is often used to encrypt asymmetric keys and certificates. Asymmetric keys and certificates are often used to encrypt symmetric keys (other than the database master key). Symmetric keys are then used to encrypt the data. As a result, any entries in this catalog view would indicate a strong possibility that there is encrypted data in the database. How do we find those symmetric keys? This query will list the symmetric keys, their algorithms, key length, and when they were created/modified:

SELECT name, key_length, algorithm_desc, create_date, modify_date
FROM sys.symmetric_keys;

One place in SQL Server where you should always find at least one symmetric key in the master database. At a minimum it will have a symmetric key corresponding to the service master key.

Asymmetric Keys

A database master key doesn't have to exist for an asymmetric key to be present. In addition, it is possible to encrypt data in SQL Server using an asymmetric key. Knowing these two facts, we should also query for the existence of asymmetric keys:

SELECT name, algorithm_desc 
FROM sys.asymmetric_keys;

Please note that I didn't include the created or modified date in that query. That's because the asymmetric key catalog view differs quite a bit from the symmetric key catalog view and doesn't include those columns. If you want to know what all the columns are for each catalog view, the best place to look is in Books Online for the appropriate version of SQL Server.

Certificates

Certificates and asymmetric keys use the same algorithm within SQL Server. They differ in some key ways, namely that you can back up a certificate as well as have a certificate generated elsewhere and imported into SQL Server. Certificates can be used to encrypt data as well. Because of this, we need to check for the existence of certificates, even if we don't see any symmetric keys.

SELECT name, subject, start_date, expiry_date 
FROM sys.certificates

An important note here on certificates: though an expiration date is specified, it is not checked nor enforced when used for data encryption.

I Found Something!

If one of these queries returns an encryption object, that means you've got a strong probability that there's encrypted data in that database. However, this isn't always the case. You could have legacy objects where data was once encrypted, but isn't any longer. Or you could have the case where a certificate or asymmetric key was used to sign a stored procedure. Therefore, even if you find these objects, more investigation is required. Finding these objects is important, though, because it can tell you how the data is/was encrypted.

Looking Through Data Types

There are three data types we're concerned with that handle binary data. These are the most likely data types for encrypted data. While it is possible to use sql_variant or even xml, those data types aren't very efficient. Therefore, it's best to query for any columns that use the following types:

• binary
• varbinary
• image

This query will tell us exactly what tables have these types of data types:

SELECT S.name AS 'Schema', T.name AS 'Table', 
       C.name AS 'Column', ty.name AS 'DataType'
FROM sys.columns C
  JOIN sys.types ty
    ON C.system_type_id = ty.system_type_id
  JOIN sys.tables T
    ON C.object_id = T.object_id
  JOIN sys.schemas S
    ON T.schema_id = S.schema_id
WHERE ty.name IN ('binary', 'varbinary', 'image')
ORDER BY S.name, T.name, C.name, ty.name;

Another note: just because these data types exist doesn't mean that the data is encrypted. For instance, run the query against the msdb database. The packagedata in the sysssispackages table is only "encrypted" data if the person storing the package chose to protect it. Otherwise, it's simply the binary file for the SSIS package.

What If I Didn't Find the Encryption Objects?

You should still query the column data types. If an application performs the encryption outside of SQL Server, then the built-in encryption objects won't be there. However, you will still have encrypted data in your database. There are various reasons for why an application would encrypt data outside of SQL Server. However, that isn't relevant to the question of whether such data exists in a database you are responsible for.

So There's Still Some Legwork to Be Done?

In a word, yes. The existence of symmetric keys, asymmetric keys, certificates, and/or binary data type columns suggest that there could be encrypted data, but you'll still need to ask the right folks and verify. As I noted in each section, there are legitimate reasons for an object to exist or a for a column to be a binary data type that has nothing to do with encrypting data. As a result, the presence of any of these isn't an automatic indicator that you've got encrypted data. In short, there's no "easy button."

Next Steps

• Understand the difference in performance between symmetric and asymmetric keys.
• Make sure you know the symmetric key algorithms and which are safe to use.
• Know the role key length plays in asymmetric keys and which key lengths you should be using.
• Learn how to use authenticators so that just having access to the key isn't enough.

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