Exported database from Azure SQL failed to be imported to Azure SQL or to local SQL Server


Update: (2018-01-18)

the next release of SSDT and DacFX will contain the fix for this issue.

until the next release is available we modified option 2 and have a different script for bacpac files with size less than 4GB.

if you experience some issues with the bacpac after it was patched you can use this version that should keep the file in a consistent state.

please keep follow up this post for more updates.

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.

 

Comments (66)

  1. ErikEJ says:

    Small typo: PS script on the pacpac => PS script on the bacpac

    1. Aman [MSFT] says:

      Funny one… fixed.

  2. pituach says:

    Thanks for sharing 🙂
    * Option 2 seems best useful, since there is no sense in changing the original (source) MASTER KEY only for the purpose of copying the database.

  3. anil m says:

    Even option 2 is not working it is inserting special chars after removing key part in BACPAC file.

    1. Aman [MSFT] says:

      can you share what is the outcome in your case? did you try to import the -patched bacpac file?

      1. anil m says:

        Below is the error, after using given PS script with patched file.

        *** Error importing database:Could not import package.
        Error SQL72014: .Net SqlClient Data Provider: Msg 102, Level 15, State 1, Procedure XYZZZ, Line 29 Incorrect syntax near ‘DeclareA’.Error SQL72045: Script execution error. The executed script:
        CREATE PROCEDURE[dbo]……

        1. Aman [MSFT] says:

          @anil – I will contact you directly to get some more details about your specific issue, we will share some more information later.

          1. anil m says:

            Thanks, Yochanan Rachamim.

          2. Nick Capito says:

            I had this same issue. Any resolution to this? It looks like its inserting weird characters to the bpac file .

            DeclareA’…

    2. KarlBeyer says:

      The powershell script to remove the masterkey seems to introduce an encoding issue which fails the import of the modified bacpac. On lines 74 and 101, where the Get-Content commandlet is used, it should also pass along the encoding parameter and use UTF8 (at least that worked for me to get past the encoding issues):

      line 74: xml]$modelXml = Get-Content -Encoding UTF8 $modelXmlFile
      line 101: [xml]$originXml = Get-Content -Encoding UTF8 $originXmlFile

  4. Grant Slater says:

    Please can you fix the script in Option 2 to support larger than 4GB ZIP files.

    1. we changed the script so now it support file size > 4GB. since this size of data operation it might take longer to complete file processing.

  5. jxm says:

    The script is not working for me. I’m not sure if it’s a problem with the hash or the data is getting munged. I’ve tried the process on a couple exported bacpac files from different databases with the same result.

    PS C:\temp> .\RemoveMasterKey.ps1 -bacpacPath “D:\db.bacpac”

    Type
    —-
    SqlMasterKey

    Completed update to the model.xml and Origin.xml in file D:\db-patched.bacpac

    Then, when trying to import the patched file:

    *** Error importing database:Could not load package from ‘D:\db-patched.bacpac’.

    File contains corrupted data.

  6. Maverick998 says:

    What is the timeline for fixing this so we don’t have to use these workarounds?

  7. Ryan says:

    I’m getting the following exception on large DB’s

    Exception calling “Dispose” with “0” argument(s): “Exception of type ‘System.OutOfMemoryException’ was thrown.”

    Anyway around this?

    1. Hi Ryan,
      I suspect you are using SSMS, I’d suggest using sqlpackage.exe or the Import/export features in the Azure DB portal.

      Regards
      Clive Challinor [MSFT]

  8. Lan Vo says:

    Option 2 works for me. Thanks.

  9. ashwini says:

    We tried altering the masterkey on the azure database and then export it. Still the same error while import.
    Is it required to alter the masterkey on a copy of that database and the export a copy(ompletely inactive from user interaction) on blob storage?

    1. no, you can do that on a live database. copy is needed to have consistent export of the database and not related to this master key issue

  10. KrisAerts says:

    Option 2 isn’t working for us. We are getting the error “File contains corrupted data”. I’ve even followed the steps inside the script manually with the same error as the result. The bacpac is 6GB (14GB unpacked). Any help/insight would be greatly appreciated.

    1. what is the target for the import? Azure SQL or SQL Server installed on a box? is this possible for you to create password for the master key before you export the database?

      1. TrevorYVR says:

        After running the Powershell script, I am getting the same error “File contains corrupted data”, when importing into a local version of sql server:

        Microsoft SQL Server 2016 (SP1-GDR) (KB4019089) – 13.0.4206.0 (X64) Jul 6 2017 07:55:03 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 6.3 (Build 15063: ) (Hypervisor)

        I am not able to set a password for the master key before exporting.

        Is it possible to resolve this problem? Having to spend 8 hours on trying various workarounds for every advertised-as-15-minute job when doing anything slightly unusual on Azure is getting quite frustrating.

        1. already discuss on another thread

          1. TrevorYVR says:

            Considering how highly this page scores in google pagerank, perhaps it would be helpful to your paying customers if you posted the URL to the other thread to which you refer?.

          2. Hi Trevor, Sorry for not been clear from the first place, this I meant this was discussed in another comment here on this page. I would encourage you to try the manual option #3 added to this article by Clive.
            you can also open a support case with us and we will be happy to assist you to solve the problem.

            Yochanan

      2. TrevorYVR says:

        I also tried re-importing back into Azure, setting the master password, re-exporting as bacpac, downloading a restoring on-prem, same problem. Ran the powershell script on that and importing the patched version, same corrupted data error.

        1. already discuss on another thread

      3. Eoin says:

        Same here.

        Downloaded the bacpac
        Ran the script to generate the -patched.bacpac file
        Tried to import it into a SQL Server 2016 instance and we get the error `File Contains Corrupted Data`

        TITLE: Microsoft SQL Server Management Studio
        ——————————

        Could not load package from ‘C:\TEMP\OURDB-patched.bacpac’. (Microsoft.SqlServer.Dac)

        ——————————
        ADDITIONAL INFORMATION:

        File contains corrupted data. (Microsoft.Data.Tools.Schema.Sql)

        ——————————

        File contains corrupted data. (WindowsBase)

        ——————————
        BUTTONS:

        OK
        ——————————

        1. Hi Eoin, try to use the manual option to edit the bacpac file. if not successful, please open support case so we can troubleshoot further.

          1. Ryan B says:

            The manual option is not really a viable option. I’m surprised this is taking MS so long to fix. This issue has been occurring for close to a year now with no resolution in site. I’ve attempted option 1 and 2, with the multitude of scripts that have been provided via this URL and it still doesn’t work.

            Like I said manual is not a viable option. I have a program written specifically to copy the Azure DB, create a backup, download and restore to on-prem for development use. I can’t be expected to manually apply the fix to a dozen DBs every week.

            I believe a proper fix from Microsoft should be readily available, seeing as so many people are having the same issues.

          2. Hi Ryan,

            for automated process the manual workaround surely not an option.
            if you cannot resolve the problem with the first options please raise a support case for us so we can work on it together to find the best solution for you.

            Regards,
            Yochanan.

  11. Paul Hatcher says:

    The patched file won’t import due to a dependency error – it won’t create an SP as the security policies referenced don’t exist yet

    Is there an easy way around this?

    1. Hi Paul, can you share the exact error message?

  12. Thanks options 2 works

    is there any way we do modification on destination ( on prim SQL server ) as a permanent fix considering as a routine activity ?

    1. the fix should allow you to import the database to on-prem SQL server as well, so any action on the SQL server will not do any help anyway.

  13. Babji Talluri says:

    Hi,

    I was having similar kind of issue due but not due to blank passwords but due to enabling database auditing.Do we have any ETA on when is this going to be fixed? so that the bacpacs restores work fine even with database auditing enabled.

    Thank you in advance,
    Babji

    1. Babji Talluri says:

      Hi MSFT,

      Could any one please review my question and answer when you get a chance.

      Thank you,
      Babji

  14. TrevorYVR says:

    In case any Microsoft people are still monitoring this post, I am still having this problem and would very much like a fix, and would be happy to provide the necessary details to reproduce.

    1. Hi Trevor, if you ware not successful with all the options in this post I will be very happy if you can open support case with us so we can troubleshoot your specific case.
      if this is not an option for you, you can share here more details about your bacpac file and the specific error message and we will try to help.

  15. Miguel says:

    Option 3 worked for me, but with some further steps.
    The SqlPackage tool wouldn’t accept the new file because of a checksum error. (I had edited the file and then zipped back to generate the modified bacpac file)

    So instead I had to keep the old backup file without modifications, and keep the modified model.xml outside of it. Then, provide the model as a parameter to the tool via the ModelFilePath flag, like so:

    .\SqlPackage.exe /Action:Import /SourceFile:”pathToBackupFile\backup.bacpac” /ModelFilePath:”pathToModelFile\model.xml” /TargetConnectionString:”yourconnectionstring”

    1. Hi Miguel,
      Yes you can’t just modify the model.xml, you need to re-calculate the checksum in the Origin.xml file. If you check out this link
      https://blogs.msdn.microsoft.com/azuresqldbsupport/2017/08/16/editing-a-bacpac-file/ it explains how to do that.

      Regards
      Clive Challinor [MSFT]

  16. Option 2 worked for me, but this is surely a workaround and not a fix? bacpacs have worked fine for years and now they don’t. Is MS actually going to fix the issue?

    I tried (on SQL Azure) the ALTER MASTER KEY option, the re-exported, but that did not work; the import to local on-prem db still fails with CREATE MASTER KEY

    I would really like to able to just import bacpacs again.

    1. Hi Neil,
      indeed, this is only workaround.
      this behavior will be fixed and we will update this post with more information.

      Regards,
      Yochanan.

      1. Thank you for your reply.

        One thing to look out for with patched bacpacs is that ithe copy/patch process seems to modify scripts. eg: the sql for an unpatched bacpac view contains
        THEN ‘Was £’
        but this gets mangled to
        THEN ‘Was £’
        in a patched bacpac’s version of the View (and 13 other occurrences in the db).

        Some sort of encoding issue in the bacpac copy process perhaps?

        1. Sakthivbala says:

          Hi Am getting the same error, It insert some junk characters in the SQL procedure script which makes the restore process fail.
          How to fix this issue.

          1. if your bacpac file is less than 4gb please try the new script on option 2. I would appreciate if you can reply here and share if this was successful for you.

  17. Bad Dancer says:

    OK so late to the party… Exporting older created catalogues worked well with sqlpackage.exe (120). Newly created catalogues fail on sqlpackage.exe (120) but work on sqlpackage.exe (140) when trying to re-import them back to on premis. Trying to export an older Azure catalogues (to on premise SQL) with sqlpackage.exe (140) , now the older catalogues give SqlClient Data Provider: Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ‘;’.
    Error SQL72045: Script execution error. The executed script:
    CREATE MASTER KEY;
    I’m nervous to create a master key password as it took a lot of time to find the intial issue. can you guarantee that I will be able to access both older and newer catalogs?

    1. Hi Bad Dancer,

      I’m not sure when exactly you get the 72045 exception, but it expected when you try to import package that originally created on Azure SQL DB into on-premise SQL installation as the BOX version does not support master key with no password.
      Please raise a support case if you need out help to resolve the problem.

      Regards,
      Yochanan.

  18. Alan.Nebula says:

    Hi Yochanan, Clive

    I’ve just installed both EN\x64\DacFramework.msi & EN\x86\DacFramework.msi as instructed in the link and it still fails with the same error:

    *** Error importing database:Could not import package.
    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;

    Using SqlPackage.exe from C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin
    File date is 12/09/2017
    Version 14.0.3811.1

    Any ideas ?

    Thanks,
    Alan

    1. Hi Alan, for you the workaround to set master key password can do the work.
      a new version of DacFX that addresses this issue will be published shortly.

  19. YetAnOtherProfile says:

    Hi Clive Challinor,

    Following [Update: Clive Challinor [MSFT] 18th October 2017] does not help me.

    I’m still having difficulties importing importing a database from Azure.

    First i have installed Microsoft® SQL Server® Data-Tier Application Framework (17.3 GA DacFx)
    https://www.microsoft.com/en-us/download/details.aspx?id=56048

    It doesn’t help.

    Then i have created a user on Azure to stop sqlpackage.exe from complaining. See below steps 1-4

    1 ——————————————————————–

    –Creating user in AzureDB

    –Azure Master DB
    USE [master]
    CREATE LOGIN ImportExportUser WITH password=’YourPassword’;
    CREATE USER ImportExportUser FROM LOGIN ImportExportUser;
    EXEC sp_addrolemember ‘loginmanager’, ‘ImportExportUser’

    –Azure Data Source DB
    CREATE USER ImportExportUser FROM LOGIN ImportExportUser;
    EXEC sp_addrolemember N’db_owner’, N’ImportExportUser’
    GO

    2 ——————————————————————–

    Navigating to SqlPackage.exe on local file system:

    cd “c:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin\”

    3 ——————————————————————–

    — Executing export command ( to local file system ):
    sqlpackage.exe /Action:Export /ssn:tcp:zuactngtf7.database.windows.net /sdn:YourDB /su:ImportExportUser /sp:YourPassword /tf:C:\users\YourName\documents\azuredbbackup\20171023YourDB.bacpac /p:Storage=File

    4 ——————————————————————–

    — Executing import command ( from local file system to local db ):
    sqlpackage.exe /Action:Import /tsn:tcp:. /tu:ImportExportUser /tp:YourPassword /tdn:local-YourDB /sf:C:\users\YourName\documents\azuredbbackup\20171023YourDB.bacpac /p:Storage=File

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

    Result when executing sqlpackage.exe from command line above:

    *** Error importing database:Could not import package.
    Unable to connect to master or target server ‘YourDB’. You must have a user with the same password in master or target server ‘YourDB’.
    ======================================================================
    Result when importing using SQL Server Management Studio 17.3

    ———————————————————————-

    TITLE: Microsoft SQL Server Management Studio
    ——————————

    Could not import package.
    Warning SQL72012: The object [YourDB_Data] exists in the target, but it will not be dropped even though you selected the ‘Generate drop statements for objects that are in the target database but that are not in the source’ check box.
    Warning SQL72012: The object [YourDB_Log] exists in the target, but it will not be dropped even though you selected the ‘Generate drop statements for objects that are in the target database but that are not in the source’ check box.
    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;

    (Microsoft.SqlServer.Dac)

    ——————————
    BUTTONS:

    OK
    ——————————

    I’m not happy about dropping the master key and disabling auditing on the Azure source database, as some have suggested.

    Any better ideas?

    Is it just a matter of waiting for a new SSMS upgrade or can anybody explain what an import from Azure to a local on premise database requires using v. 17.3?

  20. YetAnOtherProfile says:

    “The latest SSDT (17.3) should have a fix for this issue”

    Unfortunately it doesn’t.

    1. 17.3 addresses some of the issues, but new version will be published shortly that resolves all the issues described here.
      please follow this blog for more updates

  21. Alastair Pitts says:

    So we were running into this issue, and resolved it via the Powershell script. However, that’s just flakey enough that we tried to upgrade to the new SSDT (17.3) as described in the updates.

    However, we are now getting a new error:

    Importing to database ‘3c1a1d63-f898-4b8e-87c5-ca0efd77c669’ on server ‘(local)’.
    *** Error importing database:Could not load package from ‘.\dbexports\3c1a1d63-f898-4b8e-87c5-ca0efd77c669.bacpac’.
    Specified argument was out of the range of valid values.
    Parameter name: zipFileName

  22. In my case, the problem is still here. I’m using SSMS 17.3 + SSDT 17.3. I cannot import bacpac (created on Azure) to my Localdb.
    Still getting “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.”

    1. Please try to follow one of the options on this page until the next release of SSDT is available.

  23. YN says:

    Has this issue now been resolved (without requiring the workarounds in this post?)

    1. a fix will be ready soon, follow this post for updates

      1. Freek Paans says:

        Any updates?

        1. I am in touch with product team about it, this is work in progress and the fix will be included in next release, we are doing our best to release an update as soon as possible, I will update this post when next release will be available.

      2. Amir Mechouk says:

        How soon in soon?

        1. Unfortunately, I do not have the timeline, I will update this post when next release will be available.

      3. Hi Yochanan,

        Please advise if any timelines for the fix creation?

        Thanks

        1. Should be available in next release, I will update this post when next release will be available.

  24. Sakthivbala says:

    Hi I used Option 2,
    New patch file created successfully.
    But while trying to restore using the patched bacpac file am getting below error.

    Error SQL72014: .Net SqlClient Data Provider: Msg 102, Level 15, State 1, Procedure XXX, Line 61 Incorrect syntax near ‘INNERÂ’.
    Error SQL72045: Script execution error. The executed script:

    Can anyone help me on this ASAP.

    1. Please see the above comment on your other message

Skip to main content