SQL Server 2008在物理服务器更换时打开主密钥错误 [英] SQL Server 2008 Open Master Key error upon physical server change over

问题描述

我将SQL Server数据库从一个系统复制到下一个相同的设置，但完全不同的物理机。我使用Norton Ghost并手动恢复文件，例如，在重新安装SQL Server 2008 Express后，在c：\Program文件夹中找到的整个SQL Server 2008文件夹。

我的一个数据库中的一个表，列已经启用了AES_256加密。我重新设置了我的IIS7，并尝试运行访问数据库的应用程序，检索数据时，我收到这个错误：

服务器错误'/'申请。
请在
数据库中创建一个主密钥，或者在执行
操作之前，在
会话中打开主密钥。说明：在
执行当前Web请求期间发生未处理的
异常。
请查看堆栈跟踪以获取有关错误的更多
信息，并在
中发送代码。

异常详细信息：
System.Data.SqlClient.SqlException：
请在
数据库中创建一个主密钥，或者在执行
操作之前打开
会话中的主密钥。 p>

源错误：

在执行当前
期间生成了一个未处理的异常
网络请求。有关
起始点和异常位置的信息
可以使用以下异常
堆栈跟踪来识别。

$ b $我已经做了一些阅读，并发现了一些关于AES加密如何与机器密钥相关联的链接，但是如何将它复制到新的系统上是一个损失。或者甚至不是这样。

注意：我尝试删除对称密钥，证书和主密钥并重新创建它们。这可以摆脱错误，但比通过AES_256加密的数据不会出现。然而，未加密的列会执行。

任何帮助将不胜感激。提前致谢！

解决方案

使用服务器主密钥对数据库主密钥进行加密，这是特定于安装SQL Server的机器的服务器主密钥。将数据库移动到另一个服务器时，您将无法自动解密并打开数据库主密钥，因为本地服务器密钥很有可能不同。如果您无法解密数据库主密钥，则无法解密依赖于其的其他任何内容（证书，对称密钥等）。

基本上，您要根据新的服务器密钥重新加密数据库主密钥，可以使用此脚本（使用admin priviliges）完成数据库主密钥：

  - 重置服务器的数据库主密钥（如果数据库从另一个服务器上的备份还原）
 OPEN MASTER KEY DECRYPTION BY PASSWORD ='---您的数据库主密钥密码---'
 ALTER MASTER KEY添加加密服务主键
 GO 
  

请注意，创建数据库主密钥时，您应该始终提供密码，以便您可以在无法使用服务主密钥的情况下使用密码打开密钥 - 希望您将密码存储在某个地方！

或者，您可以还原数据库主密钥的备份 - 但是您需要创建一个f或目标服务器，而不是源服务器。

如果您没有备份或密码，那么我不确定您是否可以恢复新服务器上的加密数据，因为您将不得不删除并重新创建具有新密码的数据库主密钥，这将删除任何从属密钥和数据。

I copied a SQL Server database from one system to the next, identical setup, but completely different physical machine. I used Norton Ghost and recoverd files manually, for example, the entire SQL Server 2008 folder found in c:\Program Files after re-installing SQL Server 2008 Express.

One of my databases has AES_256 encryption enabled on a number of one of its tables, columns. I resetup my IIS7 and tried to run the app that access the database, upon retrieving the data, I get this error:

Server Error in '/' Application. Please create a master key in the database or open the master key in the session before performing this operation. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Please create a master key in the database or open the master key in the session before performing this operation.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

I've done some reading and found some links about how the AES encryption is linked with the machine key, but am at a loss as to how to copy it over to the new system. Or perhaps this even isn't the case.

NOTE: I've tried dropping the symmetric key, certificate and the master key and re-creating them. This gets rid of the error, but than the data that in encrypted via AES_256 does not show up. The columns that are NOT encrypted do, however.

Any help would be much appreciated. Thanks in advance!

解决方案

The database master key is encrypted using the server master key, which is specific to the machine where SQL Server is installed. When you move the database to another server, you lose the ability to automatically decrypt and open the database master key because the local server key will most likely be different. If you can't decrypt the database master key, you can't decrypt anything else that depends on it (certificates, symmetric keys, etc).

Basically you want to re-encrypt the database master key against the new server key, which can be done with this script (using admin priviliges):

-- Reset database master key for server (if database was restored from backups on another server)
OPEN MASTER KEY DECRYPTION BY PASSWORD = '---your database master key password---'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO

Note that when you create a database master key, you should always provide a password as well so that you can open the key using the password in the scenario where the service master key cannot be used - hopefully you've got that password stored somewhere!

Alternatively you can restore a backup of the database master key - but you need one that was created for the target server, not the source server.

If you haven't got either a backup or a password then I'm not sure you will be able to recover the encrypted data on the new server, as you will have to drop and recreate the database master key with a new password, which will kill any dependent keys and data.

这篇关于SQL Server 2008在物理服务器更换时打开主密钥错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！