I am using sql server 2k5 database encryption with a symmetric key protected by password. Symmteric key password is stored in memory on the server and cannot be stored in plain text in sps.
In SQL Mgmt Studio, i run a query opening key, exec Mysp and closing key - data returns decrypted. In my c# code tho, when i want to call the same sp to read some encrypted data, i am issuing 3 separate commands since i am using stored procs to return data. In order:
- submit the SqlCommand(CommandType=Text) “open symmetric key MyKey decryption by password = ‘mypassword’” to open the key
- submit a SqlCommand(CommandType=SP) to call my stored proc with parms etc. to read encrypted data
- submit the SqlCommand(CommandType=Text) “close symmetric key MyKey” to close the key
If i issue the 3 commands in that order, the data is returned but encrypted columns are not decrypted.
The only way When i can get back decrypted data is to wrap all 3 commands in a sqlTransaction. Since some stored procs are taking a long time to return it is causing deadlocks (long running sps is another issue). i am simply reading data so dont need transactional context but it seems unless i open the key, read my data and close the key in a transaction the key does not remain open
Q. Is this the correct approach to read encrypted data from sql server in c# code?
Have searched extensively and cannot find any examples of how to open key, get data and close key in code.
Thanks in advance.