Transparent Data Encryption with Azure Key Vault

Bradley Schacht

Bradley Schacht is a Cloud Solution Architect on the state and local government team with Microsoft based in Jacksonville, FL. He has co-authored 3 SQL Server books including "SQL Server 2014 Professional Administration". As a former consultant and trainer, he uses his experience on many parts of the Microsoft BI and data platform to help customers deliver the best possible solutions. Bradley frequently presents at community events around the country. He is a contributor to sites such as SQLServerCentral.com and an active member of the Jacksonville SQL Server User Group (JSSUG).

You may also like...

9 Responses

  1. Maciej says:

    Hello, Bradley.
    Thanks for perfect instructions, they really work!
    I’m able to encrypt my database with Azure Key Vault but I have problems with restoring it on another server. I did everything you mentioned on the second server: created logins and credentials, created symmetric key that I used before, etc. Now I can attach encrypted database taken from the first server and it works OK but I can’t restore database from backup file: “no backupset selected to be restored”.
    Is it a bug or I did something wrong?

    Best regards
    Maciej Ciurla, Unit4

    • I just want to make sure I have this right. You are able to detach and then reattach the database files from one server to another but you are unable to take a backup and restore it to another server?

      Was the backup taken before or after TDE was applied to the database? Also, have you tried doing the restore with T-SQL or just through the GUI?

  2. Maciej says:

    I’ve checked it once again: yes, I can detach and attach database from one server to another after importing asymmetric key. I CANNOT restore backup of the same database using both GUI and T-SQL.
    I found news from MS https://support.microsoft.com/en-us/help/4024305/cumulative-update-4-for-sql-server-2016-sp1 that “Restore fails when you do backup by using compression and checksum on a TDE enabled database in SQL Server 2016” and applied CU4 to my servers but it still doesn’t work :-((

  3. Maciej says:

    Hi, Bradley. Do you have any good information for me?
    best regards
    Maciej

    • Sorry for the delay. I ran through everything on a couple servers in my demo environment and I was able to take the backup from the first server (with TDE enabled) and move it over to the new server. I tried a restore before running the appropriate scripts to connect to Key Vault and it failed as expected. I then ran through the scripts, did the restore and it worked flawlessly.

      You basically need to run through everything up to the point of changing the context over to the encrypted database on the secondary server before doing the restore. All the commands run at the master database should be performed ahead of trying to do a restore on the second server.

  4. Maciej says:

    Thanks a lot for your time and help 🙂

  1. August 15, 2017

    […] recently wrote a post about using Transparent Data Encryption (TDE) with Azure Key Vault as an alternative to managing certificates. Today’s post will explore using SQL […]

  2. April 19, 2018

    […] have previously written about using Transparent Data Encryption (TDE) with Azure Key Vaule as a great way to store and manage encryption keys for SQL Server. With Azure SQL Database there […]

Leave a Reply