SQL Server 2005 Encryption, asp.net and stored procedures -

i need write web application using sql server 2005, asp.net, , ado.net. of user data stored in application must encrypted (read hipaa).

in past projects required encryption, encrypted/decrypted in application code. however, encrypting passwords or credit card information, handful of columns in couple tables. application, far more columns in several tables need encrypted, suspect pushing encryption responsibilities data layer better performing, given sql server 2005's native support several encryption types. (i convinced otherwise if has real, empirical evidence.)

i've consulted bol, , i'm adept @ using google. don't want links online articles or msdn documentation (its i've read it).

one approach i've wrapped head around far use symmetric key opened using certificate.

so 1 time setup steps (performed dba in theory):

1. create master key
2. backup master key file, burn cd , store off site.
3. open master key , create certificate.
4. backup certificate file, burn cd , store off site.
5. create symmetric key encryption algorithm of choice using certificate.

then anytime stored procedure (or human user via management studio) needs access encrypted data have first open symmetric key, execute tsql statements or batches, , close symmetric key.

then far asp.net application concerned, , in case application code's data access layer, data encryption entirely transparent.

so questions are:

1. do want open, execute tsql statements/batches, , close symmetric key within sproc? danger see is, if goes wrong tsql execution, , code sproc execution never reaches statement closes key. assume means key remain open until sql kills spid sproc executed on.

2. should instead consider making 3 database calls given procedure need execute (only when encryption necessary)? 1 database call open key, second call execute sproc, , third call close key. (each call wrapped in own try catch loop in order maximize odds open key closed.)

3. any considerations should need use client side transactions (meaning code client, , initiates transaction, executes several sprocs, , commits transaction assuming success)?

1) using try..catch in sql 2005. unfortunately there no finally, you'll have handle both success , error cases individually.

2) not necessary if (1) handles cleanup.

3) there isn't difference between client , server transactions sql server. connection.begintransaction() more or less executes "begin transaction" on server (and system.transactions/transactionscope same, until it's promoted distributed transaction). concerns open/closing key multiple times inside transaction, don't know of issues aware of.