Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Checking if any database objects are encrypted Expand / Collapse
Author
Message
Posted Thursday, May 2, 2013 3:51 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, October 17, 2014 10:27 AM
Points: 999, Visits: 13,495
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
Post #1448742
Posted Thursday, May 2, 2013 4:06 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:48 AM
Points: 40,193, Visits: 36,598
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 2008, MVP
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

Post #1448747
Posted Thursday, May 2, 2013 4:08 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, October 17, 2014 10:27 AM
Points: 999, Visits: 13,495
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
Post #1448749
Posted Thursday, May 2, 2013 4:27 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:48 AM
Points: 40,193, Visits: 36,598
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 2008, MVP
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

Post #1448752
Posted Thursday, May 2, 2013 5:09 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, October 17, 2014 10:27 AM
Points: 999, Visits: 13,495
Sure, I meant that but wasn't clear in my response.

John
Post #1448762
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse