All right, I'm tired of spewing knowledge (or whatever it is I spew). I've heard rumours of a feature in SQL 2005 that might interest me and I'm in no mood to look up the details.

I was recently involved in a discussion where someone wanted to connect to a database from a web service. In it, he wanted to use impersonation (for obvious reasons) but also connection pooling (for obvious reasons). Essentially, the requirement was to use the user's credentials for everything within the web service except database actions where he would switch to a single user to allow for connection pooling. Yes, it's possible with a SQL user but we don't want to do it the easy way, do we? We've always been told SQL Authentication is bad (even though we use it anyway for demos and prototypes and never get around to changing it in production).

Anyway, I don't care about the solution to this problem. We drilled the use of the LogOnUser API. We talked about trade-offs 'twixt connection pooling and security measures. We discussed scaling in more detail than a Head & Shoulders commercial. In short, I don't care if you have a clever solution.

What I am interested in is this new-fangled (and, to me, unconfirmed) feature thingy in SQL 2005. Apparently, there is a way to use the following scenario:

  1. User authenticates to web application/service
  2. Web app/service connects to SQL using a single pre-defined Windows account
  3. Web app/service passes user's security context to SQL
  4. SQL impersonates authenticated user for all database actions

This is some kind of wackiness, it is. So, my loyal and, I pray, giving readers. Is this possible? The Coding Hillbilly wishes enlightenment.