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?

5

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

From docs.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.

  • 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 '18 at 0:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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