Exported database from Azure SQL failed to be imported to Azure SQL or to local SQL Server
Published Mar 13 2019 06:39 PM 11.9K Views
First published on MSDN on Mar 30, 2017

Update: (2018-01-30) - Resolved


DacFX version 17.4.1 has been released, this version contains the fix for all of the behaviors related to the master key.


you can download it here


to confirm you are using the correct version, verify that the file version of SQLPackage.exe is at least 14.0.3917.1


and follow the instructions here: using sqlpackage to import or export azure sql db


alternatively you use SSMS 17.6 and above, you can download it here




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.

read more about it here CREATE MASTER KEY (Transact-SQL)

failure to import back to Azure SQL caused by a limitation in the import process.

our teams are currently working to change this behavior from the server side.

Resolution:
Option 1
to mitigate import to Azure SQL DB use the import from the Azure portal.

to mitigate import to local SQL Server installation you can alter the existing master key and add password encryption to it.

this should be done before you export the database

ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = '<PasswordHere>';

Option 2
for short term mitigation we provide you PowerShell script that do the following:

  • remove the master key object from the bacpac

  • remove the credential object from the bacpac


after running this PS script on the bacpac you will have new bacpac file with "patched" suffix.

Download the PS script to fix: (updated: 2018-01-18)

Bacpac larger than 4GB - here


Bacpac smaller than 4GB - here


example:

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

Option 3 (Added 2017-09-11 Clive Challinor [MSFT])
I know there have been some issues with option 2 above, so I thought I’d provide some additional detail on how to manually make the changes. If you do get problems with option 2 and get the reported corrupt file message please raise a support call so we can have a look at a repro of the issue.

I’d recommend that you first take a look at my article on how to edit a .bacpac file, you are going to need it!

Editing a .bacpac file

As a test I created a database and enabled auditing, and very little else, the unzipped model.xml looks something like this :

<?xml version="1.0" encoding="utf-8"?>

<DataSchemaModel FileFormatVersion="1.2" SchemaVersion="3.0" DspName="Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider" CollationLcid="1033" CollationCaseSensitive="False" xmlns="http://schemas.microsoft.com/sqlserver/dac/Serialization/2012/02">

<Model>

<Element Type="SqlDatabaseOptions">

<Property Name="Collation" Value="SQL_Latin1_General_CP1_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="ServiceBrokerOption" Value="1" />

<Property Name="IsAllowSnapshotIsolation" Value="True" />

<Property Name="IsReadCommittedSnapshot" Value="True" />

<Property Name="IsEncryptionOn" 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" />

</Model>

</DataSchemaModel>



When I ran it through the steps in method 1 I end up with a file which looks like this

<?xml version="1.0" encoding="utf-8"?>

<DataSchemaModel FileFormatVersion="1.2" SchemaVersion="3.0" DspName="Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider" CollationLcid="1033" CollationCaseSensitive="False" xmlns="http://schemas.microsoft.com/sqlserver/dac/Serialization/2012/02">

<Model>

<Element Type="SqlDatabaseOptions">

<Property Name="Collation" Value="SQL_Latin1_General_CP1_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="ServiceBrokerOption" Value="1" />

<Property Name="IsAllowSnapshotIsolation" Value="True" />

<Property Name="IsReadCommittedSnapshot" Value="True" />

<Property Name="IsEncryptionOn" Value="True" />

<Property Name="FileStreamDirectoryName" Value="" />

<Property Name="TargetRecoveryTimePeriod" Value="120" />

<Relationship Name="DefaultFilegroup">

<Entry>

<References ExternalSource="BuiltIns" Name="[PRIMARY]" />

</Entry>

</Relationship>

</Element>

</Model>

</DataSchemaModel>



You can see clearly that in the patched version the <Element Type="SqlMasterKey" /> is gone, this should allow the import to work without error.

As a side note, the patching code does a little bit more than just removing this key, I found that it also removed database credentials, so elements that look like this



<Element Type="SqlDatabaseCredential" Name="[https://cbtevents.blob.core.windows.net/event]">

<Property Name="Identity" Value="SHARED ACCESS SIGNATURE" />

</Element>

Also got removed, I didn’t get round to testing if this was absolutely necessary.

Version history
Last update:
‎Mar 13 2019 06:39 PM
Updated by: