Mulples threads and impersonation

[David Cablalero]

I have a windows service which every night checks a SQL Server database for
some data and business rules. The application can access different DBs with
the same structure, to tell the service which database to check I created
local users and assigned each of them a different default DB in SQL Server,
then, in the windows service I impersonate each user and then access the DB,
when the connection to the DB is made, the default DB for the impersonated
user is automatically selected, so far so good.

Now, after that, I realized that each time I connect to the DB to do the
checking, the code can take a long time to finish and impersonate the next
user, so I decided to put the code that does the impersonating and the
checking in a thread, so the service now creates a different thread for each
user it has to impersonate and then the thread impersonates its assigned
user and runs the checking process, my idea with this was that every DB
could be checked simultaneously, instead of sequentially.

Problem is, its not working!!! It seems every time one of the threads
impersonates a user, it "overwrites" the last impersonation.

Is there no way to have each thread impersonate a diferent user at the same
time??

I'm using Windows Server 2003, SQL Server 2005, Visual Studio.NET 2003 (if
necessary to upgrade to 2005, no problem!), but code should run on Windows
2000 server and SQL Server 2000 also.

Please help!!!

Thanks!
David C.

 

[Carl Daniel [VC++ MVP]]

I have a windows service which every night checks a SQL Server database for
some data and business rules. The application can access different DBs with
the same structure, to tell the service which database to check I created
local users and assigned each of them a different default DB in SQL Server,
then, in the windows service I impersonate each user and then access the
DB, when the connection to the DB is made, the default DB for the
impersonated user is automatically selected, so far so good.

Now, after that, I realized that each time I connect to the DB to do the
checking, the code can take a long time to finish and impersonate the next
user, so I decided to put the code that does the impersonating and the
checking in a thread, so the service now creates a different thread for
each user it has to impersonate and then the thread impersonates its
assigned user and runs the checking process, my idea with this was that
every DB could be checked simultaneously, instead of sequentially.

Problem is, its not working!!! It seems every time one of the threads
impersonates a user, it "overwrites" the last impersonation.

Is there no way to have each thread impersonate a diferent user at the
same time??

I'm using Windows Server 2003, SQL Server 2005, Visual Studio.NET 2003 (if
necessary to upgrade to 2005, no problem!), but code should run on Windows
2000 server and SQL Server 2000 also.

Impersonation is strictly a per-thread operation - there's no way to change
the identity of every thread in a process en-masse.

How are you doing the impersonation? How are you assessing that it's "not
working"? Have you checked in SQL Server (e.g. via SQL Profiler) to see if
the requests are being made by the impersonated users?

-cd

 

[DCaballero]

Hi Carl, thanks for replying!
Here are some code extracts:

Public Class LMSService
Inherits ServiceBase

Private Class DoTaskClass
Private sUser As String, sDomain As String, sPwd As String

Public Sub New(ByVal sD As String, ByVal sU As String, ByVal sP
As String)
sDomain = sD
sUser = sU
sPwd = sP
TaskThread = New Threading.Thread(AddressOf DoTask)
End Sub

Private Sub DoTask()
try
tokenHandle = IntPtr.Zero
Dim returnValue As Boolean = LogonUser(sUser, sDomain,
sPwd, LOGON32_LOGON_INTERACTIVE, LOGON32_PROVIDER_DEFAULT, tokenHandle)
Dim newId As New WindowsIdentity(tokenHandle)
impersonatedUser = newId.Impersonate()

'The checking code

Catch ex As Exception
WriteLog("'" + sDomain + "\" + sUser + "':" +
ex.Message + vbCrLf + vbCrLf + ex.StackTrace)
Finally
If Not IsNothing(impersonatedUser) Then
impersonatedUser.Undo()
impersonatedUser = Nothing
End If
If Not System.IntPtr.op_Equality(tokenHandle,
IntPtr.Zero) Then
CloseHandle(tokenHandle)
End If
End Try
End Sub

Public Sub StartTask()
TaskThread.Start()
End Sub
End Class


Private WithEvents LMSServiceTimer As Timer

Private Sub LMSTimer(ByVal sender As Object, ByVal e As
ElapsedEventArgs) Handles LMSServiceTimer.Elapsed
Dim LMSTask As DoTaskClass

if (DateLastCheck<DateToday) then
for each user to impersonate
LMSTask = New DoTaskClass(sDomain,sUser,sPwd)
LMSTask.StartTask
next
then
End Sub

Protected Overrides Sub OnStart(ByVal args() As String)
LMSServiceTimer.Enabled = True
End Sub

<MTAThread()> _
Public Shared Sub Main()
Dim ServicesToRun() As System.ServiceProcess.ServiceBase

ServicesToRun = New System.ServiceProcess.ServiceBase() {New
LMSService}
System.ServiceProcess.ServiceBase.Run(ServicesToRun)
End Sub

End Class

Of course I skipped a lot of things here, like the imports of the
impersonating functions, not creating new threads for the same users,
checking if the threads have finished, etc. I only put the code of the
problem I'm talking about.

I've been testing with only 2 users/databases, after the run I check
the databases and only 1 of them was affected, which one was affected
varies, I'm guessing depending on the order in which the threads did
the impersonation.

 

[Carl Daniel [VC++ MVP]]

Hi Carl, thanks for replying!
Here are some code extracts:

You've only shown one thread here. How are multiple threads created? e.g.
is there a separate instance of LMSService for each thread, or is there a
single instance that starts all the threads?

If the latter, and if this snippet represents the actual code, then your
problem lies in the use of member variables to pass information to the
thread: You've no way to know when the newly started thread has consumed
the values of sDomain, sUser and sPwd. It could be before the first call to
new Thread() returns, or it could be after you've started all your threads.

You either need to interlock thread startup so that you're guarateed that
the new thread has consumed those values before you move on, or change the
design so that unique copies of those variables exist for each thread, e.g.
by making a Task class that has a copy of those variables and implements the
"DoTask" method that's actually run on the new thread.

-cd

 

[DCaballero]

But that's exactly what I'm doing, as you can see in the code, the
sDomain, sUser and sPwd variables are private in DoTaskClass, they are
initialized with values passed to the constructor of the class, so when
each thread is started it uses its class' own variables, I thought this
way each thread would impersonate its own user. What am I doing wrong?

When you say interlock thread startup, do you mean that I start 1
thread and wait for it to impersonate before starting the next thread?
Would that be the same as impersonating the user in the main thread,
creating and starting 1 thread, impersonate the next user in the main
thread then creating and starting the next thread and so on?

David C.