asked
The certificate, asymmetric key, or private key file does not exist or has invalid format.
New to TDE. We have a production database that we want to distribute to our developers. I keep getting this error when trying to create a certificate from an existing file. Here is the sequence of events: On the production database > Create a master key CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeyPassword' Create / backup the certificate for encryption CREATE CERTIFICATE My_TDE_Cert WITH SUBJECT = 'Test Certificate' GO BACKUP CERTIFICATE My_TDE_Cert TO FILE = 'C:\SQL_Temp\My_Cert_File.cer' WITH PRIVATE KEY (FILE = 'C:\SQL_Temp\My_Cert_Key.pvk' , ENCRYPTION BY PASSWORD = 'CertificatePassword' ) GO Encrypt and backup the database CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE My_TDE_Cert GO ALTER DATABASE Test_DB SET ENCRYPTION ON GO BACKUP DATABASE Test_DB TO DISK = N'C:\SQL_Temp\Test_DB.bak' WITH NOFORMAT, INIT, COPY_ONLY, NAME = 'Test_DB TDE TEST - Full Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 DECLARE @backupSetId int select @backupSetId = position from msdb..backupset where database_name = 'Test_DB' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='Test_DB' ) if @backupSetId is null begin raiserror('Verify failed', 16, 1) end RESTORE VERIFYONLY FROM DISK = N'C:\SQL_Temp\Test_DB.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND GO Then I copy the backup, Cert file and Cert key to the local machine. On the local machine, I create a master key using the same command and password above. (I know that it should not be necessary to use the same password.) Everything works fine up to this point. I then try to create a certificate using the original files using the command below. CREATE CERTIFICATE My_TDE_Cert FROM FILE = 'C:\SQL_Temp\My_Cert_File.cer' WITH PRIVATE KEY (FILE = 'C:\SQL_Temp\My_Cert_Key.pvk' , ENCRYPTION BY PASSWORD = 'CertificatePassword' ) GO Upon which, I get the error stated. The 'CertificatePassword' is the same for both scripts. File access does not appear to be an issue as I can use first create / backup cert script to create a different cert at that location. What am I missing????
Okay, So I completely missed the syntax error!!! The command should have been CREATE CERTIFICATE... WITH PRIVATE KEY ... DECRYPTION BY, not ENCRYPTION BY. Correcting that, everything works. So, my bad for not catching the syntax error. And MS bad for throughing a misleading error message.
Update: STILL the same error.... Double checked permissions between servers, file path identical… = same error Created a new SQL instance on same machine as original (machine/instance used to create the certificate). Double checked that both instances are using the same account (
serverSQL@ourDomain.local) for all SQL services. Both instances now referencing the exact same path on the exact same machine… = same error Ran the creation script on the same machine/instance used to create the original certificate, but changed the certificate name. (script below). = same error CREATE CERTIFICATE My_TDE_Cert_2 FROM FILE = 'C:\SQL_Temp\My_Cert_File.cer' WITH PRIVATE KEY (FILE = 'C:\SQL_Temp\My_Cert_Key.pvk' , ENCRYPTION BY PASSWORD = 'CertificatePassword' ) GO In all cases above, I had started SSMS with "Run as administrator". So, I think I can safely rule out a permissions issue. It HAS to be some issue with the Cert and / or Key, but I have no idea what is happening. I'm just glad this is happening in the testing phase, instead of when we actually need to restore our live encrypted database to another server. Does anybody have ANY ideas?????????
+1. Thanks for letting us know. I was least expecting a wrong command and though that everything is correct, but indeed is a good lesson to learn.
Did you copy the backup of the server certificate and the private key file from the source server to the same location on the destination server? Moreover, I think this could be a permission issue. The copied certificate and private keys would have been restricted to access at the creator instance. So your instance at local machine may not be able to access those files. Please try giving explicit permissions to those files and let us know if it worked or not.
I have encountered this problem several times due to the system running out of disk space on C:\. Specifically I have run into the problem with creating asymmetric keys from assemblies for creating trust relationships to allow "unsafe" code. I suspect this would occur or certificates and symmetric keys also because they still need to be stored. Important: Even after the space issue is resolved you need to restart the server. I think this has something to do with the CLR itself.
1 Person is following this question.
