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:

Create a copy of my Azure SQL v12 database on the same server as the original
Export the copy-version (completely inactive from user interaction) to blob storage
Download the .bacpac file from blob storage to my local drive
In SSMS (October 2016 release) my local sql server instance, right click Databases and choose 'Import Data Tier Application'
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).

score 38 · Answer 1 · edited May 30 '17 at 20:00

38

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:

Disable auditing on the server (or database)
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.

edited May 30 '17 at 20:00

ps2goat

8,067
1
35
68

answered Mar 10 '17 at 08:10

marvc1

3,641
3
25
34

We've just hit this. Do you have any further insight into what other implications dropping the master key might have? – ChadT Mar 20 '17 at 23:19
1

@ChadT yeah I did get some more info. I've update the answer above. – marvc1 Mar 21 '17 at 09:55
5

Blog post from MS with details https://blogs.msdn.microsoft.com/azuresqlemea/2017/03/30/exported-database-from-azure-sql-failed-to-be-imported-to-azure-sql-or-to-local-sql-server/ – GraemeMiller May 10 '17 at 12:17
@GraemeMiller thanks for blog link. It has a script that fixes up the downloaded bacpac for local import. No server DB changes necessary. – J. Andrew Laughlin May 12 '17 at 20:23
Yeah. We used that to avoid server change – GraemeMiller May 12 '17 at 22:14
**The script is currently still broken**, at least for some users. – tbone Sep 01 '17 at 04:07

score 16 · Accepted Answer · answered Nov 14 '16 at 01:21

16

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

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

In that database run the following script:

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

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.

answered Nov 14 '16 at 01:21

How 'bout a Fresca

2,267
1
15
26

To clarify, does Step 1 say I should create a copy of the Azure SQL database *in the Azure SQL server* or on my local on-prem SQL Server? – Dai Oct 02 '17 at 01:43
The copy should be made in the Azure SQL Server. As soon as that copy has finished being exported to blob storage, you can delete the copy DB...just make sure to delete the copy DB and not the original! – How 'bout a Fresca Oct 02 '17 at 13:22
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password' – Ramanujam Allam Oct 30 '17 at 05:16
I'm restoring a DNN database locally and was happy to find this. By any chance, do you do DNN work and if so, are there any hiccups that you've noticed? If not, I'm in uncharted waters, I guess. – user1585204 Apr 18 '18 at 15:19
For anyone dropping by this question, the following resloved it for me. https://www.sqlcoffee.com/Troubleshooting213.htm sp_configure 'contained database authentication', 1; GO RECONFIGURE; GO – Mikkel Nov 08 '21 at 16:04

score 12 · Answer 3 · answered Oct 18 '17 at 11:33

12

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

answered Oct 18 '17 at 11:33

Adrian Onu

671
7
13

3

This didn't work for me when I tried it, it ended up creating a corrupted .bacpac that was useless. I'm not sure why though, since I have heard that it works for others. – How 'bout a Fresca Oct 25 '17 at 14:16
1

The script generates a new patched file and doesn't modify the original. Note that the bacpac path needs to be a full path as in the example and that you may need to execute the following from within PowerShell to turn off security settings that block that script: `Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass`. – Savage Feb 02 '18 at 16:54
This is the easiest solution (and the only one that worked for me. Also, you really need to run `Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass` before `.\RemoveMasterKey.ps1 -bacpacPath "C:\BacPacs\Test.bacpac"`. tnx @Savage – Guilherme May 29 '18 at 21:22

score 0 · Answer 4 · answered Feb 09 '18 at 14:46

Fix corrupt bacpac created by removing master key.

The Sugestion to run the script RemoveMasterKey, also created a corrupted bacpac file in my case by inserting Â into the model.xml file in multiple places.

There is a way to edit the bacpac by extractig the files, Removing the offending characters in the model.xml file and then generating a new checksum for the Origin.xml file

once done Zipping the files back up with the extension .bacpac allows you to import the backpac.

Fix found at: http://inworksllc.com/editing-sql-database-azure-bacpac-files/

checksum generator: https://github.com/gertd/dac/tree/master/drop/debug

Steps provided:

1) Update the zip file with the modified model.xml

2) Rename the zip file with the bacpac extension

3) Run dacchksum.exe /i:database.bacpac (where database.bacpac is the name the bacpac file)

4) Update Origin.xml in the bacpac file with the new value provided by dacchksum.exe

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

4 Answers4

Linked

Related