5

I am having problems trying to audit a third party app with a SQLServer backend.

The app manages users and user access via a SQL table, using a single SQL login to access the databases.

I am trying to audit this by using the Host PID much like Windows Task Manager associates a PID and Owner for every process.

Here is what I have attempted and tried

  • I was able to figure out how to extract the app.exe PID.
  • I cannot figure out how to get the Windows Owner associated to that PID.
  • I have tried using xp_cmdshell to query the Windows tasklist and even wrote a .Net console app which gets called by SQL to collect the information but every time I try to extract the Owner it is blank.

Any thoughts on how I can get the Owner?

  • We went through this same exercise... as that 3rd-party app vendor; single SQL login, Windows apps, need to audit the user that's logged in. We ended up modifying the software to collect the data we need. – Jon Seigel Sep 7 '12 at 21:49
  • If the app is run from the users desktop (as opposed to a web app for example) then you could use the hostname to identify which workstation the connection is coming from. – Kenneth Fisher Jul 1 '13 at 20:00
1

I cannot figure out how to get the Windows Owner associated to that PID.

You can get the owner using PowerShell:

# Identify the name of the remote computer you want to query
$computerName = 'SomeRemoteComputerName'
# Invoke the command on the remote system to get the information that is necessary
Invoke-Command -ComputerName $computerName -ScriptBlock {
    # Build a hashtable that associates process ids with owners
    $processOwners = @{}
    Get-WmiObject -Class Win32_Process | ForEach-Object {
        $processOwner = $_.GetOwner()
        # Combine the domain and user information together to get the process owner
        $processOwners[[int]$_.ProcessId] = $processOwner.Domain + '\' + $processOwner.User 
    }
    # Now get all processes and add the owner information to them
    Get-Process | ForEach-Object {
        $processOwner = $null
        # If we have process owner information for the process, look up the owner in the table
        if ($processOwners.ContainsKey($_.Id)) {
            $processOwner = $processOwners[$_.Id]
        }
        # Add the owner information to the current process object
        Add-Member -InputObject $_ -MemberType NoteProperty -Name Owner -Value $processOwner
        # Return the current process object from the script block
        $_
    } | Select-Object Name,Owner,Description
}

script source

0

I assume your app is doing the user/password check as you said it manages the users. With extended events you can audit the login and every needed field.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.