Search code examples
sql-serverazureazure-sql-databasesql-server-2016ssms-2017

Import data tier application is failing

I'm getting the following error while restoring the database backup (.bacpac file) into my on premise SQL Server 2016 instance.

TITLE: Microsoft SQL Server Management Studio

Could not import package.

Warning SQL72012: The object [Azure_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 [Azure_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 33161, Level 15, State 1, Line 1
Database master keys without password are not supported in this version of SQL Server.

Error SQL72045: Script execution error. The executed script: CREATE MASTER KEY;

Solution

  • This is due to a difference between SQL Azure and SQL Server 2016. Having a master key without a password is an Azure-only feature. There are two ways to address this issue.

    1) open the .bacpac and remove the master key object as well as the credential object. (Rename the .bacpac to .zip, unzip and then look through the xml files, make necessary changes, zip back up and rename back to .bacpac)

    or

    2) add password encryption to the master key before you export the database to a .bacpac using the following: ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = '<PasswordHere>';