Could not import package. Warning SQL72012: The object exists in the target

Question

I exported my Azure database using Tasks > Export Data-tier Application in to a .bacpac file. Recently when I tried to import it into my local database server (Tasks > Import Data-tier Application), I encountered this error:

Could not import package.
Warning SQL72012: The object [MyDatabase_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 [MyDatabase_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 12824, Level 16, State 1, Line 5 The sp_configure value 'contained database authentication' must be set to 1 in order to alter a contained database.  You may need to use RECONFIGURE to set the value_in_use.
Error SQL72045: Script execution error.  The executed script:
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET CONTAINMENT = PARTIAL 
            WITH ROLLBACK IMMEDIATE;
    END


Error SQL72014: .Net SqlClient Data Provider: Msg 5069, Level 16, State 1, Line 5 ALTER DATABASE statement failed.
Error SQL72045: Script execution error.  The executed script:
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET CONTAINMENT = PARTIAL 
            WITH ROLLBACK IMMEDIATE;
    END

 (Microsoft.SqlServer.Dac)

I followed the advice on other posts and tried to run this on SQL Azure database:

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

However, it says

Could not find stored procedure 'sp_configure'.

I understand the equivalent statement in Azure is: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql?view=sql-server-2017

What is the equivalent statement to "sp_configure 'contained database authentication', 1;"?

Solution 1

The solution is to execute this against the master database of your local/on-premise SQL Server:

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

Thank you to David Browne - Microsoft and Alberto Morillo for the quick solution.

Solution 2

I had the same issue and it got fixed by importing the bacpac via command prompt. In link Import Bacpac go to SQLPackage section and run the command provided there.

sqlpackage.exe /a:import /tcs:"Data Source=<serverName>.database.windows.net;Initial Catalog=<migratedDatabase>;User Id=<userId>;Password=<password>" /sf:AdventureWorks2008R2.bacpac /p:DatabaseEdition=Premium /p:DatabaseServiceObjective=P6

Comments

[David Browne - Microsoft]

You need to enable contained database authentication on your on-premises SQL Server. It's always enabled in Azure SQL Database.

[Alberto Morillo]

Please run that sp_configure on your on-premise SQL Server instance, do not run that statement on Azure SQL Database.

[Windhoek]

This works. Thank you very much.

[User1]

What does this query actually do?

[Dark Templar]

This answer should be selected as the solution to the problem.

[haugan]

Yep, this is it - worked perfectly for me after running these commands to my local master db.

[rory.ap]

Yes, how do you use this. So I ran it. What now? Re-try the installation? Or is it "fixed" now as if the installation succeeded the first time?

[Nick Turner]

The question is about SQL Azure, not a local DB. This doesn't work

[Ricardo C]

This is just one of many causes. Your SQL Server version can be the cause.

[navigator]

This worked for me! Restoring bacpac to local SQL 2019 Dev installation.

[ransems]

Run the script against the master. Restore once more. Voila, no error on import.