I plan to migrate a SQL Server database to another server. There is a certificate listed under the Security > Certificates view in SSMS Object Explorer.

I ran the below query to check if database is encrypted and it is not...

select name, is_encrypted from sys.databases

How can I find if this certificate is used? And how do I ensure successful migration?

Thanks!

  • Hi there. I realize that I answered this almost 2 years after it was asked, but I was wondering if you had any feedback to provide. Thanks :). – Solomon Rutzky Jan 18 '17 at 18:19
  • Hi, the queries below helped me but none of my databases were encrypted despite the presence of certs. Hope this is of some help. – K09 Jan 31 '17 at 12:29

As far as I know, the places where a Certificate will be linked -- whether by id or thumbprint -- can be found via the following three queries:

SELECT N'key_encryptions' AS [Where?],
       crt.[name], crt.[principal_id], crt.[pvt_key_encryption_type_desc],
       N'---' AS [---], ke.*
FROM   sys.certificates crt
INNER JOIN sys.key_encryptions ke
        ON ke.[thumbprint] = crt.[thumbprint]

SELECT N'database_mirroring_endpoints' AS [Where?],
       crt.[name], crt.[principal_id], crt.[pvt_key_encryption_type_desc],
       N'---' AS [---], dme.*
FROM   sys.certificates crt
INNER JOIN sys.database_mirroring_endpoints dme
        ON dme.[certificate_id] = crt.[certificate_id]

SELECT N'service_broker_endpoints' AS [Where?],
       crt.[name], crt.[principal_id], crt.[pvt_key_encryption_type_desc],
       N'---' AS [---], sbe.*
FROM   sys.certificates crt
INNER JOIN sys.service_broker_endpoints sbe
        ON sbe.[certificate_id] = crt.[certificate_id]

AND, the query shown in this answer:

Find signed procedures, functions, triggers, assemblies and by which certificates / asymmetric keys


You can migrate the Certificate by backing it (and its private key or the Database Master Key) up to files and then restoring those files into the new Database. Please see the MSDN documentation for the following commands:

And, if any Certificates are ENCRYPTED_BY_MASTER_KEY (or whatever), then you will likely also need to look at:

This will provide details about the certificates encrypting any database that has TDE encryption enabled:

use master 
select 
    db_name(db.database_id) DatabaseName,
    c.name,
    * 
from sys.databases db
join sys.dm_database_encryption_keys dek
    on
    dek.database_id=db.database_id
join sys.certificates c
    on
    dek.encryptor_thumbprint=c.thumbprint
where
    db.is_encrypted=1

For all other column level encryption within a user database, backup the Database Master Key, migrate it to the other environment and restore it after restoring the user database.

  • The OP knows that the database is not encrypted. Specifically asking for dbname --> security --> Certificates – Kin Nov 18 '16 at 21:16

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.