SSMS 2016错误导入Azure SQL v12 bacpac：不支持密码的主密钥

I have done this dozens of times but just recently ran into this error. Here are the steps I have gone through to get here:

我做了几十次，但最近刚遇到这个错误。以下是我到达此处的步骤：

Create a copy of my Azure SQL v12 database on the same server as the original
在与原始服务器相同的服务器上创建Azure SQL v12数据库的副本
Export the copy-version (completely inactive from user interaction) to blob storage
将副本版本（从用户交互完全不活动）导出到blob存储
Download the .bacpac file from blob storage to my local drive
将.bacpac文件从blob存储下载到本地驱动器
In SSMS (October 2016 release) my local sql server instance, right click Databases and choose 'Import Data Tier Application'
在SSMS（2016年10月发布）我的本地sql server实例中，右键单击Databases并选择'Import Data Tier Application'
Choose my recently downloaded bacpac file and start the import
选择我最近下载的bacpac文件并开始导入

It only takes a few seconds for it to bomb out and I get the error:

炸弹只需要几秒钟就可以了，我得到了错误：

Error SQL72014: .Net SqlClient Data Provider: Msg 33161, Level 15, State 1, Line 1 Database master keys without password are not supported in this version of SQL Server
Error SQL72045: Script execution error. The executed script: CREATE MASTER KEY;

I followed the same process for the same database 1.5 months ago any everything worked fine...Is anyone else experiencing this??? I have SSDT version 14.0.61021.0 installed - not sure if that matters or not. I'm also running SQL Server 2016 Developer Edition (v13.0.1722.0).

我在1.5个月之前对同一个数据库进行了相同的处理，任何一切都运行良好......还有其他人遇到过这个吗???我安装了SSDT版本14.0.61021.0 - 不确定是否重要。我也在运行SQL Server 2016 Developer Edition（v13.0.1722.0）。

4 个解决方案

#1

I had the same problem. After speaking to Azure support they found out the issue was caused because a blank database master key is created to encrypt the storage credentials for the auditing (auditing is an optional setting).

我有同样的问题。在与Azure支持人员交谈后，他们发现问题是由于创建了一个空白数据库主密钥来加密审计的存储凭据（审计是可选设置）。

Note that database auditing settings are inherited from the server settings.

请注意，数据库审核设置是从服务器设置继承的。

Anyway, the work around they came up with was:

无论如何，他们想出的工作是：

Disable auditing on the server (or database)
禁用服务器（或数据库）上的审核
Drop the database master key with DROP MASTER KEY command.
使用DROP MASTER KEY命令删除数据库主密钥。

Then the export works as expected. Hopefully Azure will fix this issue soon so that auditing and export can work together.

然后导出按预期工作。希望Azure能尽快解决这个问题，以便审计和导出可以协同工作。

Update 21st March 2017 Better work-around From MS

更新2017年3月21日更好的解决方案来自MS

As the fix will take some time to be deployed, they also suggested an alternative solution, which will not require any additional steps (like disabling auditing or the steps form the blog) on your side to avoid this issue. After auditing is enabled, please update the master key and set the password. Setting a password for the existing master key will mitigate the issue. Also, setting the password will not impact auditing and it will keep working. The syntax to add the password is as follows:

由于修复程序需要一段时间才能部署，因此他们还建议了一种替代解决方案，它不需要任何其他步骤（如禁用审核或博客中的步骤）来避免此问题。启用审核后，请更新主密钥并设置密码。为现有主密钥设置密码可以缓解此问题。此外，设置密码不会影响审核，它将继续工作。添加密码的语法如下：

-- execute in the user database
ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = ‘##############’;

The link also has a PowerShell script you can use to remove the offending SQL Statement from the .bacpac file.

该链接还有一个PowerShell脚本，您可以使用该脚本从.bacpac文件中删除有问题的SQL语句。

#2

Okay the way I ended up resolving this issue was to do the following:

好吧，我最终解决此问题的方法是执行以下操作：

Create a copy of the SQL Azure database on the same server as the original
在与原始服务器相同的服务器上创建SQL Azure数据库的副本
In that database run the following script:

在该数据库中运行以下脚本：
```
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = [password here];
```
Follow steps 2-5 in the original post
按照原始帖子中的步骤2-5进行操作

I haven't seen documentation on this, but apparently when you create an Azure SQL database it creates a database master key (DMK) without a password, and in SQL Server 2016 this is not okay. Hopefully this helps somebody else out.

我没有看到关于此的文档，但显然当您创建Azure SQL数据库时，它会创建一个没有密码的数据库主密钥（DMK），而在SQL Server 2016中，这是不可行的。希望这有助于其他人。

Note: I was able to do this since I only wanted the data from the original database to refresh my local development copy - I haven't fully researched the implications of this.

注意：我能够这样做，因为我只希望原始数据库中的数据刷新我的本地开发副本 - 我还没有完全研究它的含义。

#3

You need to:

你需要：

remove the master key object from the bacpac
从bacpac中删除主密钥对象
remove the credential object from the bacpac
从bacpac中删除凭证对象

There is this script published on Microsoft blog, run it, and will do all the above.

这个脚本在Microsoft博客上发布，运行它，并将完成上述所有操作。

C:\PS> .\RemoveMasterKey.ps1  -bacpacPath "C:\BacPacs\Test.bacpac

#4

Fix corrupt bacpac created by removing master key.

修复通过删除主密钥创建的损坏的bacpac。

The Sugestion to run the script RemoveMasterKey, also created a corrupted bacpac file in my case by inserting Â into the model.xml file in multiple places.

Sugestion运行脚本RemoveMasterKey，在我的情况下通过将Â插入到多个地方的model.xml文件中也创建了一个损坏的bacpac文件。

There is a way to edit the bacpac by extractig the files, Removing the offending characters in the model.xml file and then generating a new checksum for the Origin.xml file

有一种方法可以通过extractig文件编辑bacpac，删除model.xml文件中的有问题的字符，然后为Origin.xml文件生成新的校验和

once done Zipping the files back up with the extension .bacpac allows you to import the backpac.

一旦完成，使用扩展名备份文件.bacpac允许您导入backpac。

Fix found at: http://inworksllc.com/editing-sql-database-azure-bacpac-files/

修复发现于：http：//inworksllc.com/editing-sql-database-azure-bacpac-files/

checksum generator: https://github.com/gertd/dac/tree/master/drop/debug

校验和生成器：https：//github.com/gertd/dac/tree/master/drop/debug

Steps provided:

提供的步骤：

1) Update the zip file with the modified model.xml

1）使用修改后的model.xml更新zip文件

2) Rename the zip file with the bacpac extension

2）使用bacpac扩展名重命名zip文件

3) Run dacchksum.exe /i:database.bacpac (where database.bacpac is the name the bacpac file)

3）运行dacchksum.exe /i:database.bacpac（其中database.bacpac是bacpac文件的名称）

4) Update Origin.xml in the bacpac file with the new value provided by dacchksum.exe

4）使用dacchksum.exe提供的新值更新bacpac文件中的Origin.xml

#1