Share via

Always Encrypted Testing

David Chase 681 Reputation points
2020-10-12T11:44:16.787+00:00

We want to test setting up an Always Encrypted database and set columns as needed. Can we revert back to unencrypted after testing is done?

SQL Server | Other
SQL Server | Other

Additional SQL Server features and topics not covered by specific categories

0 comments

Answer accepted by question author

Erland Sommarskog 134.6K Reputation points MVP Volunteer Moderator
2020-10-12T21:31:52.073+00:00

Yes, you can backup the database prior to testing and then restore a backup. (You don't have to drop the database first.) Everything about Always Encrypted is stored inside the database, so if you restore a backup, all traces are gone.

Was this answer helpful?

0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 134.6K Reputation points MVP Volunteer Moderator
    2020-10-12T21:18:24.607+00:00

    As I recall, you can use the Always Encrypted wizard to revert. It is the same process: the data has to be read to the client layer where it is encrypted/decrypted and then written back to the updated schema. (That is, when you make a column encrypted with Always Encrypted, you are changing the schema of the table.)

    There is also a cmdlet for the task, I believe.

    Nevertheless, I would recommend that you conduct your testing in a testing environment that you can throw away if things go south.

    Was this answer helpful?

    0 comments
  2. Evgenij Smirnov 541 Reputation points
    2020-10-12T13:19:36.29+00:00

    Not to my knowledge (which is limited :-) ). You have to drop and re-add the encrypted columns. Mostly you will just add a second table, copy the data decrypting is as you go then drop the table with encrypted columns and rename the unencrtypted table.

    Was this answer helpful?

    0 comments
  3. David Chase 681 Reputation points
    2020-10-12T13:04:01.977+00:00

    Will this same process work since we aren't using TDE?

    Was this answer helpful?

    0 comments
  4. Tom Phillips 17,786 Reputation points
    2020-10-12T12:35:11.597+00:00

    Was this answer helpful?

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.