Problem

You receive the following error when trying to import a BACPAC that was generated on a different server:

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.
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)

Solution

Execute the following T-SQL on your local instance:

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

Background

Initially I thought this error was arising due to enabling Transparent Data Encryption (TDE) on SQL Azure databases, but in actual fact it was down to switching to database level authentication rather than relying on server level logins.

A side-effect of this change was that the database is now considered to be (partially) contained, i.e. it has no hard dependencies on master. Read more on Contained Databases here. Support for contained databases is disabled by default as they have security implications. From the contained database authentication Server Configuration Option MSDN entry:

When contained databases are enabled, database users with the ALTER ANY USER permission, such as members of the db_owner and db_accessadmin database roles, can grant access to databases and by doing so, grant access to the instance of SQL Server. This means that control over access to the server is no longer limited to members of the sysadmin and securityadmin fixed server role, and logins with the server level CONTROL SERVER and ALTER ANY LOGIN permission. Before allowing contained databases, you should understand the risks associated with contained databases.