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.

Show quote Hide quote 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

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] wrote:
Show quoteHide quote

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

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.

Carl Daniel [VC++ MVP] wrote:
Show quoteHide quote

.NET Frameowrk's interaction with the Windows OS
Suspend/Resume obsolete?
Efficient regular expression pattern ?
Can we use dlls build with VS2005 with old apps?
How to define Type T for List<T> at runtime