Dismiss Notice

Register now to be one of the first members of this SharePoint Community! Click here it just takes seconds!

Dismiss Notice
Welcome Guest from Country Flag

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

Discussion in 'Official Microsoft News' started by Yochanan Rachamim - MSFT, Mar 30, 2017.

Thread Status:
Not open for further replies.
  1. Yochanan Rachamim - MSFT

    Yochanan Rachamim - MSFT Guest

    Blog Posts:
    0
    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


    use the import from the Azure portal in case you need to import the database to Azure SQL DB.

    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 pacpac you will have new bacpac file with “patched” suffix.

    download it here

    example:


    C:pS> .RemoveMasterKey.ps1 -bacpacPath “C:BacPacsTest.bacpac”

    Continue reading...
     
Thread Status:
Not open for further replies.

Share This Page

LiveZilla Live Chat Software