locked
TDE on SQL Server 2012 Database RRS feed

  • Question

  • Hi,

    I implemented TDE on couple of databases on SQL Server 2012 Test lab.

    Now how can i find out which database is encrypted by which certificate?

    I used one certificate for few databases and created separate certificate for few databases. 

     

    Tuesday, June 14, 2016 8:32 PM

Answers

  • Can you please tell me?

    While create certificate on Source Server i created certificate name like as :

    Use master

    CREATE CERTIFICATE My_TDECert WITH SUBJECT= 'Testing TDE Encryption on ABCDDB';

    GO

     

    Before restoring database on Destination server i used below code:

    CREATE CERTIFICATE TDECert2

    FROM FILE = 'S:\My_TDECert.cer'

    WITH PRIVATE KEY (FILE = 'S:\MY Cert_Key.pvl',

    DECRYPTION BY PASSWORD = 'Pass1234567890')

    Here in both the scripts Certificate name is different My_TDECert,TDECert2).

    But it worked fine. How it is possible and Actually what below code does:

    CREATE CERTIFICATE TDECert2

    FROM FILE 'S:\My_TDECert.cer'




    The key here is not the certificate name but the Thumb Print.

    So, even if the Certificate names are different the THUMBPRINT will be same as long as it is recreated from the same cert and private key.

    Select  thumbprint,* from sys.certificates 

    check the thumb print on both the servers for the certificate.. they would same regardless of the difference in name.


    Hope it Helps!!

    Wednesday, June 29, 2016 5:18 PM

All replies

  • select B.Name as [Certificate],db_name(database_id) from sys.dm_database_encryption_keys a inner join sys.certificates B on A.encryptor_thumbprint=B.thumbprint
    


    Hope it Helps!!

    Tuesday, June 14, 2016 8:53 PM
  • Can you please tell me?

    While create certificate on Source Server i created certificate name like as :

    Use master

    CREATE CERTIFICATE My_TDECert WITH SUBJECT= 'Testing TDE Encryption on ABCDDB';

    GO

     

    Before restoring database on Destination server i used below code:

    CREATE CERTIFICATE TDECert2

    FROM FILE = 'S:\My_TDECert.cer'

    WITH PRIVATE KEY (FILE = 'S:\MY Cert_Key.pvl',

    DECRYPTION BY PASSWORD = 'Pass1234567890')

    Here in both the scripts Certificate name is different My_TDECert,TDECert2).

    But it worked fine. How it is possible and Actually what below code does:

    CREATE CERTIFICATE TDECert2

    FROM FILE 'S:\My_TDECert.cer'




    • Edited by VijayKSQL Tuesday, June 14, 2016 9:14 PM add
    Tuesday, June 14, 2016 9:13 PM
  • Try to run the below code and share your output:
    use master;goselect    database_name = d.name,    dek.encryptor_type,    cert_name = c.namefrom sys.dm_database_encryption_keys dekleft join sys.certificates con dek.encryptor_thumbprint = c.thumbprintinner join sys.databases don dek.database_id = d.database_id;

    Thursday, June 16, 2016 5:28 AM
  • Here is the Output.

    database_name encryptor_type cert_name
    tempdb ASYMMETRIC KEY NULL
    TestDb CERTIFICATE Beprd_TDECert

    Thursday, June 16, 2016 2:38 PM
  • Can you please tell me?

    While create certificate on Source Server i created certificate name like as :

    Use master

    CREATE CERTIFICATE My_TDECert WITH SUBJECT= 'Testing TDE Encryption on ABCDDB';

    GO

     

    Before restoring database on Destination server i used below code:

    CREATE CERTIFICATE TDECert2

    FROM FILE = 'S:\My_TDECert.cer'

    WITH PRIVATE KEY (FILE = 'S:\MY Cert_Key.pvl',

    DECRYPTION BY PASSWORD = 'Pass1234567890')

    Here in both the scripts Certificate name is different My_TDECert,TDECert2).

    But it worked fine. How it is possible and Actually what below code does:

    CREATE CERTIFICATE TDECert2

    FROM FILE 'S:\My_TDECert.cer'




    The key here is not the certificate name but the Thumb Print.

    So, even if the Certificate names are different the THUMBPRINT will be same as long as it is recreated from the same cert and private key.

    Select  thumbprint,* from sys.certificates 

    check the thumb print on both the servers for the certificate.. they would same regardless of the difference in name.


    Hope it Helps!!

    Wednesday, June 29, 2016 5:18 PM