This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
Cannot restore azure database to local due to missing master key
Question
I create a bacpac from azure and restore to my local on-prem server. However, it throws an error like “Please create a master key in the database or open the master key in the session before performing this operation”. The reason is one of the column in the table is encrypted. I don’t know how to restore the database from azure with master key and certificate etc. Do you know what I could do?
Thanks in advance,
asked 2017-01-04 by chung
Answer
Restoring a BACPAC isn’t the same as restoring a SQL Server native backup (created using the BACKUP DATABASE syntax).
A native backup using BACKUP DATABASE is created by exporting all schema & data by backing up at the page level into backup files. By convention, these generally have a .bak file extension. A RESTORE DATABASE does the inverse to create a database by copying the data pages out of the .bak and into the database. (It’s a little more involved than this, but that’s the big picture.) If the source database is encrypted with TDE, the data pages in the backup are also encrypted.
A BACPAC is created by generating CREATE statements for all of the schema, then doing an export of data. The .bacpac file itself is actually just a zip file of the CREATE scripts & data export. When doing a “restore” of the BACPAC, it simply executes the CREATE scripts, then does an import of the data. If the source database is encrypted with TDE, the exported data within the .bacpac file is not encrypted.
If you want to create a TDE-encrypted database using a BACPAC, simply pre-create an empty database and enable TDE on that database. Then, import the BACPAC into that empty database, and Bob’s your uncle.
answered 2018-10-15 by Andy Mallon