Automatically open keys upon connection
-
I'm experimenting with encrypting some values in SQL Server (2012). This requires that I open the appropriate key(s) for the user/role. I haven't seen any information on doing this automatically. I have written a procedure that will open keys by querying a table that associates users with keys. When I open the database in ADO.net via C#, I can simply check for the existence of the procedure and execute it before proceding. [I see that procedures can be executed when the server starts, but that's not what I want.] But it seems like there should be a standard/conventional/"best practice" way to do this at the database level so that the keys get opened even when I connect from SSMS or SSIS. Can keys be opened automatically depending on the user? Can a procedure be executed automatically when a user connects?