I ran across an article recently from MSSQLTips by my friend, Brian Kelley. It talks about the ways you can detect encryption in use in your database. Brian’s approach, which is one I agree with, is that you can look for symmetric keys, asymmetric keys, and certificates in the system tables. The tables you query are:

• sys.symmetric_keys
• sys.asymmetric_keys
• sys.certificates

That’s a good way to detect SQL Server encryption in use, but not encryption in general. One of the things I’ve advocated for applications that contain sensitive data and need to be protected from the DBA is to have the application create temporary keys or use .NET libraries to encrypt data. In that case, SQL Server just sees data, and doesn’t detect encryption.

Brian offers a solution that is to examine any columns containing these data types

• binary
• varbinary
• image

That’s a good start, but how do you detect that this string is encrypted?

504b 0304 1400 0000 0800 1a86 4640 0d41 …

That’s actually not encrypted; it’s the start of a zip file. However it could be a jpg, a tiff or some other binary format. The only way I thought of was mirrored in this Stack Overflow note: you’d have to compare known file types and look for a pattern in a header of some sort that doesn’t match. It wouldn’t be sure you didn’t have encryption, but you might make some educated guess if no file type that might fit the data matches.

There was also a link in the comments to a Stack Exchange discussion on the same topic. It’s similar, though I saw the use of the KEY_NAME() function in there. I hadn’t used it, perhaps because of the poor documentation of encryption in SQL Server. I also found a KEY_ID() function that works similarly, returning the ID for the name of a key.

Filed under: Blog Tagged: encryption, security, syndicated