none
How to find which table columns are encrypted? RRS feed

  • Question

  • I'm trying to figure out which columns are encrypted and which keys are used to encrypt them.  What is the column crypt_property in sys.key_encryptions?  

    Monday, April 6, 2009 11:18 PM

All replies

  •   Unfortunately there is no schema/metadata information regarding what data is encrypted; the encrypted data is simply varbinary data for SQL Server Engine.

      One workaround that may help is to look at all the varbinary columns and use the key_name builtin (http://msdn.microsoft.com/en-us/library/cc645960.aspx) to find which columns seem to be storing encrypted data.

      I hope this information helps. Please let us know if you have additional questions or feedback.

      -Raul Garcia
       SDE/T
       SQL Server Engine
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, April 7, 2009 12:02 AM
    Moderator
  • Hi Raul,

    I wonder how the DecryptByKey function can use the correct symmetric key to decrypt.  If I have created multiple symmetric keys and used them to encrypt different data into different columns, using DecryptByKey can decrypt those columns correctly as long as the symmetric keys are opened.  Since DecryptByKey does not need the symmetric key as a parameter, it must somehow able to link the columns to the corresponding symmetric keys to decrypt them.    Are the links existed in somewhere that can be viewed or hidden by SQL Server?
    Tuesday, April 7, 2009 1:35 AM
  •   The encrypted message includes the key GUID as a preamble, and it is used to find the key in the key ring.

       I recently posted an article were we described the encrypted message format in detail. You can find it in the SQL Server Security blog: http://blogs.msdn.com/sqlsecurity/archive/2009/03/29/sql-server-encryptbykey-cryptographic-message-description.aspx

      -Raul Garcia
       SDE/T
       SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, April 7, 2009 6:51 PM
    Moderator
  • Try:

    select *
    from   sys.all_columns
    where  encryption_type is not null

    Thursday, February 8, 2018 8:26 AM