Best practices for concurrency when using SQL Server encryption

When using encryption in SQL Server with symmetric keys, one needs to open the keys, and then one's supposed to close them afterwards.  This would seem to create massive concurrency issues, because if a web application using a pooled connection is constantly opening and closing keys, it seems that the potential is high that one would get an error that one can't open the key because it's already open (because, say, another web user is calling a stored procedure that has already opened that key).  And if one somehow allows the code to go through anyway with try/catch logic, that's even worse, because then one stored procedure could close a key before another one has finished using it.

It seems that this must be an issue many people have encountered, so I don't want to reinvent the wheel here.  What are the best practices for avoiding concurrency problems in SQL Server when opening and closing keys?  Do I simply need to have each stored procedure manage concurrency separately (say by calling sp_getapplock in every procedure that's going to use keys), or is there some cleaner solution for use with keys that's more centralized/manageable?  (I searched for articles on encryption keys and concurrency issues but didn't find anything.)

Thanks!
NewbisAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David Johnson, CD, MVPOwnerCommented:
Assumption Column Level Encryption:

the data is stored unencrypted in memory each process has their own memory copy. only if you change something things depend upon your locking policy. opportunistic et al. when the second item wants to change the same table item.it first reads the item and if changed then the write fails..

are you using .net encryption? why not column level encryption or Transparent Data Encryption (sql level)
0
NewbisAuthor Commented:
I'm using column-level encryption in SQL Server.  That's why I have to keep calling the OPEN SYMMETRIC KEY command.  Transparent data encryption requires the enterprise edition....plus, I think column-level encryption better matches our business need.

This is an ASP.NET application that uses Entity Framework to call stored procedures.  So there's really only one database user account for the application.

So I guess the key isssues are:
1) Is there a potential for conflict, or not?  Say two users trigger actions that call different stored procedures at about the same time, each of which call the OPEN SYMMETRIC KEY command for the same key.  Is it possible then that one of the users would get a key-already-open error, or is there something built-in that prevents this?

2) Is there a "cleaner" solution to this than having every single procedure that uses these keys use sp_getapplock to control access?
0
NewbisAuthor Commented:
By the way, I should clarify:  In my testing, actually there is no "already-open" error.  But if I try to close a key twice, then I get the following:

Msg 15315, Level 16, State 1, Line 1
The key 's1' is not open. Please open the key before using it.

I tested just in a query window and found that in that case, everything is synchronous...obviously it's the same thread, so it will just call the procedure after it has finished.  But I guess to be 100% confident, I would need to test this with the web server and browser on two different machines hitting it, to see if it's possible that EF could call one stored procedure for one user before the stored procedure for another user has finished, to the effect of creating the possibility of this Msg 15315 above, or even worse, having one procedure close the key before another one has finished with it.

create procedure testConcurrency
as
OPEN SYMMETRIC KEY S1 DECRYPTION BY
ASYMMETRIC KEY a1 WITH PASSWORD = N'[mypassword]'; 
waitfor delay '00:00:08'
CLOSE SYMMETRIC KEY S1
GO
create procedure testConcurrency2
as
OPEN SYMMETRIC KEY S1 DECRYPTION BY
ASYMMETRIC KEY a1 WITH PASSWORD = N'[mypassword]'; 
CLOSE SYMMETRIC KEY S1
GO

exec testConcurrency
exec testConcurrency2 --This doesn't cause any problem since it's all in the same process

Open in new window

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

NewbisAuthor Commented:
One thing that gives me a little more confidence that maybe things aren't so bad is that in the following test, it seems that if a procedure gets an unhandled error before it can close an encyption key, the key still isn't left open.  I get the message Msg 15315 error, even though the procedure gets an unhandled dvide-by-zero error, meaning that somehow the state of keys may have scope only within the procedure itself...which would mean that there's no potential concurrency problem.  But I guess that's what I want to be absolutely sure about.

(PS - Maybe my test here isn't comprehensive enough, unless the scoping of keys has changed in the last few years.  It seems to contradict this article about scoping:  http://blogs.msdn.com/b/sqlsecurity/archive/2007/11/29/open-symmetric-key-scope-in-sql-server.aspx .  I guess reading further, I can avoid problems on the decryption side by using DECRYPTBYKEYAUTOCERT.  However, there seems to be no corresponding command on the encrypt side.)

alter procedure TestFail
as
OPEN SYMMETRIC KEY S1 DECRYPTION BY
ASYMMETRIC KEY a1 WITH PASSWORD = N'[password]'; 
select 1/0
CLOSE SYMMETRIC KEY S1
go

exec TestFail
close symmetric key s1
select 'It got here'

Open in new window

0
Scott PletcherSenior DBACommented:
You can use view sys.openkeys to determine if that key is open or not.  Naturally then don't CLOSE it unless it is currently open :-).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NewbisAuthor Commented:
Thanks..I'll try that.  I guess I could check if the key is already open under the same user account, and then I'll have to handle what to do if it is.  I'm going to set this up in something closer to a production environment so I can test these things and see what really happens.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.