user locking

Question

user locking

pmadhavapeddi22

SSCertifiable

Points: 5306
More actions
May 5, 2009 at 6:53 am

#208964

Hi,
user is getting locked very frequently in sql 2005 and it is saying sa can unlock the user. I am the sa and I am trying to unlock using
Alter login login name with PASSWORD = 'pwd_123' UNLOCK
but it is occurring repeatedly. Please help to fix this permanently
Thanks
madhava

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Obviously you are using at least SQL Server 2005 so you posted in the wrong forum (2000).

Is this a shared login? Is this login used by some type of web application where a configuration file needs to be updated?

Could your database be under attack? Check the default trace for the Audit Login Failed event like this:

SELECT

TE.[name],

I.TextData,

I.ApplicationName,

I.HostName,

I.SessionLoginName,

I.LoginName

FROM

sys.traces T CROSS Apply

:: fn_trace_gettable(CASE WHEN CHARINDEX('_', T.[path]) 0

THEN SUBSTRING(T.PATH, 1,

CHARINDEX('_', T.[path]) - 1) +

'.trc'

ELSE T.[path]

End, T.max_files) I JOIN

sys.trace_events AS TE ON

I.EventClass = TE.trace_event_id

WHERE

T.id = 1 AND

TE.NAME = 'Audit Login Failed'

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 2

Hi Jack

Thank you so much for the quick response!!!

yes, this is a shared login in our office env and they access web application

I have got so many records after executing the query provided by you

How should I proceed further to fix this

Thanks

madhava

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

First where are the failed logins coming from (HostName, Application Name, and IP Address in TextData)? If all the failures are coming from the web server then you need to check the application configuration to make sure it is using the right password. If it is coming from elsewhere you need to determine who is trying to access the database using the shared login. If they are internal you need to make sure the application configuration information is locked down so that you can change the password in SQL Server and have it changed in the application configuration. If it si coming from an external source you need to work with your system/network admin to lock down the network and SQL Server.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 4

Hi jack,

This is happening even if I connect to sql2005 through enterprise manager. Login is getting locked and I am unlocking it thats all and when I changed the password app team is also changing the password in configuration file

Thanks

madhava

Steve Jones - SSC Editor SSC Guru Points: 736430 More actions · Answer 5

If the login is getting continually locked, someone is still logging in with the wrong password. If this is a web app, you might need to restart the IIS app pool to be sure that it's using the changed password.

Profiler will help you determine where the logins that fail are coming from.