SSMS 2016 Error Importing Azure SQL v12 bacpac: master keys without password not supported

I had the same problem. After speaking to Azure support they found out the issue was caused because a blank database master key is created to encrypt the storage credentials for the auditing (auditing is an optional setting).

Note that database auditing settings are inherited from the server settings.

Anyway, the work around they came up with was:

1. Disable auditing on the server (or database)
2. Drop the database master key with DROP MASTER KEY command.

Then the export works as expected. Hopefully Azure will fix this issue soon so that auditing and export can work together.

Update 21st March 2017 Better work-around From MS

As the fix will take some time to be deployed, they also suggested an alternative solution, which will not require any additional steps (like disabling auditing or the steps form the blog) on your side to avoid this issue. After auditing is enabled, please update the master key and set the password. Setting a password for the existing master key will mitigate the issue. Also, setting the password will not impact auditing and it will keep working. The syntax to add the password is as follows:

-- execute in the user databaseALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = ‘##############’;

The link also has a PowerShell script you can use to remove the offending SQL Statement from the .bacpac file.

Okay the way I ended up resolving this issue was to do the following:

1. Create a copy of the SQL Azure database on the same server as the original

2. In that database run the following script:

   ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = [password here];

3. Follow steps 2-5 in the original post

I haven't seen documentation on this, but apparently when you create an Azure SQL database it creates a database master key (DMK) without a password, and in SQL Server 2016 this is not okay. Hopefully this helps somebody else out.

Note: I was able to do this since I only wanted the data from the original database to refresh my local development copy - I haven't fully researched the implications of this.

You need to:

• remove the master key object from the bacpac
• remove the credential object from the bacpac

There is this script published on Microsoft blog, run it, and will do all the above.

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