Post reply

query to track columns which are encrypted via Symmetric Keys

  • July 13, 2017 at 1:18 pm

    #331803

    Hi,

    Any query to track columns which are encrypted via Symmetric Keys?

    Thanks

    Thanks.

  • July 13, 2017 at 1:22 pm

    #1950827

    SQL-DBA-01 - Thursday, July 13, 2017 1:18 PM

    Hi,

    Any query to track columns which are encrypted via Symmetric Keys?

    Thanks

    Please define what you mean by 'track'.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • July 13, 2017 at 1:29 pm

    #1950829

    Is the below query sufficient to find the encrypted columns?


    SELECT stab.name Table_Name, sc.name Column_Name FROM sys.columns sc
       INNER JOIN sys.types st ON sc.system_type_id=st.system_type_id
       INNER JOIN sys.tables stab ON stab.object_id=sc.object_id
       WHERE st.name='varbinary'
       AND stab.is_ms_shipped=0

    Thanks.

  • July 13, 2017 at 1:43 pm

    #1950836

    I played around with database encryption some time ago.  You don't encrypt a column, per se.  You encrypt the data before you insert it into the table.  Check for varbinary columns in the database.  Also check in your code repository for stored procedures that call the function EncryptBySymKey.  Remember.  In a single column of encrypted values each value may be encrypted with a different key.  That would be the worst possible design, but it is still a possible design.

  • July 13, 2017 at 3:00 pm

    #1950845

    SQL-DBA-01 - Thursday, July 13, 2017 1:29 PM

    Is the below query sufficient to find the encrypted columns?


    SELECT stab.name Table_Name, sc.name Column_Name FROM sys.columns sc
       INNER JOIN sys.types st ON sc.system_type_id=st.system_type_id
       INNER JOIN sys.tables stab ON stab.object_id=sc.object_id
       WHERE st.name='varbinary'
       AND stab.is_ms_shipped=0

    No, that finds columns of the varbinary type. They may or may not be encrypted data.

    Encrypted is not a property of a column, so you cannot say definitively that a column is definitely encrypted.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 13, 2017 at 3:05 pm

    #1950847

    Is that case how to find it? DBA's often need to get the details.

    Thanks.

  • July 13, 2017 at 3:20 pm

    #1950849

    SQL-DBA-01 - Thursday, July 13, 2017 3:05 PM

    Is that case how to find it? DBA's often need to get the details.

    Nothing directly. I've seen others use Key_name but I haven't played with it enough to say for sure.
    If you want to try, do the search for varbinary columns and then use key_name to see if it returns the name of a key:
    select distinct key_name(YourVarbinaryColumn) from YourTable

    Sue

  • July 14, 2017 at 5:48 am

    #1950903

    SQL-DBA-01 - Thursday, July 13, 2017 1:18 PM

    Hi,

    Any query to track columns which are encrypted via Symmetric Keys?

    Thanks

    For Always Encrypted use this

    SELECTcmk.name
                       , cmk.key_path
                       , cev.encryption_algorithm_name
                       , cek.name
                       , OBJECT_NAME(c.object_id) AS TableName
                       , c.name
                       , c.encryption_type_desc
                       , c.encryption_algorithm_name
    FROM sys.columns c
    INNER JOIN sys.column_encryption_key_values cev
    ON c.column_encryption_key_id = cev.column_encryption_key_id
    INNER JOIN sys.column_encryption_keys cek
    ON cev.column_encryption_key_id = cek.column_encryption_key_id
    INNER JOIN sys.column_master_keys cmk
    ON cev.column_master_key_id = cmk.column_master_key_id

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • July 14, 2017 at 6:02 am

    #1950908

    Nice - but unfortunately won't work on SQL 2014. The column encryption views weren't introduced until SQL 2016.

    Sue

  • July 14, 2017 at 6:13 am

    #1950911

    Sue_H - Friday, July 14, 2017 6:02 AM

    Nice - but unfortunately won't work on SQL 2014. The column encryption views weren't introduced until SQL 2016.

    Sue

    Because they're specific for the new Always Encrypted feature, not the older symmetric key/asymmetric key encryption via the EncryptBy* functions

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 14, 2017 at 7:00 am

    #1950925

    my error, completely missed the fact its the 2014 forum section, bozo :hehe:

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply