How to Fix SQL72014 and SQL72045 When Importing a BACPAC

Problem

I was importing a BACPAC generated on another server into my local development environment using SQL Server Management Studio and ran into the following errors:

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

Could not import package.
Warning SQL0: A project which specifies Microsoft Azure SQL Database v12 as the target platform may experience compatibility issues with SQL Server 2014.
Warning SQL72012: The object [databaseXYZ_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 [databaseXYZ_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)

Fix

Run the following T-SQL on the target SQL Server instance before importing the BACPAC:

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

Once that setting is enabled, rerun the import.

Why This Happens

The import process is trying to set the target database to partial containment:

ALTER DATABASE [YourDatabaseName]
SET CONTAINMENT = PARTIAL;

If contained database authentication is disabled on the SQL Server instance, that step fails and the import stops with SQL72014 and SQL72045.

This often happens when the BACPAC comes from Azure SQL Database and is being imported into a local SQL Server environment, where contained database authentication may be turned off by default.

Explanation

At first I suspected the issue might be caused by a corrupt BACPAC or Transparent Data Encryption (TDE), but the real cause was much simpler: the import required support for a contained database.

A partially contained database reduces dependencies on the master database and allows authentication and configuration to live more at the database level rather than relying entirely on server-level logins.

Because this has security implications, SQL Server does not always enable it by default on local or on-premises instances.

Notes

• SQL72014 and SQL72045 are the main errors causing the import failure.
• The SQL72012 warnings about data and log objects are not the root cause here.
• For most local development environments, enabling contained database authentication is enough to complete the import successfully.

Further Reading

• Contained database authentication server configuration option
• SqlPackage import documentation
• Import a BACPAC file to create a new database