SQL Server 2005 - 在不同服务器上恢复加密数据库
我已经备份了加密的数据库（对称密钥/证书）并且 在不同的服务器上恢复它。
不幸的是，我们在解密时遇到了问题......希望 有人可以帮忙。
在恢复的数据库中，我可以看到对称密钥和证书 SSMS，但是当我尝试使用证书打开密钥时（打开对称 密钥 KeyA 通过证书 CertB 解密）我得到 以下非常具有描述性错误：
消息 15466，级别 16，状态 1，第 1 行 解密过程中发生错误。
有任何想法吗？
提前致谢。
I have backed up an encrypted DB (symmetric key/certificate) and
restored it on a different server.
Unfortuantely we're having problems with the decryption... hoping
someone can help.
In the restored db, I can see the Symmetric Key and the Certificate in
SSMS, but when I try to Open the key using the cert ( open symmetric
key KeyA decryption by certificate CertB )I get the
following very descriptive error:
Msg 15466, Level 16, State 1, Line 1
An error occurred during decryption.
Any ideas?
Thanks in advance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
“当您恢复使用加密功能的数据库时，您只需要注意一件事 - 如果数据库主密钥 (DbMK) 需要服务主密钥 (SMK) 加密，您需要重新生成此加密，请注意，此加密是在您创建 DbMK 时默认进行的，但如果您想要更严格地控制对加密数据的访问，则可能会故意删除它，如果您确实有此类 SMK 加密。对于 DbMK，重新生成它的步骤如下：
通过密码 = '密码' 打开主密钥解密
更改主密钥 通过服务主密钥添加加密
关闭主密钥
就是这样 - 数据库加密功能现在应该像进行备份时一样工作。 另请注意，无论是在进行备份的服务器上还是在其他地方恢复数据库都没有关系。 对于此过程唯一重要的是您知道保护 DbMK 的密码之一”
http://blogs.msdn.com/lcris/archive/2007/11/16/sql-server-2005-restoring-the-backup-of-a-database-that-uses-encryption.aspx answers this:
"When you restore a database that uses encryption features, there is only one thing you need to take care off - if the database master key (DbMK) needs a service master key (SMK) encryption, you need to regenerate this encryption. Note that this encryption is made by default when you create the DbMK, but it may be intentionally dropped, if you want tighter control of access to the encrypted data. Anyway, if you did have such SMK encryption for the DbMK, the steps to regenerate it are the following:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
CLOSE MASTER KEY
That's it - the database encryption features should now work as when the backup was taken. Also note that it doesn't matter if you restore the database on the server where the backup was taken or elsewhere. The only thing that matters for this procedure is that you know one of the passwords protecting the DbMK "
主密钥由源服务器上的服务主密钥解密，我们在目标服务器上使用密码解密主密钥。 我将主密钥更改为由服务主密钥解密，现在可以使用了。
The master key was decrypted by the service master key on the source server and we were decrypting the master key with password on the destination. I altered the master key to be decrypted by the service master key and it's working now.
您可能遇到的问题是服务器的数据库主密钥不同。 据我了解，其他密钥都是基于此的，在尝试解密数据时可能会导致问题。 查看加密层次结构，了解相关步骤的说明数据加密。
我希望这个答案能有所帮助，并且不会太偏离轨道。 :)
The problem you are probably experiencing is that the Database Master Key for the servers is different. To my understanding the other keys are based off of this and it could cause problems when trying to decrypt the data. Check out the encryption hierarchy for a description of the steps that go into data encryption.
I hope this answer helps and isn't too off-track. :)
http: //social.msdn.microsoft.com/forums/en-US/sqlsecurity/thread/34c9c35c-2d08-4873-abfd-aae40240dfe7/?prof=required
该链接对我有用，请按照 2 个链接进行备份/restore
您可以使用 UNC 从目标服务器进行恢复，而不必复制文件。
http://social.msdn.microsoft.com/forums/en-US/sqlsecurity/thread/34c9c35c-2d08-4873-abfd-aae40240dfe7/?prof=required
That link worked for me, follow the 2 links to backup/restore
You can do the restore from the destination server using a UNC, you do not have to copy the file.