SQL Server account lock out

Question

Post reply

SQL Server account lock out

PMar

Mr or Mrs. 500

Points: 569
More actions
September 9, 2011 at 3:55 pm

#243016

When password policy is checked for a SQL Server login account, after 'X' successive failed logins, the account get's locked.
How to determine the max. number of failed login attempts 'X' before an account gets locked? Is it a setting that can be controlled on the SQL Server level in order to lock an account after a limited no. of failed attempts?

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

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

That is set in the Group Policy.

Perhaps this may provide you with some insight.

http://weblogs.asp.net/cabhilash/archive/2010/04/07/login-failed-for-user-sa-because-the-account-is-currently-locked-out-the-system-administrator-can-unlock-it.aspx

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

free_mascot One Orange Chip Points: 27169 More actions · Answer 2

I guess it will be difficult to find out howmany attempts are left?

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

You can audit Login Attempts and go into the Windows Application Log and filter on the Event.

http://sqlandme.com/2011/07/11/sql-server-login-auditing/

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

PMar Mr or Mrs. 500 Points: 569 More actions · Answer 4

PMar

Mr or Mrs. 500

Points: 569

September 12, 2011 at 9:13 am

#1380105

Thanks!!

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

You can also create a Trigger at the Server Level or the Database Level depending upon what you are trying to do.

This will be a little more effort but it will probably be easier to extract & track the information.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

prithvi.m Mr or Mrs. 500 Points: 561 More actions · Answer 6

+1 for adding a trigger to specific user. Ideally trigger should be based on sys.dm_exec_sessions.