SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Checking if any database objects are encrypted


Checking if any database objects are encrypted

Author
Message
Animal Magic
Animal Magic
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1610 Visits: 13731
Hi,

I have just been given a database to check over to see if we can get at all the data. The database has restored fine, so that's step 1 complete as there is no backup encryption.

Step 2 was to check the objects. I ran the following query

SELECT SCHEMA_NAME(sp.schema_id) AS [Schema],
sp.name AS [Name],
sp.object_id AS [ID],
sp.create_date AS [CreateDate],
sp.modify_date AS [DateLastModified],
CAST(CASE WHEN smsp.definition IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted]
FROM sys.all_objects sp LEFT JOIN sys.sql_modules smsp
ON smsp.object_id = sp.object_id
WHERE smsp.definition IS NULL -- This identifies an encrypted object
AND sp.type IN ('FN', 'IF', 'V', 'TR', 'PC', 'TF', 'P')
AND sp.is_ms_shipped = 0

SELECT sp.type, sp.type_desc
, COUNT(smsp.definition) AS UnencryptedObjects -- only non-null or unencrypted objects will be counted
, COUNT(*)-COUNT(smsp.definition) AS EncryptedObjects
, COUNT(*) AS Total
FROM sys.all_objects sp LEFT JOIN sys.sql_modules smsp
ON smsp.object_id = sp.object_id
WHERE sp.type IN ('FN', 'IF', 'V', 'TR', 'PC', 'TF', 'P')
AND sp.is_ms_shipped = 0
GROUP BY sp.type, sp.type_desc



....which returned 0 encrypted objects.

I noticed that tables are not included in the above query as they do not have a record in sys.sql_modules.

What checks, if any, do i need to run to check for encrypted data in tables without having to do a select top 1 from each table.

Regards

John
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87996 Visits: 45277
You can look for varbinary columns. It's not a perfect indicator as other things can require varbinary (eg files), but encrypted data must go into varbinary columns.

There's no specific flags for encrypted columns (and selecting from the tables won't necessarily tell you they're encrypted), because encryption is not a table/column attribute, it's just something you do to data before inserting it.

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


Animal Magic
Animal Magic
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1610 Visits: 13731
Cheers Gail. I did that check and there were only 4 in the whole DB (user tables only) and i can see all the data in those.

Thanks

John
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87996 Visits: 45277
Encryption doesn't stop you from seeing the data, just that you'll see binary values (0xA45B2DF999212...) rather than something you can understand

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


Animal Magic
Animal Magic
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1610 Visits: 13731
Sure, I meant that but wasn't clear in my response. :-)

John
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search