![]() The post Exchanging SQL Certificates with different service accounts can be tricky appeared first on SQL Server with Mr. If you change the permissions so that the second SQL Account can read the certificate backup file you created then the restore will work. So if you need to restore the certificate using the CREATE CERTIFICATE syntax shown in the example you’ll get an error. By default SQL Server will make it so that the backup of the certificate is only available to the account that is running the instance that creates the certificate backup. ![]() The answer is depressingly simple, NTFS permissions. Now as we can see, the creation of the certificate failed, but only when running each server under its own service account. –The master key file does not exist or has invalid format. RESTORE MASTER KEY FROM FILE = ‘\\FileShare\DatabaseMasterKey_Master.key’ĭECRYPTION BY PASSWORD = ‘RandomEncryptionPwd’ĮNCRYPTION BY PASSWORD = ‘RandomEncryptionPwd’ * Try restoring the master key instead */ ![]() –The certificate, asymmetric key, or private key file is not valid or does not exist or you do not have permissions for it. WITH PRIVATE KEY (FILE = ‘\\FileShare\BackupCert.pvk’,ĭECRYPTION BY PASSWORD = ‘RandomEncryptionPwd’) WITH PRIVATE KEY (FILE = ‘\\FileShareBackupCert.pvk’,ĮNCRYPTION BY PASSWORD = ‘RandomEncryptionPwd’) ĬREATE CERTIFICATE BackupCert FROM FILE = ‘\\FileShare\BackupCert.cer’ WITH SUBJECT = ‘Backup Encryption Certificate’ īACKUP MASTER KEY TO FILE = ‘\\FileShare\DatabaseMasterKey_Master.key’ĮNCRYPTION BY PASSWORD = ‘SomeRandomPwd’ īACKUP CERTIFICATE BackupCert TO FILE = ‘\\FileShare\BackupCert.cer’ * Create the certificate to be used for backups */ So let’s review the code that Nic posted to Stack Exchange earlier today.ĬREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘SomeRandomSecureString’ The problem that people will usually run into is that all the example code assumes that all the instances are running under the same service account, but this isn’t always the case in the real world. Setting up Transparent Data Encryption, or Backup Encryption, or basically anything else where you need to restore a certificate to another SQL Server instance can get tricky as soon as you run the instances under different service accounts.
0 Comments
Leave a Reply. |