1

This question already has an answer here:

Doing my first backup and restore of someone else's sql server database. I have already done a successful backup and restore of one of the databases, but the other two are locked behind Transparent Data Encryption.

Is there a simple way of finding the certificates' files and just copy/pasting them to the new server's filepath and then restore? What's the proper direction for restoration from backup here?

If I do:

select * FROM [master].[sys].[certificates]

I get a bunch of rows that have ## before them. Are these significant? There's only one cert from that output that doesnt have ## and I'm not sure if that applied to a db that I'm not interested in...

marked as duplicate by Erik Darling, Joe Obbish, joanolo, Marco, James Anderson May 16 '17 at 6:52

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

0

Is there a simple way of finding the certificates' files

On source server:

declare 
    @SourceDb sysname = 'MyDb',
    @Thumbprint varbinary(20),
    @Cert sysname,
    @BackPath nvarchar(260) = 'C:\temp\Cert.bak',
    @KeyPath nvarchar(260) = 'C:\temp\Cert.key',
    @Pwd sysname = '&DoN0tUseTh1$',
    @SQL nvarchar(max)

select @Thumbprint = encryptor_thumbprint 
from sys.dm_database_encryption_keys 
where database_id = db_id(@SourceDb)

select @Cert = name from master.sys.certificates
where thumbprint = @Thumbprint

select @Cert '@Cert'

set @sql = 'BACKUP CERTIFICATE ' + quotename(@Cert) + ' TO FILE = '''+@BackPath+'''
WITH PRIVATE KEY (
FILE = '''+@KeyPath+''',
ENCRYPTION BY PASSWORD = '''+@Pwd+''');'
exec(@sql)

and then restore

On destination server:

declare 
    @DestDb sysname = 'MyDb',
    @Cert sysname = 'MyTDECert',
    @BackPath nvarchar(260) = 'C:\temp\Cert.bak',
    @KeyPath nvarchar(260) = 'C:\temp\Cert.key',
    @Pwd sysname = '&DoN0tUseTh1$',
    @SQL nvarchar(max)
set @sql = 'USE master; 
    CREATE MASTER KEY ENCRYPTION BY PASSWORD  = '''+@Pwd+''';'
exec(@sql)
if @@error<>0 print 
'You already have a DMK - do not drop it. 
Instead, BACKUP MASTER KEY TO FILE... 
and secure its backup.
Also BACKUP SERVICE MASTER KEY TO FILE...
and secure its backup.'

set @sql = 'USE master;
CREATE CERTIFICATE '+quotename(@Cert)+' 
FROM FILE = '''+@BackPath+'''
WITH PRIVATE KEY 
(FILE = '''+@KeyPath+''',
DECRYPTION BY PASSWORD = '''+@Pwd+''');'
exec(@sql)

What's the proper direction for restoration from backup here?

Now restore the database. After securing the keys and backups off of the 2 servers: Permanently shift+delete them (which in above examples were saved or copied to C:\temp).

Are these significant? There's only one cert from that output that doesnt have ## and I'm not sure if that applied to a db that I'm not interested in...

They are significant - leave them be :). A cert's thumbprint is used (in above code on the source server) to find the cert which encrypted the db. More than likely it is the cert without the double hashes in the name, but checking sys.dm_database_encryption_keys is better than guessing :).

  • Hi Bill, thanks for this. Is there a way to check and make sure I have CREATE CERTIFICATE permissions? I keep getting an error saying Cannot find the certificate ***, because it does not exist or you do not have permission. – AI52487963 May 16 '17 at 19:53
  • See Backup Certificate's permissions in BACKUP CERTIFICATE (Transact-SQL). Note that permissions can include "knowledge of the password that is used to encrypt the private key", per example B in same link. In other words, if a 3rd party encrypted the database using their certificate, you need to request the password from that 3rd party. I suspect most 3rd parties will refuse to divulge their password, because it is used to protect their proprietary interests. – Bill May 18 '17 at 4:43

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