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

状況

SQLAzureで実行中の実データをローカルにインポートしてテストするために、Azureポータルからエクスポートしたbacpacをデータ層アプリケーションのインポートで取り込んだところ、

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

パッケージをインポートできませんでした。
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
によると、

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 から

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

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

    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に変更する。

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

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

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" />をノードごと削除。

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を修正した場合はこちらも修正する必要がある。

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

愚痴

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

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

状況

原因

対処法

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

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

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

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

愚痴