Sql-Server

證書、非對稱密鑰或私鑰文件無效或不存在

  • May 23, 2018

我正在嘗試將數據庫從另一台伺服器還原到我的本地主機伺服器。該數據庫已加密(bak 文件),所以我必須對其進行解密,這很好,我擁有所有證書文件和密碼,但是出於某種原因,我仍然收到以下錯誤:

The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.

這是我正在執行的程式碼:

     CREATE CERTIFICATE [Certificate1] 
FROM FILE = 'C:\Location of the certs'
WITH PRIVATE KEY ( 
   FILE = 'C:\Location of the certs' ,   
   DECRYPTION BY PASSWORD = 'password'
);

PS。我不是備份和恢復方面的專家。

以下是將加密數據庫還原到不同伺服器(localhost)的步驟。

1.創建證書:

CREATE CERTIFICATE [CertficateName] 
FROM FILE = 'C:\FolderName\NameOfCert.cer'
WITH PRIVATE KEY ( 
   FILE = 'C:\FolderName\NameOfCertKey.key' ,   
   DECRYPTION BY PASSWORD = 'YourPassword'
);
USE Master ;
Open Master Key Decryption by password = 'YourPassword'
Backup master key to file = 'C:\SQL FodlerName\MasterKeyName.key'
       ENCRYPTION BY PASSWORD = 'YourPassword';
   GO

3.恢復萬能鑰匙

Use master 
   restore master key
   FROM FILE = 'C:\FolderName\MasterKeyName.key'
   DECRYPTION BY PASSWORD = 'YourPassword'
   ENCRYPTION BY PASSWORD = 'YourPassword'

4.這是你的最後一步,在這個階段要小心,因為我花了一段時間才知道每個命令都需要單獨執行:

Alter Database [DatabaseName]
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

接下來單獨執行

USE MASTER
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourPassword';

下一個命令單獨執行

RESTORE DATABASE [DatabaseName] FROM DISK = 'C:\Folder\FULL\NameoftheBakFilethat ourAreRestoring.BAK' 
WITH Replace , STATS = 5 

不要忘記將數據庫設置回多使用者模式。

引用自:https://dba.stackexchange.com/questions/207168