9

In my journey to master the nuances of user impersonation in Windows I first had an issue about getting impersonation to a remote database to occur at all (see this SO question) but I finally figured that out. My next hurdle is undoing/cancelling/reverting (choose your favorite verb) impersonation.

I have tried a couple different impersonation libraries that seem credible to me:

The results are identical with both libraries. Best practices dictate using the LOGON32_LOGON_NEW_CREDENTIALS logon type (see the Windows API LogonUser function) for a remote DB connection. When I do that here is what my sample code produces:

// SCENARIO A
BEGIN impersonation.
Local user = MyDomain\MyUser
DB reports: MyDomain\ImpersonatedUser
END impersonation.
Local user = MyDomain\MyUser
DB reports: MyDomain\ImpersonatedUser << NOT EXPECTED HERE!!

The only workaround I have found is to use the LOGON32_LOGON_INTERACTIVE logon type and then I get this:

// SCENARIO B
BEGIN impersonation.
Local user = MyDomain\ImpersonatedUser << EXPECTED, BUT NOT WANTED!
DB reports: MyDomain\ImpersonatedUser
END impersonation.
Local user = MyDomain\MyUser
DB reports: MyDomain\MyUser

From the terse description of the WindowsImpersonationContext.Undo method it sure seems like it should have worked in Scenario A.

Is it possible to revert using the LOGON32_LOGON_NEW_CREDENTIALS logon type?

Community
  • 1
  • 1
Michael Sorens
  • 35,361
  • 26
  • 116
  • 172
  • Close and reopen the connection to the database. The database doesn't receive notification when you change impersonation levels. I can only guess that in scenario B the database client is establishing a new connection automatically. – Harry Johnston Aug 13 '13 at 02:47
  • Thanks for the comment, @HarryJohnston; I should have stated that I did, in fact, close the SQL connection and start a fresh one. – Michael Sorens Aug 13 '13 at 03:02
  • Perhaps the database client is caching the SQL connection, or more likely the underlying network connection (a named pipe?). Your best option is probably to launch a subprocess (in the context of the new token) to do the impersonated database connection for you. – Harry Johnston Aug 13 '13 at 03:15

3 Answers3

7

I dug into the internals of the connection pooling, and it turns out that Windows credentials are not considered a part of the connection pooling key at all. Only SQL logins would be taken into account.

So if there is an available connection that was opened under user A and you are now impersonating user B, it will still use it and SQL will see you as user A. The reverse is also true.

The approach of changing the connection string slightly for the two different users is fine. You might do this if you have a "normal" user and then you need to impersonate for some "elevated" user. Of course, you don't want a different string for every user of your application - otherwise you might as well disable connection pooling completely.

When tweaking your connection string, you might consider appending the impersonated username to either the Application Name or Workstation ID fields. This would have the benefit of setting up a separate pool for each impersonated user.

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
6

Thanks to input from Harry Johnston (in comments attached to the question) and Phil Harding (in separate communication) I was able to determine that SQL Server connection pooling was the culprit here. Since pooling is determined by uniqueness of the connection string, by slightly varying the connection string (e.g. reversing order of parameters within, or even just adding a space on the end) I then observed the behaviors I expected.

===== TEST WITH SAME CONN STRING: True
BEGIN impersonation
Local user: MyDomain\msorens
DB reports: MyDomain\testuser
END impersonation
Local user: MyDomain\msorens
DB reports: MyDomain\testuser <<<<< still impersonating !!

===== TEST WITH SAME CONN STRING: False
BEGIN impersonation
Local user: MyDomain\msorens
DB reports: MyDomain\testuser
END impersonation
Local user: MyDomain\msorens
DB reports: MyDomain\msorens  <<<<< this is what I wanted to get
Michael Sorens
  • 35,361
  • 26
  • 116
  • 172
  • Something else must be at work. In that very link you provided, it says "Connections are separated into pools by connection string, and by Windows identity when integrated security is used.". Can you show some code? – Matt Johnson-Pint Aug 17 '13 at 22:09
  • 1
    My interpretation in that statement is that "Windows identity" means what `System.Security.Principal.WindowsIdentity.GetCurrent().Name` returns (what I report as `Local user` in my answer above). That is, when using LOGON32_LOGON_NEW_CREDENTIALS it is _not_ changing the windows identity and thus, dips in the same pool. Do you agree? – Michael Sorens Aug 18 '13 at 04:29
  • I don't think that's the case, but I will do some testing and find out. It's easy enough to watch the credentials in SQL profiler. – Matt Johnson-Pint Aug 18 '13 at 05:10
  • 1
    I didn't want to believe it, but your assessment appears to be correct. The connection pooling key is not updated for the impersonation. In testing, I found that if I disabled connection pooling with `Pooling=false` in the connection string, it then worked. So it can't be just pulling the current windows identity. Clearly you wouldn't want to disable pooling in production, so there has to be another way. If you are only impersonating for a particular reason (security elevation, for example), then having a slightly different connection string sounds ok. – Matt Johnson-Pint Aug 18 '13 at 22:19
  • 1
    I know I'm a little late to the party, but I have a question... At the project I'm currently involved in, we are simply using LOGON32_LOGON_NETWORK to make the pooling play nice. Pooling is actually aware of identity (see [here](http://www.pythian.com/blog/sql-server-understanding-and-controlling-connection/) and [here](https://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx)), but LOGON32_LOGON_NEW_CREDENTIALS doesn't change identity and that's why pooling misbehaves there. Are you aware of any negative consequences of using LOGON32_LOGON_NETWORK when connecting to SQL Server? – Branko Dimitrijevic Jul 21 '15 at 09:17
  • Does this same issue affect ADOMD.NET? – kevinarpe Feb 13 '17 at 05:03
0

I have found that the login type LOGON32_LOGON_NETWORK_CLEARTEXT does not have a problem with connections being re-used across impersonation contexts and works as expected without varying the connection string.

According to this thread, the "cleartext" part of this login type seems to be local to the server. I only keep the token alive for the duration of the database query or set of queries, so the token is very short-lived. Using this login type for long-lived tokens may or may not be a security risk.

Matt Miller
  • 443
  • 4
  • 10