October 30, 2013 at 1:16 pm
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.
October 30, 2013 at 2:06 pm
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
----------------------------------------------------------------------------
October 31, 2013 at 12:22 pm
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.
October 31, 2013 at 12:25 pm
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