Azure SQL Server BACPAC import failed

Question

whenever i try to import BACPAC on local sql server I am getting below error message:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Could not import package.
Warning SQL0: The source contains users that rely on an external authentication provider that is not supported by the target. These users will be treated as users without logins.
Warning SQL72012: The object [DB_NAME_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 [DB_NAME_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' check box.
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;


 (Microsoft.SqlServer.Dac)

------------------------------
BUTTONS:

OK
------------------------------

All the previously reported issues on SO are not same as this one. Does anyone faced the same issue before?

Genoa

Answer 1

Eventually I was able to resolve this issue by following option 2 suggested here

In nutshell,

1. Save the utility RemoveMasterKeyFrombacpac from here
2. Run the command .\RemoveMasterKey.ps1 -bacpacPath "C:\BacPacs\fileName.bacpac"

Once above command finishes we get the -patched bacpac which imported seamlessly.

Update

With new MS SQL Server 2016 (13.0.1745.2) and SSMS v17.6 this is not an issue anymore.

Genoa

This worked great. I have SSMS v18.6 and needed to run this script. – Mcintyre

Answer 2

Try running this against your master database (right click on the server name in SSMS -> New Query)

sp_configure 'contained database authentication', 1;
GO 
RECONFIGURE;
GO

After this, you can go through the import bacpac wizard again and the issue should be resolved.

Georgettegeorgi

Thank you, this solution is still valid as of 2024. It is needed when transferring some (but not all) Azure SQL databases to a local SQL Server instance and it indeed solves the problem. – Bonn