locked
Seeking query statement to verify TDE is enabled on a specific User DB. The one I'm using, shows too much. RRS feed

  • Question

  • Hi Experts,

    I have one User DB that I need to demonstrate to management that it's TDE-encrypted.  The easier the format, the better.  Right now, I'm using this:

    --verify encryption
    USE <my database>;
    GO
     
    SELECT
        DB.NAME,
        DB.IS_ENCRYPTED,
        DM.ENCRYPTION_STATE,
        DM.PERCENT_COMPLETE,
        DM.KEY_ALGORITHM,
        DM.KEY_LENGTH
    FROM
        SYS.DATABASES DB
        LEFT OUTER JOIN SYS.DM_DATABASE_ENCRYPTION_KEYS DM
            ON DB.DATABASE_ID = DM.DATABASE_ID;
    GO

    It shows the system databases and too much.  I just need a filtered down, "hey, here's the user DB and yep, it's encrypted....plain as day...".

    Anyone have anything I can use?   Thank you so much in advance!



    • Edited by guesthost Tuesday, March 28, 2017 6:42 PM
    Monday, March 27, 2017 4:19 PM

Answers

  • How about this:

     
    SELECT 
        DB.NAME,
        CASE
    		WHEN DB.IS_ENCRYPTED = 1 then 'YES'
        ELSE 'NO'
        END AS 'IS_ENCRYPTED' 
    FROM 
        SYS.DATABASES DB
        LEFT OUTER JOIN SYS.DM_DATABASE_ENCRYPTION_KEYS DM
        ON DB.DATABASE_ID = DM.DATABASE_ID
    WHERE DB.NAME = '<your_database>' GO

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Monday, March 27, 2017 5:59 PM
    • Marked as answer by guesthost Tuesday, March 28, 2017 1:04 PM
    Monday, March 27, 2017 5:07 PM

All replies

  • How about this:

     
    SELECT 
        DB.NAME,
        CASE
    		WHEN DB.IS_ENCRYPTED = 1 then 'YES'
        ELSE 'NO'
        END AS 'IS_ENCRYPTED' 
    FROM 
        SYS.DATABASES DB
        LEFT OUTER JOIN SYS.DM_DATABASE_ENCRYPTION_KEYS DM
        ON DB.DATABASE_ID = DM.DATABASE_ID
    WHERE DB.NAME = '<your_database>' GO

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Monday, March 27, 2017 5:59 PM
    • Marked as answer by guesthost Tuesday, March 28, 2017 1:04 PM
    Monday, March 27, 2017 5:07 PM
  • Hi vciAdmin,

    >>USE backgroundcheck;

    Is that your user DB? If so, you would only need a little bit modification:
    USE backgroundcheck; 
    GO
     
    SELECT 
        DB.NAME,
        DB.IS_ENCRYPTED,
        DM.ENCRYPTION_STATE,
        DM.PERCENT_COMPLETE,
        DM.KEY_ALGORITHM,
        DM.KEY_LENGTH
    FROM 
        SYS.DATABASES DB
        LEFT OUTER JOIN SYS.DM_DATABASE_ENCRYPTION_KEYS DM
            ON DB.DATABASE_ID = DM.DATABASE_ID
    where DB.DATABASE_ID = DB_ID()
    GO
     
    


    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by guesthost Tuesday, March 28, 2017 1:03 PM
    • Unmarked as answer by guesthost Tuesday, March 28, 2017 1:04 PM
    Tuesday, March 28, 2017 6:40 AM