Question about encrypting a current App.

When we convert an existing app to start encryption and decrypting data as needed, I am required to make changes to atleast 50 stored procs. Within each of these SPs I do:

-- OPEN SYMMETRIC KEY

-- do the ecrypt or decrypt operation

-- CLOSE SYMMETRIC KEY

Is it possible to not use these steps in each of my SP's? i.e, can we generalize the open and close key calls somewhere else? Any tips are greatly appreciated. TIA.

You can open the symmetric keys as part of connecting to the database and close them as you log out. Anything executing in that context will have access to the keys and be able to encrypt and decrypt with them.

If your application opens/uses multiple connections on behalf of a user and is supposed to encrypt/decrypt on all of them, you would need to open the keys when you open each connection and close them when you cease using a connection. If connections are not pooled, you don't need to close the keys if you're going to kill the connection anyway.

Thanks
Laurentiu