Who or what disabled a given SQL login?

Question

Hi,

We have several Windows 2008 R2 SP2 (64 bits) boxes running SQL 2008 R2 SP1 Standard (x64) with mixed authentication.

From time to time, I find that some SQL logins (used by our own applications) have been disabled, so I have to re-enable them manually.

I searched through both SQL logs and Windows Event Viewer, and I can't find who or what disabled these logins.

I thought about SQL Audits, but (as far as I know), they're not enabled on SQL 2008 R2 Standard.

So, how may I pinpoint what user or process is disabling these particular SQL logins? Any ideas?

Thanks in advance

---

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Answer 1

look at the SQL Error log to find the time when the login had been disabled.

together with the Windows Event Log you may find who disabled the account.

have you check if the SQL Server password policy does not disable the login?

are you sure that all logins are using the correct password?

Answer 2

Does SQL server print Login disable event into SQL error logs? I dont think so. thought it print the login failure attemtpt if you have seletect via security audit.

Apart from confirming the correct password policy, you can check in default trace file also: SELECT * FROM sys.traces will give you the path of trace file.

---

Kindly mark the reply as answer if they help

Answer 3

Hi,

Unfortunately, SQL and Windows logs say nothing on when was the login disabled and who (or what) disabled it.

Passwords are OK (at the least the ones I and my apps use), there's obviously someone (or something) trying to log with a wrong password.

I found those .TRC files, but they seem to show only the login failed events

Audit Login Failed   CIRX87 3104 Microsoft SQL Server Management Studio usrInscriptions 129 2012-04-04 08:17:19.120 1 - Nonpooled 0  0 1501119  usrInscriptions  Login failed for user 'usrInscriptions'. Reason: Password did not match that for the login provided. [CLIENT: 172.18.134.246] 1 8 18456 master 1 - Non-DAC

(CIRX87 is one of our PCs), so I don't know if the login was disabled because CIRX87 user tried to login too many times and finally locked out

our login, or if it was already locked out when he tried to login.

---

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Answer 4

Yes it has disabled becuase of number of retry attempts with wrong password. I believe Password policy is enabled on login 'usrInscriptions' and SQL server will lock the login if there are number of failure attempts which violate the password policy.

---

Kindly mark the reply as answer if they help