0

I used Column Level Encryption to encrypt one of the columns in my SQL Server database. Now everytime I want to SELECT to see that encrypted data, I have to open the symmetric key to decrypt the column and then close it.

I want to a particular user or login to always view the encrypted data. Is it possible in Column Level Encryption, if yes, how?

2 Answers 2

1

There is no out of the box way to automatically open a key for a particular user. You could create a logon trigger that opens the symmetric key. Something like this:

CREATE TRIGGER open_sec_key
ON ALL SERVER
FOR LOGON  
AS  
BEGIN  
  IF ORIGINAL_LOGIN() = '<user>'
   OPEN SYMMETRIC KEY [key_name] DECRYPTION BY CERTIFICATE [cert_name];
END;

Logon Triggers.

2
  • As per the login triggered, the symmetric key will be opened. After it has been open, I believe anyone can SELECT and see that data? And do we have to close it manually everytime the symmetric key is opened through the trigger?
    – SSS
    Commented Apr 1, 2019 at 4:38
  • As per Docs the symmetric key is opened only for that session and remains open until the key is closed or the session terminated. I've tested this and a second session that hasn't opened the key can't see the data.
    – HandyD
    Commented Apr 1, 2019 at 5:54
-1

Try the following command if you want specific user to access data on specific database

use [mydb]

GRANT VIEW Definition TO User1
3
  • Is it for the encrypted column data (using Column Level Encryption). Will granting this permission allow the user to pass the encrypted data and see the original data, even if the symmetric key is closed?
    – SSS
    Commented Apr 1, 2019 at 4:34
  • This will create encryption exception for specific user on a specfic database. so yes you can view the data anytime you want on that database without modifing encryption status over and over everytime you need to select something on that database Commented Apr 2, 2019 at 6:12
  • 1
    I tried it, it wont work if the symmetric key is closed.
    – SSS
    Commented Apr 3, 2019 at 4:20

Your Answer

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

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