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

Question

I have done this dozens of times but just recently ran into this error. Here are the steps I have gone through to get here:

1. Create a copy of my Azure SQL v12 database on the same server as the original
2. Export the copy-version (completely inactive from user interaction) to blob storage
3. Download the .bacpac file from blob storage to my local drive
4. In SSMS (October 2016 release) my local sql server instance, right click Databases and choose 'Import Data Tier Application'
5. Choose my recently downloaded bacpac file and start the import

It only takes a few seconds for it to bomb out and I get the 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;

I followed the same process for the same database 1.5 months ago any everything worked fine...Is anyone else experiencing this??? I have SSDT version 14.0.61021.0 installed - not sure if that matters or not. I'm also running SQL Server 2016 Developer Edition (v13.0.1722.0).

Answer 1

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 database
ALTER 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.