I'm trying to create a way to encrypt/decrypt data in SQL Server so that my front end application (done in MS Access) doesn't have to mess with any encryption processes (other than providing a password). I have gotten to where it works very well using UDFs connected to assemblies that have the encryption password hardcoded into them. However, I would like to make it so that the encryption password isn't hardcoded into anything, which means that my Encrypt/Decrypt functions would need the password passed to them as a parameter.
I have no idea if this is possible, but here is my idea. When the user opens up the front end application, they type in a password that gets sent to the SQL Server that is then stored in a variable that is used by the update/insert triggers on the view so that the password can then be passed to my encrypt/decrypt UDFs for the entire time the application is open, so any reads/writes already have the password stored in the variable. I would want it to be like a global variable that is limited to the current ODBC connection. So, once a connection is dropped (the application is closed) the variable is no longer populated with the password. Once I figure out the SQL Server end of it, I think that I can get my application to send the information over fine, but I'm not sure how to setup the "semi-global" variable.
Note about the encryption: Per our auditors, I'm only worried about the data at rest, so I don't need to worry about the data in transit over the network.
I have no idea if this is possible, but here is my idea. When the user opens up the front end application, they type in a password that gets sent to the SQL Server that is then stored in a variable that is used by the update/insert triggers on the view so that the password can then be passed to my encrypt/decrypt UDFs for the entire time the application is open, so any reads/writes already have the password stored in the variable. I would want it to be like a global variable that is limited to the current ODBC connection. So, once a connection is dropped (the application is closed) the variable is no longer populated with the password. Once I figure out the SQL Server end of it, I think that I can get my application to send the information over fine, but I'm not sure how to setup the "semi-global" variable.
Note about the encryption: Per our auditors, I'm only worried about the data at rest, so I don't need to worry about the data in transit over the network.