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://learn.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;"?

Answer 1

I tried importing the bacpac file on my local SQL Server 2019, which failed, even after running the recommended sp_configure command. Then I tried on my local SQL Server 2022 and it worked straight away.

Answer 2

I received a similar error, which in my specific case I had added a delete permission for a specific user to a table in my Azure SQL Database, then tried importing the bacpac file locally to my PC. This was done in Az SQL Db, in the Object Explorer by Right-clicking > Properties for a table, then select under "Select a page", clicking Permissions and adding the delete permission for the user.

For reference I tried the sp_configure 'contained database authentication', 1; command and it didn't solve the problem.

After attempting the bacpac import, I got the SQL72012 error. In review, I suspect it occurred because I never added that user at the server level under Security > Logins.

I fixed the issue by removing the permission on Az SQL database and I created a stored procedure to handle the delete instead. I also added the user locally at the server level to avoid similar problems. After removing the permission and generating the bacpac file, it imported successfully.

Answer 3

Using SQL Server Installation Center

1. Open SQL Server Installation Center:

2. Install Full-Text Search

Select "Add Features to an Existing Instance":

In the SQL Server Installation Center, go to the "Installation" section. Click on "New SQL Server stand-alone installation or add feature to an existing installation."

In side this choose "Full-Text and Semantic Extractions for Search." click install.

3. After installation is complete, you may need to restart your SQL Server services.

Answer 4

I know this question is almost 5 years old but today we came upon the same issue. The problem was that the .bacpac was created by a competitor for a client who was moving from their platform to ours, and they had no incentive to be cooperative beyond giving us the .bacpac. We had no way of connecting directly to their Azure database so all we could do was to try and make it work with the .bacpac we got.

Now I can't provide very specific details as I'm not 100% sure what, exactly, there was in the .bacpac that would be too specific to this particular company, so I can only describe what we did in the broadest terms.

Basically I did the following:

1. changed the .bacpac extension to .zip and extracted all to a folder
2. edited the model.xml file in notepad
3. I did the same for a sample local database that I had also exported as a .bacpac
4. The DataSchemaModel node looked very different. So I deleted the <DataSchemaModel node from their model.xml and replaced it with the one from my own export
5. Then I looked at the <Element Type="SqlDatabaseOptions" Disambiguator="1" node just after the node. I removed all the Property nodes and replaced them with the ones from my own export
6. After that it got kinda tricky. I went to the end of the model.xml file and found an element like <Element Type="SqlUser".... I removed the one(s) that I don't normally see in model.xml file. This element also has a Name="...." attribute so I took note of that name, then searched through model.xml and removed other things like Element Type="SqlPermissionStatement" referencing this user.
7. I removed stuff like <Element Type="SqlExternalDataSource" ....

After that I recalculated the model.xml checksum (plenty of online sources to show you how to do that) and I zipped it all up again into a new .bacpac

It required a bit of trial and error, and I don't remember the sequence of events exactly but once or twice it complained about something referencing something I had removed, but I removed those nodes and repeated the creation of a new .bacpac and eventually it just imported without errors.

I would imagine that some of the stuff I removed might have been important for the correct functioning of our competitor's software, but it didn't affect the data we wanted to migrate into our own system so it was "good enough" for us.

Posting this just in case it helps someone.

Answer 5

Using the Azure portal to Import a bacpac:

The Collation chosen in the first step of the Azure SQL Import wizard, is important!

It needs to match the Collation in the source DB that was used to create the bacpac.

The sqlpackage and SSMS methods do NOT need this step.

Answer 6

The solution that worked for me was to directly copy DB from azure to my localhost.

1. Create a new database in your local DB server.

2. Right-click on a new database, Tasks -> Import data.

3. Provide data for Azure db:

4. Provide data for your localhost:

5. Click Next. Check the first checkbox to select all.

6. On the next form choose -> Run immediately

7. Wait until the process is completed.

Answer 7

I had the same issue and it got fixed by importing the bacpac via command prompt. In link Import Bacpac go to SQLPackage section and run the command provided there.

sqlpackage.exe /a:import /tcs:"Data Source=<serverName>.database.windows.net;Initial Catalog=<migratedDatabase>;User Id=<userId>;Password=<password>" /sf:AdventureWorks2008R2.bacpac /p:DatabaseEdition=Premium /p:DatabaseServiceObjective=P6

Answer 8

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.