Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last update.

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

状況

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

  1. Azure側でMasterKeyをパスワード付きで設定する →ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = '<PasswordHere>';
  2. リンク先のRemoveMasterKey.ps1を使ってパッチを当てる
  3. 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に変更する。
スクリーンショット (70).png

変更の必要があるのはこのうちmodel.xmlOrigin.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:ok_hand_tone2:

愚痴

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

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした