Cannot find the certificate

Question

We get a T-SQL (SQL Server 2008 R2) error on BACKUP CERTIFICATE: ERROR_NUMBER 15151, SEVERITY 16, STATE 1, PROCEDURE -, LINE 8, MESSAGE: Cannot find the certificate 'certificate1', because it does not exist or you do not have permission.

We can see the certificate in master.sys.certificates.

Our pseudo-code:

• copy an unattached template_db to db1

• attach db1

• create certificate1 (in stored procedure in master db)

• generate @password

• CREATE DATABASE ENCRYPTION KEY … ENCRYPTION BY SERVER CERTIFICATE '+@certificate_name +… (in stored procedure in db1)

• turn on Transparent Database Encryption for db1 using certificate1. (N'ALTER DATABASE '+@db_name+N' SET ENCRYPTION ON')

• N’BACKUP CERTIFICATE '+@certificate_name+N' TO FILE = '''+@certificate_file_path+N''' WITH PRIVATE KEY ( FILE = '''+@private_key_file_path+N''', ENCRYPTION BY PASSWORD = '''+@password+N''''

To try to work-around the error, we tested three ways with the BACKUP CERTIFICATE code in a different databases each time, including db1 and master. All get the same error.

Any ideas? Thanks.

Answer 1

We tried again, running BACKUP CERTIFICATE in the master DB, and it worked this time.