还原SQL Server数据库 - 主键未打开

I have to make a local copy of a remote SQL Server database. I did this by using Tasks > Backup from Management Studio. I then locally restored the backup, which seems to have everything -- tables, users, symmetric key, and certificate.

我必须制作远程SQL Server数据库的本地副本。我是通过使用管理工作室中的任务>备份来完成的。然后我本地恢复了备份，它似乎拥有一切 - 表，用户，对称密钥和证书。

When I try to perform a select that requires me to open the symmetric key and decrypt by the certificate, I get this error:

当我尝试执行需要打开对称密钥并通过证书解密的选择时，我收到此错误：

Please create a master key in the database or open the master key in the session before performing this operation.

Why am I being asked for this, and why doesn't it open automatically like it does on the remote server?

为什么我被问到这个问题，为什么它不会像在远程服务器上那样自动打开？

I've tried changing the master key, but without the original password, I can't do much.

我已经尝试更改主密钥，但没有原始密码，我做不了多少。

4 个解决方案

#1

The problem is the SMK has changed (since the machine has changed). There's an article explaining it here. Just export and import the SMK -- bearing in mind that any encrypted data in your copied-to system will be unreadable.

问题是SMK已经改变（因为机器已经改变）。这里有一篇文章解释它。只需导出和导入SMK - 请记住，复制到系统中的任何加密数据都是不可读的。

MSDN articles:

MSDN文章：

Backing up the SMK
备份SMK
Restoring the SMK
恢复SMK

#2

Here is a good article specifically on migrating a database that includes encryption:

这是一篇专门关于迁移包含加密的数据库的文章：

http://www.sql-server-performance.com/2009/migrating-databases-checklist-part3/3/

But in short, you need to know the original password in order to move it.

但简而言之，您需要知道原始密码才能移动它。

You can backup and restore the key (i.e. replicate as you mention) but you'll need access to the remote server, ability to create backup, or copy of backup with original passwords:

您可以备份和恢复密钥（即，如您所述进行复制），但您需要访问远程服务器，创建备份或使用原始密码复制备份：

http://msdn.microsoft.com/en-us/library/ff848768.aspx

This forum conversation may also prove useful for insight:

此论坛对话也可能对洞察力有用：

http://www.sqlservercentral.com/Forums/Topic775644-146-1.aspx

#3

Same problem solved without data loss - if you have the password creating the master key

在没有数据丢失的情况下解决了同样的问题 - 如果您有密码创建主密钥

The master key in the database was encrypted by the Service Master Key. It automatically opened when you used the master key.

数据库中的主密钥由服务主密钥加密。使用主密钥时会自动打开。

Now the Service Master Key is unable to open the master key, and SQL is prompting you to "OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'" manually or create the master key.

现在，服务主密钥无法打开主密钥，并且SQL正在提示您“手动打开主密钥解密密码='密码'”或创建主密钥。

Adding encryption by the NEW SERVER's Service Master Key will again allow the automatic opening of the master key.

通过NEW SERVER的服务主密钥添加加密将再次允许自动打开主密钥。

Kudos to the guys that answered the question in the link

感谢那些在链接中回答问题的人

#4

You cannot bypass the encryption. See this link for the official word from Microsoft.

您无法绕过加密。请参阅此链接以获取Microsoft的官方消息。

#1