1

I have implemented data encryption using SQL Server symmetric key encryption technique using a certificate, master key with password.

When we backup database from PROD server and restore to a different server, we open the master key using OPEN MASTER KEY DECRYPTION BY PASSWORD = ''... statement.

Password for master key is being managed by a SQL admin.

After opening the master key, we are able to decrypt the column data using DecryptByKey function. However, once we have done some analysis, we want to stop decrypting the data. No developer should be able to decrypt the data after a while.

How to stop decrypting the data or close the master key so that no developer can see data in plain-text?

Improve this question

1 Answer 1

Reset to default
5

The command you're looking for is CLOSE MASTER KEY;

From learn.microsoft.com:

This statement reverses the operation performed by OPEN MASTER KEY. CLOSE MASTER KEY only succeeds when the database master key was opened in the current session by using the OPEN MASTER KEY statement.

Improve this answer
1
  • Thanks for your answer. I tried this statement earlier and it didn't prevent decrypting data. Could you please share some suggestions on how to prevent decryption for whole database when desired?
    – Sri
    Sep 28, 2018 at 0:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.