how to use sql encryption via an asp site. is this correct?

ive been playing around with encryption, just encrypting a column with a symmetric key, and having that symmetric key encrypted with a cert, and all that , and it works well through the SSMS, but i want to know how that will be used if im using sql server as a back end database for an asp web site, or an application. does the sql server have to be protected with an SSL cert, and once a user has logged in, what would the steps be to view encrypted data?

lets say i have an asp.net site wtih forms authentication, where the username and password are checked against values stored in a table, all done via https so the password data cant fall into the wrong hands. once the user is authenticate this is my idea:

each user has a status, stored in the status column of the users table : Admin or RegularUser.

if the user status is an Admin, and clicks on something that returns encrypted data, a sproc checks the user has admin status and uses impersonation to impersonate a login that has control to use the encryption key that encrypted the data. so the data is returned unencrypted.

is this a valid way of using encryption via asp.net, or are their security issues?

|||

SSL protects the communication with the server; the encryption you experimented with is for protecting the data within the server. For the steps required to view the encrypted data, it is up to you to determine those in your database application - you can have the user manually decrypt or you can have him access the data via views that automatically decrypt.

Thanks
Laurentiu

|||

You can provide access to data via a view that automatically decrypts using decryptbykeyautocert, for example. You can then group your Admin users in a role and grant permissions to that role to use the key. Admin users would then be able to decrypt the data and see it; regular users would not be able to.

Thanks
Laurentiu

|||that makes sense. thanks. one thing though. as the only account to access the sql server is the account the website is running under, how can i grant different groups of users different security levels? could it be as simple as once they log into the site ( which checks they are in the users table and checks a col called SecurityLevel) and if they have a level of "Admin User", the site will call sprocs for them that uses impersonation of a login that has admin access, where as if they only have "standard User" security level, the site will call sprocs that uses a login with low priviledges via impersonation to get their data. is this a viable solution or is it a round about way of doing something that could be much easier?