Is there a way to remove blob storage credential from azure database to allow bacpac local restore?

3
votes

I am trying to export a bacpac from Azure and restore it locally on SQLEXPRESS 2016. When I try to restore it though I get the following errors from the Import Data-tier Application wizard in SSMS:

Could not import package.

Warning SQL72012: The object [TestBacPacDB_Data] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.

Warning SQL72012: The object [TestBacPacDB_Log] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source'

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;

After some digging I found that a credential and master key have been added to the database. The credential name references a blob storage container, so I'm thinking maybe auditing was set up at some point with the container as an external resource or something similar.

I would like to delete this credential so I can restore the database locally, but the database throws an error stating that it is in use. I've tried disabling the logging in Azure, but the credential still can't be deleted.

I know sometimes it takes time for Azure to shut down resources, so maybe that's the cause, but I was wondering if anyone else has had a similar problem.

I'm trying to avoid having to set a password for the master key, since I don't care about the credential locally as in this question: SSMS 2016 Error Importing Azure SQL v12 bacpac: master keys without password not supported

2
azureazure-sql-databasessmssql-server-expressdata-tier-applications

2 Answers

2
votes

Ultimately, we ended up creating a master key. In order to restore our databases locally in this way, we create the database by hand first in SSMS, then add a master key to it. This allows the data import to work correctly.

0
votes

I had exactly the same problem, and tried a myriad of potential fixes found all over the place. Most were relating to rekeying the system, making a copy first, etc... and absolutely nothing worked.

As insane as this is, the only way I could finally get around it was manually editing the internal structure:

  1. Take the bacpac from original source or copy, anywhere
  2. Rename to .zip, and uncompress the folder structure
  3. Edit "model.xml", search for anything to do with "master key" and / or "shared access signature" and delete the corresponding nodes
  4. Calculate the Sha-256 checksum for the now modified model.xml
  5. Replace the checksum at the bottom of "Origin.xml"
  6. Rezip all the files and rename back to xxx.bacpac
  7. Import onto a local system as you normally would