使用不同的服務帳戶時向 SQL Server 頒發恢復證書

我正在嘗試在 SQL 2017 中配置備份加密，但遇到了一些問題。

我在一台機器上創建主密鑰和證書沒有問題。將證書安裝在另一台機器上並讀取備份也沒有問題，但是，如果另一台機器執行的服務帳戶與創建證書的服務帳戶不同，我有一個問題。

以下是我正在採取的步驟（我也嘗試恢復主密鑰，但這也會引發錯誤）：

/* Server 1 */
/* Create the master key */
USE master;  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SomeRandomSecureString';  
GO 


/* Create the certificate to be used for backups */
CREATE CERTIFICATE BackupCert  
  WITH SUBJECT = 'Backup Encryption Certificate';  
GO

/* Backup the master key */
BACKUP MASTER KEY TO FILE = '\\FileShare\DatabaseMasterKey_Master.key'   
   ENCRYPTION BY PASSWORD = 'SomeRandomPwd';

BACKUP CERTIFICATE BackupCert TO FILE = '\\FileShare\BackupCert.cer'
 WITH PRIVATE KEY (FILE = '\\FileShareBackupCert.pvk',   
    ENCRYPTION BY PASSWORD = 'RandomEncryptionPwd');   

GO 

/* Server 2 */
/* Create master key */
USE master;  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SomeRandomSecureString';  
GO 

/* Restore the cert */
CREATE CERTIFICATE BackupCert FROM FILE = '\\FileShare\BackupCert.cer'
 WITH PRIVATE KEY (FILE = '\\FileShare\BackupCert.pvk',   
    DECRYPTION BY PASSWORD = 'RandomEncryptionPwd');

--Msg 15208, Level 16, State 6, Line 32
--The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.

/* Try restoring the master key instead */
DROP MASTER KEY;

RESTORE MASTER KEY FROM FILE = '\\FileShare\DatabaseMasterKey_Master.key'   
   DECRYPTION BY PASSWORD = 'RandomEncryptionPwd'
   ENCRYPTION BY PASSWORD = 'RandomEncryptionPwd';

--Msg 15317, Level 16, State 2, Line 39
--The master key file does not exist or has invalid format.

我查看的參考資料試圖弄清楚這裡發生了什麼：

• https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-encryption?view=sql-server-2017
• https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/create-a-database-master-key?view=sql-server-2017
• https://docs.microsoft.com/en-us/sql/t-sql/statements/create-certificate-transact-sql?view=sql-server-2017
• https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/create-an-encrypted-backup?view=sql-server-2017

我還確保所有機器之間的 SMK 相同，但在恢復證書方面仍然沒有運氣。

我確信我在這裡做錯了什麼，但我無法弄清楚是什麼。

任何想法，將不勝感激。謝謝。

您需要修復證書上的 NTFS 權限。預設情況下，SQL 在創建備份時會設置 NTFS 權限，這樣只有執行 SQL 服務的帳戶才能讀取證書備份文件。

這就是為什麼它適用於單個帳戶，而不適用於兩個帳戶。

引用自：https://dba.stackexchange.com/questions/211777