SQL AzureでエクスポートしたbacpacをローカルのSQLサーバーにインポートすると SQL72014が発生する

またなにかアップデートされた?

# 状況

SQLAzureで実行中の実データをローカルにインポートしてテストするために、Azureポータルからエクスポートしたbacpacを[データ層アプリケーションのインポート](https://msdn.microsoft.com/ja-jp/library/ee362018(v=vs.100).aspx)で取り込んだところ、

```
===================================

パッケージをインポートできませんでした。
Warning SQL72012: オブジェクト [hoge-2017-11-1-15-7_Data] はターゲットに存在しますが、[ターゲット データベースにあってソースにないオブジェクトに対して DROP ステートメントを生成する] チェック ボックスをオンにしてもドロップされません。
Warning SQL72012: オブジェクト [hoge-2017-11-1-15-7_Log] はターゲットに存在しますが、[ターゲット データベースにあってソースにないオブジェクトに対して DROP ステートメントを生成する] チェック ボックスをオンにしてもドロップされません。
Error SQL72014: .Net SqlClient Data Provider: メッセージ 33161、レベル 15、状態 1、行 1 パスワードのないデータベース マスター キーは、このバージョンの SQL Server ではサポートされていません。
Error SQL72045: スクリプトの実行エラーです。実行されたスクリプト:
CREATE MASTER KEY;


 (Microsoft.SqlServer.Dac)

------------------------------
プログラムの場所:

   場所 Microsoft.SqlServer.Dac.DeployOperation.ThrowIfErrorManagerHasErrors()
   場所 Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass14.<>c__DisplayClass16.<CreatePlanExecutionOperation>b__13()
   場所 Microsoft.Data.Tools.Schema.Sql.Dac.OperationLogger.Capture(Action action)
   場所 Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass14.<CreatePlanExecutionOperation>b__12(Object operation, CancellationToken token)
   場所 Microsoft.SqlServer.Dac.Operation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   場所 Microsoft.SqlServer.Dac.ReportMessageOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   場所 Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   場所 Microsoft.SqlServer.Dac.DeployOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   場所 Microsoft.SqlServer.Dac.OperationExtension.Execute(IOperation operation, DacLoggingContext loggingContext, CancellationToken cancellationToken)
   場所 Microsoft.SqlServer.Dac.DacServices.InternalDeploy(IPackageSource packageSource, Boolean isDacpac, String targetDatabaseName, DacDeployOptions options, CancellationToken cancellationToken, DacLoggingContext loggingContext, Action`3 reportPlanOperation, Boolean executePlan)
   場所 Microsoft.SqlServer.Dac.DacServices.ImportBacpac(BacPackage package, String targetDatabaseName, DacImportOptions importOptions, Nullable`1 cancellationToken)
   場所 Microsoft.SqlServer.Dac.DacServices.ImportBacpac(BacPackage package, String targetDatabaseName, DacAzureDatabaseSpecification creationDefaults, Nullable`1 cancellationToken)
   場所 Microsoft.SqlServer.Management.Dac.DacWizard.ImportDatabase.DoWork()
   場所 Microsoft.SqlServer.Management.TaskForms.SimpleWorkItem.Run()

```
と、エラーになった。

# 原因
 [Exported database from Azure SQL failed to be imported to Azure SQL or to local SQL Server – Azure SQL Database Support](https://blogs.msdn.microsoft.com/azuresqldbsupport/2017/03/30/exported-database-from-azure-sql-failed-to-be-imported-to-azure-sql-or-to-local-sql-server/)
によると、

>Symptoms: 
>
>When using blob auditing on Azure SQL Server or Database, after you export database to .bacpac file
>
>if you import the database to another Azure SQL DB that already have blob auditing enabled at the server level you may get this error message:
>
>Could not import package.
>Error SQL72014: .Net SqlClient Data Provider: Msg 15578, Level 16, State 1, Line 1 There is already a master key in the database. Please drop it before performing this statement.
Error SQL72045: Script execution error. The executed script:
CREATE MASTER KEY;
>
>if you import the database to local on-premise SQL Server installation you may get this 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;

>this also apply if you created master key without password for your own usage.

>Cause: 

>This caused by a different behavior between Azure SQL DB and Local SQL Server installation

>a master key without password is an Azure SQL DB only feature, while local SQL Server installation must have password encryption for master key.

master keyに対する仕様がローカルDBとAzureで違うらしく、Azure側のnull Master Keyをローカル側に適用しようとしてエラーになっているようだ。

# 対処法
同じく  [Exported database from Azure SQL failed to be imported to Azure SQL or to local SQL Server – Azure SQL Database Support](https://blogs.msdn.microsoft.com/azuresqldbsupport/2017/03/30/exported-database-from-azure-sql-failed-to-be-imported-to-azure-sql-or-to-local-sql-server/) から

1. Azure側でMasterKeyをパスワード付きで設定する →` ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = '<PasswordHere>';`
2. リンク先のRemoveMasterKey.ps1を使ってパッチを当てる
3. bacpacを直接編集してなんとかする

のどれか。
1は今稼働中のサービスに手をいれるのは憚られたので不採用。
2は実行してみたものの日本語フィールドやテーブルが文字化けしちゃってダメ。

```sql
	select 蜿嶺ｻ俶律,繧ｭ繝｣繝ｳ繧ｻ繝ｫ譌･,sum(驥鷹｡・
	from (
		select resv.id,resv.蜿嶺ｻ俶律,resv.繧ｭ繝｣繝ｳ繧ｻ繝ｫ譌･,resv.莠育ｴ・律,resv.繧ｹ繝・・繧ｿ繧ｹ,rec.驥鷹｡・
		from glh莠育ｴ・as resv
		left outer join glht蜿門ｼ募ｮ溽ｸｾ as rec on rec.hid=@hid and resv.id=rec.莠育ｴ・d and rec.遘醍岼CD in ( select 遘醍岼CD from glh遘醍岼 where hid=@hid and 雋ｸ蛟溷玄蛻・1)
		where resv.hid=@hid 
		and resv.id in (select 莠育ｴ・d from glh莠育ｴ・・邏ｰ where hid=@hid and 莠育ｴ・律 between @startDate and @endDate)
		and (resv.繧ｭ繝｣繝ｳ繧ｻ繝ｫ譌･ is null or resv.繧ｭ繝｣繝ｳ繧ｻ繝ｫ譌･> @startDate)
 	) as bb
	group by 蜿嶺ｻ俶律,繧ｭ繝｣繝ｳ繧ｻ繝ｫ譌･
	order by 蜿嶺ｻ俶律,繧ｭ繝｣繝ｳ繧ｻ繝ｫ譌･
```
こんな有様。

なので3でなんとかする。


# bacpacを修正してMaster Key情報を消す

bacpacファイルはzip圧縮されたフォルダなので拡張子をzipに変更する。
![スクリーンショット (70).png](https://qiita-image-store.s3.amazonaws.com/0/199750/f42af7f2-53f1-479d-c3a8-083aad6adc61.png)

変更の必要があるのはこのうち`model.xml`と`Origin.xml`

## model.xmlからMasterKeyの設定を削除

```xml:model.xml
<?xml version="1.0" encoding="utf-8"?>
<DataSchemaModel FileFormatVersion="1.2" SchemaVersion="3.0" DspName="Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider" CollationLcid="1041" CollationCaseSensitive="False" xmlns="http://schemas.microsoft.com/sqlserver/dac/Serialization/2012/02">
	<Model>
		<Element Type="SqlDatabaseOptions">
			<Property Name="Collation" Value="Japanese_CI_AS" />
			<Property Name="IsAnsiPaddingOn" Value="False" />
			<Property Name="IsQuotedIdentifierOn" Value="False" />
			<Property Name="IsCursorDefaultScopeGlobal" Value="True" />
			<Property Name="IsTornPageProtectionOn" Value="False" />
			<Property Name="IsFullTextEnabled" Value="True" />
			<Property Name="CompatibilityMode" Value="120" />
			<Property Name="ServiceBrokerOption" Value="1" />
			<Property Name="IsAllowSnapshotIsolation" Value="True" />
			<Property Name="IsReadCommittedSnapshot" Value="True" />
			<Property Name="FileStreamDirectoryName" Value="" />
			<Property Name="TargetRecoveryTimePeriod" Value="120" />
			<Relationship Name="DefaultFilegroup">
				<Entry>
					<References ExternalSource="BuiltIns" Name="[PRIMARY]" />
				</Entry>
			</Relationship>
		</Element>
		<Element Type="SqlMasterKey" />
```
先頭の方にある`<Element Type="SqlMasterKey" />`をノードごと削除。

```xml:model.xml
		<Element Type="SqlDatabaseCredential" Name="[https://hogehoge.blob.core.windows.net/sqldbauditlogs]">
			<Property Name="Identity" Value="SHARED ACCESS SIGNATURE" />
		</Element>
	</Model>
</DataSchemaModel>
```

最後にある `<Element Type="SqlDatabaseCredential"...`をノードごと削除。

## Origin.xmlのチェックサムを更新

`Origin.xml`には上記の`model.xml`のチェックサムが記述されていて、これを変更しないとエラーになる。

```
===================================

パッケージからスキーマ モデルを読み込めませんでした。 (Microsoft.SqlServer.Dac)

------------------------------
プログラムの場所:

   場所 Microsoft.SqlServer.Dac.DacPackage.LoadModel(IPackageSource packageSource, Boolean ignoreUnresolvedExternalErrors)
   場所 Microsoft.SqlServer.Dac.Extensions.DacExtensions.GetCollationString(IPackageSource packageSource)
   場所 Microsoft.SqlServer.Dac.Extensions.DacExtensions.GetCollationString(BacPackage package)
   場所 Microsoft.SqlServer.Management.Dac.DacWizard.CreateDatabaseOnTargetWorkItem.DoWork()
   場所 Microsoft.SqlServer.Management.TaskForms.SimpleWorkItem.Run()

===================================

パッケージ D:\Data\hoge-2017-11-1-15-7.bacpac 内の model.xml に対して計算されたチェックサムが、保存されていたチェックサムと異なります。 (Microsoft.Data.Tools.Schema.Sql)

------------------------------
プログラムの場所:

   場所 Microsoft.Data.Tools.Schema.Sql.Build.DacUtilities.CheckModelChecksum(SqlPackage package)
   場所 Microsoft.Data.Tools.Schema.SchemaModel.DataSchemaModel.DeserializePackage(SqlPackage package, ErrorManager errors, Action`3 constructorParametersSetter)
   場所 Microsoft.SqlServer.Dac.DacPackage.DeserializePackage(SqlPackage package, DacSchemaModelStorageType modelStorageType, ErrorManager errorManager, DataSchemaModelHeader& header)
   場所 Microsoft.SqlServer.Dac.DacPackage.LoadModel(IPackageSource packageSource, Boolean ignoreUnresolvedExternalErrors)

```

model.xmlを修正した場合はこちらも修正する必要がある。

```xml:Origin.xml
  <ExportStatistics>
    <SourceDatabaseSize>2738568</SourceDatabaseSize>
    <TableRowCountTotalTag>1891579</TableRowCountTotalTag>
  </ExportStatistics>
  <Checksums>
    <Checksum Uri="/model.xml">E3E2ED1D8C66CE0F0729B2702BE3149697B63CB8DDAD8F554FBCA31357CFD71E</Checksum>
  </Checksums>
  <ModelSchemaVersion>3.0</ModelSchemaVersion>
</DacOrigin>
```

のChecksumの部分。
このチェックサムはSHA256で計算する。

Windowsの場合はコマンドプロンプトで
`c:\> CertUtil -hashfile "<path to model.xml>" SHA256`
としたらOK。

```
c:\> certUtil -hashfile "C:\hoge\model.xml" SHA256
SHA256 ハッシュ (ファイル C:\hoge\model.xml):
691313c46a04b04a5e98e19ad19c69c9bd972744ed5253b00cff82b411c122bc
CertUtil: -hashfile コマンドは正常に完了しました。
```

# ファイルを戻してインポート

bacpacのzipに編集したmodel.xmlとOrigin.xmlをコピーして拡張子をbacpacに戻したらインポートでOK:ok_hand_tone2:


#愚痴

データ層アプリケーションのインポートツールでこのへん迂回するオプションを追加してほしい。