July 14, 2017 at 7:18 am
Hi there,
I need a solution to track down who is logging to a certain sql login.
Context: everyone in the company logs in their computers via their AD user. Everything ok.
In SQL Server, there is a SQL login which is shared among everyone, lets say it is named 'devlogin'
So, John's AD user is "CERTAINDOMAIN\jhon.redfield". He logs in SQL Server using SQL Server Authentication (not windows authentication). To do so, he fills in 'devlogin' user and password. Then he proceeds to do his work;
But Maria also logs in her computer via "CERTAINDOMAIN\maria.rose". She also uses SQL Server Authentication and fills in 'devlogin' credentials. She then proceeds to do her job.
Question: is there any solution in any SQL Server versions that I can use to know exactly what John and Maria did, separately? I don't want to log 'devlogin' actions, but the AD user who was connected behind it, you see?
Thanks in advance!
July 14, 2017 at 7:28 am
arcbzdr - Friday, July 14, 2017 7:18 AMHi there,I need a solution to track down who is logging to a certain sql login.
Context: everyone in the company logs in their computers via their AD user. Everything ok.
In SQL Server, there is a SQL login which is shared among everyone, lets say it is named 'devlogin'So, John's AD user is "CERTAINDOMAIN\jhon.redfield". He logs in SQL Server using SQL Server Authentication (not windows authentication). To do so, he fills in 'devlogin' user and password. Then he proceeds to do his work;
But Maria also logs in her computer via "CERTAINDOMAIN\maria.rose". She also uses SQL Server Authentication and fills in 'devlogin' credentials. She then proceeds to do her job.Question: is there any solution in any SQL Server versions that I can use to know exactly what John and Maria did, separately? I don't want to log 'devlogin' actions, but the AD user who was connected behind it, you see?
Thanks in advance!
Is this question related to this strange post, by any chance?
And as far as I know, the answer is no.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 14, 2017 at 8:07 am
As John Mitchell noted in the other thread,,all you can do is reasonably infer the person, based on the IP address or hostname(whihc technically can be spoofed in the connection string).
you can capture the IP address, and join that to a list of computers/ip addresses.
you can use powershell to find the last person to log into each of those machines, which is reasonably certain that it's the same person's machine all the time.
so you can capture the IP address, say it was 192.168.100.55 on your internal LAN.
you can get the machine name and last person who logged in via powershell for that IP with this script.#$computerList = $Env:COMPUTERNAME;
$computerList ="192.168.100.55"
function Get-LastLoggedOnUser($computerName)
{
Get-WmiObject Win32_NetworkLoginProfile -Computer $computerName |
Sort -Descending LastLogon |
Select * -First 1 |
? {$_.LastLogon -match "(\d{14})"} |
% {
New-Object PSObject -Property @{
Name=$_.Name ;
LastLogon=[datetime]::ParseExact($matches[0], "yyyyMMddHHmmss", $null)
}
}
}
foreach($computer in $computerList)
{
$userList += Get-LastLoggedOnUser -computerName $computer
}
$userList | Out-GridView # Export-Csv -Path C:\data\test.csv
Lowell
July 14, 2017 at 8:16 am
I did this once, when we identified that developers were using the application login as their dev login to do work. I got the management buy in that this should stop, but that it was not a show stopper. they needed to be weaned off this bad practice instead
i added a table with a scan of all workstations and last login, and a logon triger to capture just when that login was used.
finally I Added an automated DBA to Developer email,sent out once a week to each person who logged in witht eh offending sql login, with a list of the datetimes they did it. after a couple of weeks, the devs started using their own logins for work on the dev server.
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply