SQL Server 2008 物理服务器切换时打开主密钥错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2017865/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server 2008 Open Master Key error upon physical server change over
提问by Tomasz Iniewicz
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.
我将一个 SQL Server 数据库从一个系统复制到另一个相同的设置，但完全不同的物理机。我使用Norton Ghost并手动恢复文件，例如重新安装SQL Server 2008 Express后在c:\Program Files中找到的整个SQL Server 2008文件夹。
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:
我的一个数据库在其多个表、列上启用了 AES_256 加密。我重置了我的 IIS7 并尝试运行访问数据库的应用程序，在检索数据时，我收到此错误：
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.
“/”应用程序中的服务器错误。执行此操作前，请在数据库中创建主密钥或在会话中打开主密钥。说明：在执行当前 Web 请求期间发生未处理的异常。请查看堆栈跟踪以获取有关错误及其在代码中的来源的更多信息。
异常详细信息： System.Data.SqlClient.SqlException: 请在数据库中创建主密钥或在会话中打开主密钥，然后再执行此操作。
源错误：
执行当前 Web 请求期间生成了未处理的异常。可以使用下面的异常堆栈跟踪来识别有关异常来源和位置的信息。
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.
我已经阅读并找到了一些关于 AES 加密如何与机器密钥相关联的链接，但我不知道如何将其复制到新系统。或者，甚至情况并非如此。
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.
注意：我尝试删除对称密钥、证书和主密钥并重新创建它们。这消除了错误，但通过 AES_256 加密的数据没有显示。但是，未加密的列会这样做。
Any help would be much appreciated. Thanks in advance!
任何帮助将非常感激。提前致谢！
回答by Sam
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).
数据库主密钥使用服务器主密钥加密，该密钥特定于安装 SQL Server 的计算机。当您将数据库移动到另一台服务器时，您将失去自动解密和打开数据库主密钥的能力，因为本地服务器密钥很可能会有所不同。如果您无法解密数据库主密钥，则无法解密依赖于它的任何其他内容（证书、对称密钥等）。
Basically, you want to re-encrypt the database master key against the new server key, which can be done with this script (using admin privileges):
基本上，您希望针对新的服务器密钥重新加密数据库主密钥，这可以使用此脚本完成（使用管理员权限）：
-- 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.
如果您没有备份或密码，那么我不确定您是否能够在新服务器上恢复加密数据，因为您必须删除并使用新密码重新创建数据库主密钥，这将杀死任何相关的密钥和数据。
回答by wruckie
I just had a similar situation, an server rebuild after the OS drives died. I reinstalled SQL and reconnected it to all my old databases on the untouched data drives. Everything worked except for my encrypted columns. But my issue was that the master service key was hosed. I was able to repair my master service key by going back to the same domain credentialthat had been my SQL server service account before the move.
我刚刚遇到了类似的情况，在操作系统驱动器死机后重建服务器。我重新安装了 SQL 并将其重新连接到我未受影响的数据驱动器上的所有旧数据库。除了我的加密列之外，一切正常。但我的问题是主服务密钥被冲洗掉了。我能够通过返回到移动前作为我的 SQL Server 服务帐户的相同域凭据来修复我的主服务密钥。
This articlegave me the fix (kudos to Matt Bowlerfor his excellent article). I knew the local machine key had changed, but my salvation was that I could use the same service account.
这篇文章给了我解决方案（感谢Matt Bowler的出色文章）。我知道本地机器密钥已更改，但我的救赎是我可以使用相同的服务帐户。
Service Master Key: At the top of the key hierarchy is the Service Master Key. There is one per SQL Server instance, it is a symmetric key, and it is stored in the master database. Used to encrypt Database Master Keys, Linked Server passwords and Credentials it is generated at first SQL Server startup.
服务主密钥：在密钥层次结构的顶部是服务主密钥。每个 SQL Server 实例有一个，它是一个对称密钥，存储在 master 数据库中。用于加密数据库主密钥、链接服务器密码和凭据，它在第一次 SQL Server 启动时生成。
There are no user configurable passwords associated with this key – it is encrypted by the SQL Server service account and the local machine key. On startup SQL Server can open the Service Master Key with either of these decryptions. If one of them fails – SQL Server will use the other one and ‘fix' the failed decryption(if both fail – SQL Server will error). This is to account for situations like clusters where the local machine key will be different after a failover. This is also one reason why service accounts should be changed using SQL Server Configuration Manager – because then the Service Master Key encryption is regenerated correctly.
没有与此密钥关联的用户可配置密码——它由 SQL Server 服务帐户和本地计算机密钥加密。在启动时，SQL Server 可以使用这些解密之一打开服务主密钥。如果其中一个失败 – SQL Server 将使用另一个并“修复”失败的解密（如果两者都失败 – SQL Server 将出错）。这是为了解决类似集群的情况，其中本地机器密钥在故障转移后会有所不同。这也是应该使用 SQL Server 配置管理器更改服务帐户的原因之一——因为这样可以正确重新生成服务主密钥加密。
