Login failures after 2005 upgrade

Question

Login failures after 2005 upgrade

T_Peters

Hall of Fame

Points: 3052
More actions
March 5, 2008 at 5:02 pm

#179796

Greetings,
I recently upgraded from 2000 to 2005. Everything looked like it went well and the connecting applications seem to be running just fine. The problem is I'm finding many "Login failure for user..." and "Error: 18456, Severity: 12, State: 16." entries in the SQL Server logs. It's really a lot, averaging 10 to 15 failures a minute. The user is a Windows service account. I've removed the account and readded it, granting it Sys Admin privileges, but I'm still getting these failures. I can RDC into this server with this account and access everything.
I've got 2 questions.
1) How can I get getting these failures when the account has sys admin privileges?
2) Is there any way to track back to where these logins are coming from?
Thanks,
Tim

Viewing 11 posts - 1 through 10 (of 10 total)

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

Scalability Doug SSCertifiable Points: 5351 More actions · Answer 1

I ran into this issue too after upgrading to 2005 and then applying Windows SP2. It turns out that doing so enabled the "Enforce Password Policy" and other checkboxes. Check that these settings in security.

DAB

Adam Bean One Orange Chip Points: 26631 More actions · Answer 2

Your database user ID's no longer match your server login ID's, this is known as an orphaned user. In the case of a sysadmin, you shouldn't have that problem though ...

To fix orphaned users, run this:

sp_msforeachdb

'USE ?

DECLARE @username varchar(25)

DECLARE fixusers CURSOR

FOR

SELECT UserName = name FROM sysusers

WHERE issqluser = 1 and (sid is not null and sid <> 0x0)

and suser_sname(sid) is null

ORDER BY name

OPEN fixusers

FETCH NEXT FROM fixusers

INTO @username

WHILE @@FETCH_STATUS = 0

BEGIN

EXEC sp_change_users_login ''update_one'', @username, @username

FETCH NEXT FROM fixusers

INTO @username

END CLOSE fixusers

DEALLOCATE fixusers

'

The application utilizing this account, is it a service? Did the password recently change and did the service not get updated?

Scalability Doug SSCertifiable Points: 5351 More actions · Answer 3

Adam, when I encountered this problem it was not oprhaned users. We had been on SQL 2005 for 6 months when the problem arose. I wound up contacting MIcrosoft PSS and they informed me that the Windows SP2 changes the "Enforce Password Policy" settings. Sure enough, I looked at this and all were "checked". After unchecking them the login failures disappered.

DAB

Adam Bean One Orange Chip Points: 26631 More actions · Answer 4

doug@scalabilityexperts (3/6/2008)
Adam, when I encountered this problem it was not oprhaned users. We had been on SQL 2005 for 6 months when the problem arose. I wound up contacting MIcrosoft PSS and they informed me that the Windows SP2 changes the "Enforce Password Policy" settings. Sure enough, I looked at this and all were "checked". After unchecking them the login failures disappered.
DAB

Yeah, I had the same problem when we first moved to 2k5 as well ... I hate that check box!!! 🙂

T_Peters Hall of Fame Points: 3052 More actions · Answer 5

DAB - This user is a Windows account, the Enforce Password Policy and other checkboxes are greyed out when I select the Properties from Security > Logins > user. Should I be looking somewhere else?

Adam Bean - I ran the following on each db to try and find orphaned users, but didn't return any results.

USE ;

GO;

sp_change_users_login @Action='Report';

GO;

The other problem is that I don't know where these attempts to login are coming from. I see the failures in the log, but I don't know what application is making them. Is there some way to track this backwards?

Thanks,

Tim

Adam Bean One Orange Chip Points: 26631 More actions · Answer 6

Tim, did you see my comment above?

The application utilizing this account, is it a service? Did the password recently change and did the service not get updated?

The fact that you can access this SQL server with the NT account (RDP/Run As) really makes me think this might be your problem.

T_Peters Hall of Fame Points: 3052 More actions · Answer 7

Adam,

I'm not sure what application is using this account. The applications appear to be working correctly so I don't know where these attempts to login are coming from. I see the failures in the log, but I don't know what application is making them. Is there some way to track this backwards?

Adam Bean One Orange Chip Points: 26631 More actions · Answer 8

Tim Peters (3/6/2008)
Adam,
I'm not sure what application is using this account. The applications appear to be working correctly so I don't know where these attempts to login are coming from. I see the failures in the log, but I don't know what application is making them. Is there some way to track this backwards?

Yes, use profiler and edit your filter to only track that particular login. You should be able to get the hostname from where it is coming from.

T_Peters Hall of Fame Points: 3052 More actions · Answer 9

Thank you!

I ran the profiler with the Audit Login Failed event class and got the application name. It was a monitoring application that was supposed to be removed prior to the upgrade. The database that served it was removed and the instance was upgraded. Unfortunately the application was never uninstalled, till now.

Thanks,

Tim

Susantha Bathige Ten Centuries Points: 1040 More actions · Answer 10

What is the version of your master database? I mean, is it the same database that you use in SQL Server 2000? Because all your earlier logins are stored in master database.

Susantha