Thursday, 13 July 2017

SQL Error SQL72014 and SQL72045 When Importing a BACPAC

Problem


I was trying to import a BACPAC that was generated on a different server to my local development environment using SQL Server Management Studio 17.1 "Import Data-tier Application" wizard and received the following error

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)

Solution

Execute the following T-SQL:

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

Explanation

I thought above error was occurring due to either corrupt BACPAC or may be Transparent Data Encryption (TDE) on SQL Azure databases. After some soul searching on google, I learned that it was down to switching to database level authentication rather than relying on server level logins.

The consequence of running the solution T-SQL is 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. Read more about contained database authentication Server Configuration Option on MSDN.

0 comments :

Post a Comment