none
TDE Encrypted Database Won't Mount - How Can I Disable TDE to Run the DB on SQL Standard?

    Question

  • I'm by no means an expert in SQL Server.

    Until this evening I had a SQL Server 2016 in pre-production which was available to developers to spin databases up on. I eventually go around to upgrading the Enterprise evaluation licenses to our VLK for SQL Server Standard. 

    It turns out one of the developers had a database on the server encrypted with TDE. Is there any easy way for me to disable the TDE encryption so the database can be mounted on SQL Server Standard? I just get a horrible error stating that the feature is not supported, which I already know.

    I'm beginning to think it might be best for me to install another instance of SQL Server Enterprise on the server, import the TDE encrypted database, do a backup and restore, job done. 

    Would any SQL guru's out there highlight any problems with this? Or give me a method to get rid of the TDE without another instance etc. Everything is untouched since before the upgrade to the proper licenses, so the certs etc are all intact.

    Wednesday, November 21, 2018 7:25 PM

Answers

  • Yeah managed to get the encrypted database mounted on a new instance of SQL Server running on Developer. 

    Had to go through this process to migrate the certificate to the new instance

    https://www.databasejournal.com/tips/how-to-move-a-tde-encryption-key-to-another-sql-server-instance.html

    Worked a treat. Now the database is mounted, I've reconfigured the application front end and it's working so panic over. 

    The next step is to disable TDE, backup the database, then restore it to the production instance with a proper license. 

    • Marked as answer by RyanBetts Wednesday, November 28, 2018 10:11 AM
    Wednesday, November 21, 2018 11:05 PM
  • For anyone who might be interested, I've written a blog post which outlines the exact fix.

    http://blog.ryanbetts.co.uk/2018/11/tde-encrypted-database-wont-mount-on-sql-server-standard.html


    • Marked as answer by RyanBetts Thursday, November 29, 2018 8:04 AM
    Wednesday, November 28, 2018 11:39 AM

All replies

  • Without an instance of Enterprise/Developer, you are not going far with this database. You also need the certificate that encrypted it.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, November 21, 2018 10:07 PM
  • Thanks, thought that might be the case.

    Wednesday, November 21, 2018 10:12 PM
  • Yeah managed to get the encrypted database mounted on a new instance of SQL Server running on Developer. 

    Had to go through this process to migrate the certificate to the new instance

    https://www.databasejournal.com/tips/how-to-move-a-tde-encryption-key-to-another-sql-server-instance.html

    Worked a treat. Now the database is mounted, I've reconfigured the application front end and it's working so panic over. 

    The next step is to disable TDE, backup the database, then restore it to the production instance with a proper license. 

    • Marked as answer by RyanBetts Wednesday, November 28, 2018 10:11 AM
    Wednesday, November 21, 2018 11:05 PM
  • Hi,

    Please remember that the encrypting certificate should be retained even if TDE is no longer enabled on the database. Even though the database is not encrypted, parts of the transaction log may still remain protected, and the certificate may be needed for some operations until the full backup of the database is performed. About how to remove transparent data encryption from a database, you can refer to the following link.

    https://www.databasejournal.com/tips/how-to-remove-transparent-data-encryption-from-a-database.html


    Best Regards
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, November 22, 2018 3:14 AM
  • Thanks. Yeah I would not be deleting the certificates from the SQL instances.


    Thursday, November 22, 2018 8:07 AM
  • Hi,

    If you have resolved your issue, please remember to close the thread by marking the useful replies as answers. And it will be easier for other community members to find the useful answers.

    In addition, if you have another questions, you could open a new thread and feel free to ask.

    Thanks for your contribution.


    Best Regards
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, November 22, 2018 8:09 AM
  • For anyone who might be interested, I've written a blog post which outlines the exact fix.

    http://blog.ryanbetts.co.uk/2018/11/tde-encrypted-database-wont-mount-on-sql-server-standard.html


    • Marked as answer by RyanBetts Thursday, November 29, 2018 8:04 AM
    Wednesday, November 28, 2018 11:39 AM