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;"?

You need to enable contained database authentication on your on-premises SQL Server. It's always enabled in Azure SQL Database. — David Browne - Microsoft
Please run that sp_configure on your on-premise SQL Server instance, do not run that statement on Azure SQL Database. — Alberto Morillo

Windhoek Windhoek · Accepted Answer · 2019-08-22T01:20:54

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.

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

2 Answers