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.

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 it here

example:

C:\PS> .\RemoveMasterKey.ps1  -bacpacPath "C:\BacPacs\Test.bacpac"
Option 3 (Added 2017-08-17)

you can manually modify the content of the .bacpac file. here is a great article that describes how to do it.

Editing a .bacpac file

    Comments (21)

    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’…

    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?

    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?

    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?

    Skip to main content