Role Based Connections Using SSPI / Impersonating Original Caller

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

Background: I have created some extensive logic in my databases which take
the credentials of the logged on user and grant appropriate data through a
combination of views and AD group membership. This works great by using the
original caller's identity however, my boss wants me to better leverage
connection pooling.

Proposal: The proposal was that we determine the group membership, then
impersonate the appropriate domain user account (which we'd have several for
each application each with different access levels), and continue with the
connection using SSPI. (We are trying to transition to using Windows
accounts only, so using SQL accounts are out of the question at this time).

Problem: (1) We only want to impersonate the user for the connection to the
database, the rest of each application needs to run under the credentials of
the original caller. (2) The applications are already created, is there a
means to override the open method of the SQLConnection object to fullfill
step 1?

I appreciate any feedback.
 
If you want to leverage connection pooling (a good idea) then your connection
string needs to be the same for all database calls. Therefore, logic dictates
that some reengineering would be required so that the username (or role) is
passed as one of the parameters to the stored proc or sql that you use,
rather than in the connection itself.

--Peter
"Inside every large program, there is a small program trying to get out."
http://www.eggheadcafe.com
http://petesbloggerama.blogspot.com
http://www.blogmetafinder.com
 
if you use nt security with sqlserver, there is no way to specify the account
other than the thread identity.

this means you will need to impersonate the account, then open the
connection, then restore the account. you should also decorate the connection
string with the impersonation account, so the pool will not server up crossed
connections.


-- bruce (sqlwork.com)
 
Can you please clarify what you mean by "decorate the connection" and
"crossued up connections" ? Also, is it possible to override the open method
of SQLConnection object so that it will automatically take care of the
impersonation logic?
 
SqlConnection is a sealed class with no factories, so overrides are not
possible. using delegation (host an internal sqlconnection, reader,etc) you
could make your own sql class library.

if you impersonate fred and open the connection, then return it to the pool
(via close). when sam comes alongs, and gets a connection out of the pool, it
may be freds. to sqlserver he will look like fred.

to get around this, just make use of the fact that connection pools use the
connect string as a key. if you include fred in the connection string, then
other users will not get one of freds pool connections (as they use a
different pool). you can use the username parameter, because with a trusted
connection, this parameter is ignored.

-- bruce (sqlwork.com)
 
Back
Top