Importing SQL Azure database to SQL Express

This one frustrated me, but I have to say, it's pretty common to get issues it seems bringing a database down from SQL Azure to the on premise version. 

If you follow the normal route of exporting the database, downloading the bacpac, then importing it you might hit this error:

TITLE: Microsoft SQL Server Management Studio
------------------------------
Could not import package. Warning SQL72012: The object [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 [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; (Microsoft.SqlServer.Dac)
------------------------------
BUTTONS: OK
------------------------------

The cause? Well in this case it's not because you are not running a current version of SQL Server (in my case, 2016 SP1), but because SQL Azure supports a Master Key with no encryption specified - to resolve this you need to run a piece of T-SQL against your SQL Azure database to set the master key password BEFORE you export the database: 

ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = '<password>';

After that, export as normal and you should be able to import your database.

Comments are closed