Encryption on?

  • Correct me if I'm wrong, If I want to tell if encryption is turn on (Data written to tables, backups, etc.) in any of my databases I can just query "sys.dm_db_persisted_sku_features" correct.

  • If you're asking if Transparent Data Encryption is enabled, you can query sys.databases:

    select name, is_encrypted

    from sys.databases

    where database_id > 4

    ----------------------------------------------------------------------------
    Sacramento SQL Server users group - http://sac.sqlpass.org
    Follow me on Twitter - @SQLDCH
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.
  • Have a table that isn't compressed, put in a CHAR(20), insert a bunch of rows of 'aaaaaaaaaaaaaaaaaaaa'. Checkpoint the write.

    Open up a file (.mdf or backup file) in a hex editor like HxD[/url].

    If you see 'aaaaaaaaaaaaaaaaaaaa' in it, it's not encrypted. If you see other readable data (names, addresses, whatever other data you have) in it, it's not encrypted.

    If you see random garbage (statistical character analysis shows roughly even numbers of every character), then it's at least compressed, and may or may not be encrypted, as both encryption and compression ideally result in data that is indistinguishable from random noise.

  • If you have data that is encrypted at a cell level (symmetric/asymmetric encryption), you can't query to determine the data is encrypted.

Viewing 4 posts - 1 through 3 (of 3 total)

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