3

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;

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jesalcv
  • 447
  • 1
  • 5
  • 17

2 Answers2

3

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>';

digital.aaron
  • 5,435
  • 2
  • 24
  • 43
  • 1
    Where are the "master key" and "credential" objects? – haugan Nov 30 '20 at 10:04
  • 1
    For anyone else dropping by this question see: https://www.sqlcoffee.com/Troubleshooting213.htm It fixes it for me. sp_configure 'contained database authentication', 1; GO RECONFIGURE; GO – Mikkel Nov 08 '21 at 16:02
3

There a much easier solution now. Download an update for SSMS... at least version 14.0.17277.0. That way you don't have to play around with master keys or patching bacpac files. It's much much easier.

user7221931
  • 39
  • 2