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

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.