November 23, 2017 at 8:41 am
Hi there,
I'm struggling with a little query here. I'm much more a DB admin than programmer, so I really got lots of stuff I do not know regarding building my own querys - sorry for that 😉
I got the appointment of checking several instances (each hosting 20 to 30 databases), because the higher ups are afraid someone "could have implemented" some kind of encryption (TDE etc). To check it alltogether I decided to check for existense of database master keys, as DMKs could be created in every database. I could go through all of the databases individually with the following query (as a DMK is always a symmetric key): SELECT * FROM symmetric_keys
I tried the query together with "sp_MSforeachdb", but that does not go through all databases (not sure if it is because of the sys table), only for the db in which context I'm at the moment:
DECLARE @command varchar(1000)
SELECT @command = 'SELECT * FROM symmetric_keys'
EXEC sp_MSforeachdb @command
Any ideas on how to solve this?
Many thanks in advance!!
Greetings from germany
Stefan
November 23, 2017 at 8:53 am
Stefan
It works for me. Note my modifications to your code:DECLARE @command varchar(1000)
SELECT @command = 'SELECT ''?'', * FROM sys.symmetric_keys'
EXEC sp_MSforeachdb @command
John
November 23, 2017 at 8:53 am
sp_msforeachDB doesn't work quite the way you think.
What you need to do is to alter your @command string to either change database specifically, or to change the @command string so that it selects from the three-part-named table.
So, one of the following two lines:SELECT @command = 'SELECT * FROM [?].sys.symmetric_keys';
SELECT @command = 'USE [?]; SELECT * FROM sys.symmetric_keys';
If you want to see what your command is actually doing, try this:
SELECT @command = 'SELECT ''?'', db_name(), * FROM sys.symmetric_keys';
Thomas Rushton
blog: https://thelonedba.wordpress.com
November 24, 2017 at 12:21 pm
I suggest you do it this way, which will make the output easier to use (at least for me):
DECLARE @command varchar(1000)
SELECT @command = '
PRINT ''?'';
IF EXISTS(SELECT 1 FROM [?].sys.symmetric_keys)
BEGIN
USE [?];
PRINT '' Key(s) found!'';
SELECT ''?'' AS db_name, *
FROM sys.symmetric_keys
END
'
EXEC sp_MSforeachdb @command
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 28, 2017 at 2:41 am
ScottPletcher - Friday, November 24, 2017 12:21 PMI suggest you do it this way, which will make the output easier to use (at least for me):
DECLARE @command varchar(1000)
SELECT @command = '
PRINT ''?'';
IF EXISTS(SELECT 1 FROM [?].sys.symmetric_keys)
BEGIN
USE [?];
PRINT '' Key(s) found!'';
SELECT ''?'' AS db_name, *
FROM sys.symmetric_keys
END
'EXEC sp_MSforeachdb @command
Thank you very much, that really really helps! 🙂
November 28, 2017 at 9:00 am
Checking for symmetric keys won't tell you if a database has been TDE encrypted, though.
A query to check for databases that are TDE'ed you could use is:USE [MASTER];
GO
SELECT DB.NAME
, DEK.ENCRYPTION_STATE
, DEK.SET_DATE
, DEK.MODIFY_DATE
, DEK.OPENED_DATE
, DEK.ENCRYPTOR_THUMBPRINT
FROM SYS.DATABASES AS DB
LEFT JOIN SYS.DM_DATABASE_ENCRYPTION_KEYS AS DEK
ON DB.DATABASE_ID = DEK.DATABASE_ID
ORDER BY DB.NAME;
Set_date and Modify_date should be the same, Opened_date should be the last time your server was restarted (or the database in question started up.)
November 29, 2017 at 1:49 am
I do know how to check for TDE in general (could check just check "select * from sys.dm_database_encryption_keys" for that) , but my task was to check for DMK to make sure no TDE, Cell encryption etc. could be implemented. If some DMK were found, I would check further in a second step.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply