database - 使用主 key 自动从Azure导入数据层应用程序(SQL数据库)

Question

当我从具有主密钥的Microsoft Azure SQL数据库中提取数据层应用程序时，无法将其导入到本地PC上的SQL Server中。

您会在这里发现其他人遇到此问题:SSMS 2016 Error Importing Azure SQL v12 bacpac: master keys without password not supported

但是，作为答案提供的步骤不适用于我的安装。
步骤是
1.禁用服务器(或数据库)上的审核
2.使用DROP MASTER KEY命令删除数据库主密钥。

Microsoft技术支持已验证此解决方案在我安装的SQL Server 上不起作用，并且在实际控制了我的PC并解决了问题之后，他们无法确定为什么会发生。

我需要找到一种从bacpac文件中删除主密钥的方法。我有一个Powershell脚本可以从BACPAC文件中删除主密钥，但是它需要从Windows Powershell中提取，重命名文件并运行脚本来导入数据库。

是否有人拥有一个程序或一组脚本，这些程序或脚本组将自动执行删除主密钥并使用单个命令从Azure导入SQL DB的过程？

Answer 1

我是这个论坛的新手。请不要苛刻这篇文章。我正在尽力帮助他人节省我花在此上的许多时间。

我将T-SQL脚本拼凑在一起，该脚本调用Windows Powershell脚本(也从多个来源拼凑而成)，以从Microsoft Azure SQL数据库提取数据层应用程序(数据库)，并通过运行将其导入到本地SQL Server的数据库中一个命令。几个月以来，我从其他博客等站点的脚本中找到了一些代码。由于这些人，我无法提供帮助，因为我没有跟踪信息的来源。如果您正在阅读本文，并且看到自己的代码，请相信。对于无法为您的工作功劳，我深表歉意。

您的PC和本地SQL Server上可能存在一些配置设置，需要进行调整，因为整个解决方案需要对计算机的完全访问权限。如果您在兼容性方面遇到麻烦，请告诉我，我会尽力告诉您如何配置我的系统，以防您有所帮助。

我正在使用Windows 10 Pro和Microsoft SQL Server Developer(64位)v12.0.5207.0

我将在GitHub上完成所有工作的两个文件放在此处:https://github.com/Wingloader/Auto-Azure-BACPAC-Download.git
GetNewBacpac-forGitHub.sql
GetAzureDB-forGitHub.ps1

警告:Powershell脚本文件将以明文形式存储SQL sa密码和Azure SQL登录名!
如果您不想这样做，请不要使用此解决方案。

我的计算机完全由我拥有和控制，因此我可以在系统中开放安全性，并且我愿意承担保护计算机的责任。

解决方案的基本步骤如下完成:(步骤1和2是可选的，因为我希望保留要使用的数据库版本，直到我将Azure DB的干净生产副本删除时为止)

将当前数据库备份为MyLocalDB.bak。

将备份从步骤1还原到新数据库，并在数据库名称(例如MyLocalDB20171231)结尾处标记上一天。

删除原始的MyLocalDB数据库(需要，以便稍后我们可以使用原始名称重新创建数据库)

从Azure下拉生产数据库并创建一个名为MyLocalDB的新数据库。

在步骤3中删除原始数据库，以便还原的数据库可以使用原始名称(当您具有引用该数据库名称的数据连接时，这一点很重要)

在步骤4中，由T-SQL中的以下行启动从Azure提取数据层应用程序数据库的工作:

EXEC MASTER..xp_cmdshell '%SystemRoot%\System32\WindowsPowerShell\v1.0\powershell.exe -File C:\Git\GetUpdatedAzureDB\GetAzureDB.ps1"'

Powershell脚本执行以下操作:

提取的目标是一个名为today.bacpac的文件(硬编码)。首先要做的是删除该文件(如果已存在)。

从Azure将数据库提取到today.bacpac文件中。

注意:我在Azure上的数据库具有用于加密的主密钥。在将bacpac文件导入本地数据库之前，需要将其从文件中删除，否则它将失败(根据我之前与MS Support的对话，在SQL 2017中可能不需要这样做)。如果您不使用主密钥，则可以剥离执行此步骤的代码，也可以不理会它。如果不存在，它将不会删除任何内容。这只会增加程序的开销。

打开today.bacpac文件(zip文件)，然后从Origin.xml文件中删除MasterKey节点。

修改Model.xml文件以更新SHA哈希长度。这是必需的，以便在SQL打开bacpac文件时文件不会被篡改。

将文件重新压缩回到今天的新文件-patched.bacpac

运行此行代码(来自Powershell)以将bacpac文件导入SQL Server

&C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin\SqlPackage.exe" /Action:Import /SourceFile:"C:\Git\GetUpdatedAzureDB\today-patched.bacpac" /TargetConnectionString:"Data Source=MyLocalSQLServer;User ID=sa; Password=MySAPassword; Initial Catalog=MyLocalDB; Integrated Security=false;"

编辑两个文件以提供更新的路径，用户名和密码后，运行SQL脚本。您无需再次编辑脚本。您可以再次运行SQL脚本而无需修改，它将创建Azure DB的新副本。

做完了!