sa password keeps changing

[martman700]

Hi all, i have a weird problem. I have a system here in the lab with no outside access and it appears to be virus free.

I can use a front-end ASP on it no problem, but it seems as though if I leave the system up and running overnight/several hours, the sa password changes and I get a 'login failed for user sa'.

I go into query analyzer and yes that doesn't let me in either. So the password does get reset.

Nobody else in this lab, so nobody is playing jokes on me. It's been going on for a while now, i just keep resetting it daily.

There is no outside access to the server.

How could this possibly be happening? Checked the logs...nothing.

I have a mirror of this setup in production and it doesn't happen there.

Thanks.

 

[gmmastros]

Is it possible that you are restoring databases nightly? I've seen this type of thing done so that test data doesn't get too garbled with junk.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLBill]

Are you sure the password is getting reset and not just locked out?

Check your jobs. Make sure there isn't a job set to change the password.

-SQLBill

Posting advice: FAQ481-4875

 

[yelworcm]

You can run Profiler to see if sp_password is called at all. Unfortunately, you will not get a lot of information, since Profiler will not show the command, but it will tell you it was run, and maybe by whom.

 

[martman700]

Hmmm, might be on to something here, but just creates more unknowns. I'm using SQL server as the back end for an IVR application.

When I try to login with the ASP front-end, I get the error and when I go into sql query analyzer I get invalid password.

However when I phone in, my application can retrieve the data ok...using the old password.

So maybe it is a lock, but weird behaviour I'd say. I have profiler running for password change and will let it run for a day or so. I do have only one job running at 2am. and I'm not seeing where it could lock/change the password.

 

[icemel]

Better idea - script the jobs, then do a search for sp_password in the script file. I use this technique for finding stuff in the database, works great.

 

[mrdenny]

Odds are your IVR app uses persistant connections to the database. That way time is saved when getting the info from the database. It probably hasn't disconnected in a long time. I would assume that if you restart SQL and kick out the IVR's connections that it wouldn't be able to retrieve your info.

I would definetly fire up profiler and see what it's doing. Also have someone check the firewalls, router logs etc to check for unauthorized access to the system/network.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[martman700]

Thanks for the insight. Well I left profiler running last night...i'm no expert in using it.

Woke up this morning to a 'Audit Login Change Password Event'. SPID 80. Only those two columns were in the profiler report.

No real idea on what SPID 80 means and can't seem to find a reference.

Any other clues on where to look? I'd like to get more information in Profiler...will do some looking.

Thanks.

 

[SQLDenis]

SPID 80 is the ID of the connection
select @@spid to get your own
select sp_who2 to get them all

To see what statement someone is executing you can use DBCC INPUTBUFFER (spid)



“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[martman700]

I 'select @@spid' and got:

(No column name)
80


I ran 'select sp_who2' and got:

Server: Msg 207, Level 16, State 3, line 1
Invalid column name 'sp_who2'

Where should I be running this from? Sql Query Analyzer?

And where do I run DBCC INPUTBUFFER (spid) from?

I ran it from query analyzer and got:

EventType Parameters EventInfo
---------------------------------------
Language Event 0 DBCC inputbuffer(80)

 

[SQLDenis]

I am sorry just sp_who2 no select
sp_who2 (it's a stored proc)

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[martman700]

ok, ran sp_who2

says status is runnable, login is sa, it gives my DB name and the COMMAND is 'select into', seems to run every 5 minutes....but only changes the password once or twice a day.

 

[SQLDenis]

The chances of you catching the change password is minimal
I just gave you this information so that you would know how to get the statement by using DBCC INPUTBUFFER and sp_who2

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[SQLBill]

First you need to keep running profiler to see if this occurs at the same time every night. If so, then set up a job to run sp_who2 just before the time this occurs and just after the time it occurs. Save the output to a file and check it against the profile results.

-SQLBill

Posting advice: FAQ481-4875

 

[martman700]

Hmmm, well from what I'm gathering spid 80 is runnable, the login is 'sa', the program name is 'sql query analyzer'.

the command is 'select into' which doesnt' tell me much....

This means there is a job somewhere that is changing the password doesn't it...duh.

When i change the password back through EM, i get spid 72...so...no to find that job

 

[SQLDenis]

80 will not be 80 all the time

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[SQLDenis]

What I tried to say is this
SPID's below 50 are reserved for SQL server processes
SPID 80 today could be 81 or 79 or anything else when the app/person logs in again. Unless you capture the statement you won't know

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[yelworcm]

The runnable process you see is actually you running sp_who2. If you have the misfortune of delving into the code for sp_who2, you will see it selects records into a temp table. My favorite part of it has to be:

      -- (Seems always auto sorted.)   order by spid_sort

Whoever wrote that did not have 200 connections on his machine.

You may want to add audit Login, and audit Logout to your Profiler trace. That way, you can try to work back who (i.e. what machine, username, etc.) was SPID xx when the password changed.