How to track a sqlserver login if password changed how to know who has changed its a 2005 standard edition

Question

How to track a sqlserver login if password changed how to know who has changed its a 2005 standard edition

naga.rohitkumar

SSCertifiable

Points: 5738
More actions
May 15, 2015 at 3:36 am

#148895

hi geeks
How to track a sqlserver login if password changed how to know who has changed its a standard edition
we just want to check how and by whom sqlserver authenticated login has been changed???
i think in sqlserver 2005 standard edition in this audit level can not be enabled right ...
is there any alternative way to track all the logins password change and its timings and also who has changed the password.
Thanks
Rohitkumar
Thanks
Naga.Rohitkumar

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 1

naga.rohitkumar (5/15/2015)
hi geeks
How to track a sqlserver login if password changed how to know who has changed its a standard edition
we just want to check how and by whom sqlserver authenticated login has been changed???
i think in sqlserver 2005 standard edition in this audit level can not be enabled right ...
is there any alternative way to track all the logins password change and its timings and also who has changed the password.
Thanks
Rohitkumar

Kind of depends, you can start by reading Aaron's article Tracking Login Password Changes in SQL Server[/url]

😎

naga.rohitkumar SSCertifiable Points: 5738 More actions · Answer 2

yes i have followed this but not working and its throwing errors

our environment has 2008 and 2005 sqlservers and they are standard versions in that case how can we move froward

thanks

rohitkumar

Thanks
Naga.Rohitkumar

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

It's a funny thing... If most people took the time to lookup the real original definition of "geek", they'd never use the word ever again.

It's also funny that "hacker" was the word that meant someone was good at computers way before "geek" became popular.

Shifting gears back to the problem at hand, if you have to set up a monitoring system to find out who changed an SQL Server login password, there may be a couple of problems that are even more interesting and should probably be corrected. Note that I'm NOT saying that auditing for SQL Server Login password changes is a bad thing. I'm saying that the sudden requirement for auditing such password changes may be due to a larger problem that needs a resolution.

1. If the password change broke something, you need to find out why because SQL Server logins shouldn't be used for applications nor anything having to do with humans or the code they've written outside of the server. They should be reserved (if at all) for the likes of creating logins for linked servers or for jobs to make it easier to tell through auditing which job made a change to rows in a table.

2. If the password change broke something and you don't actually know who changed the password, then your group is out of control. No one should ever change an SQL Server Login password unexpectedly.

3. If you want to monitor such password changes to ensure it wasn't a hacker that did it, then you have MUCH larger problems than tracking password changes. And, don't forget that a hacker isn't going to break in as themselves. They're going to try to break in as someone with sysadmin privs.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

naga.rohitkumar (5/16/2015)
yes i have followed this but not working and its throwing errors
our environment has 2008 and 2005 sqlservers and they are standard versions in that case how can we move froward
thanks
rohitkumar

It would help if you posted what you tried and the errors (full error messages). What you posted above is like going to the mechanic and saying your car is broke.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 5

naga.rohitkumar (5/16/2015)
yes i have followed this but not working and its throwing errors
our environment has 2008 and 2005 sqlservers and they are standard versions in that case how can we move froward
thanks
rohitkumar

So, which of the methods from the article did you try and what are the errors?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

naga.rohitkumar SSCertifiable Points: 5738 More actions · Answer 6

I tired to check by using this command and we can see the time changed but not who changed

[ the servers is " 10.50.4000.0SP2Standard Edition (64-bit)] in version.

USE [database]

GO

SELECT sid, [name], createdate, updatedate

FROM master.dbo.syslogins

WHERE [name] = 'login'

GO

error message which we got repeatedly is ...

Error: 18456,Severity:14,state:8

the

Login failed for user "username"reason:password did not match that for the login provided

Thanks
Naga.Rohitkumar

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

naga.rohitkumar (5/18/2015)
I tired to check by using this command and we can see the time changed but not who changed
[ the servers is " 10.50.4000.0SP2Standard Edition (64-bit)] in version.
USE [database]
GO
SELECT sid, [name], createdate, updatedate
FROM master.dbo.syslogins
WHERE [name] = 'login'
GO
error message which we got repeatedly is ...
Error: 18456,Severity:14,state:8
the
Login failed for user "username"reason:password did not match that for the login provided

Is this all you did?

Eric M Russell SSC Guru Points: 125562 More actions · Answer 8

I googled, and it appears that the Default Trace can be used to track several types of changes to login properties... but not a password change, which would seem to be the most important. So, you would have to create a custom trace.

Anyone who knows the password for a SQL Server account can change the password; it doesn't require special permissions. What you can do is monitor logins under this account. I think it's safe to assume that whomever is logging in after the password change probably changed it, or at least they know who did.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho