SQL Server 2005: A great post by Aaron Morton about using MARS to access opened keys

Aaron Morton has a very interesting post and demo that show how MARS can be used to access keys temporarily opened by a procedure. This is a must-read for anyone that is interested in implementing custom restrictions around the use of encryption keys. Some time ago, I wrote a post about restricting the use of an encryption key just for encryption. Aaron's demo goes further and demonstrates a pitfall if one would attempt to restrict the use of an encryption key for specific data decryption. The issue happens because MARS allows interleaving around SELECT statements, and a way to prevent it is to use auto-decryption routines, which open the key in the context of the transaction rather than the session context.

Comments (2)

  1. roku says:

    link not working

    you know the other place with this demo?

  2. I don't know of another location for that demo, I see the site is still on, but it only contains entries back to Dec2010. Maybe the old posts were lost.

    From what I remember, Aaron used MARS to run in parallel both my procedure and a statement attempting to decrypt using the key being opened inside the procedure (the statement was running in a long loop). Because of the interleaved execution, his statement could temporarily gain access to the key and use it to decrypt. This was because both the procedure and the statement were running in the same user session (so they shared the user context) and their execution was interleaved, so the statement would run at some point while the procedure had the key open in the user context and get access to it.

    Hope this summary helps.

Skip to main content