Error SQL72014 .Net SqlClient Data Provider Msg 12824.

Applies to: SQL Server 2017 VM on Azure, Azure SQL Database, SQL Server Management Studio v17.9.

Date created: September 22, 2019.
 

Problem Description.
 

I exported an Azure SQL Database to an Azure Storage Account, then copied the bacpac file to a local drive on a SQL Server VM and from there I tried to import it to the default SQL Server 2017 instance using SQL Server Management Studio (SSMS), but I received the following warnings and errors:

Could not import package.
Warning SQL72012: The object [TestDB_Data] exists in the target, but 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 [TestDB_Log] exists in the target, but 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)
 

As result, I was not able to import the bacpac.

Both warnings can be discarded.

The key error is Error SQL72014 which is requesting to enable contained database authentication on the SQL Server instance.


Cause.

In order to restore a contained database comingo from Azure SQL Database into an on-premise SQL Server instance the "contained database authentication" property must be enabled.
 

Solution.

To enable this property please try below script:

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

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.