This morning I noticed that our sa sql server account was locked out, and then somehow unlocked.
9/27/2019 5:28:26 AM – Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: local machine]
…
9/27/2019 5:28:47 AM – Login failed for user 'sa' because the account is currently locked out. The system administrator can unlock it. [CLIENT: xx]
…
I replaced the client IP with xx for posting here. Then at 5:45 AM EST the account was unlocked somehow and the errors stopped.
We also receive few of these messages during the time the account was locked
The client was unable to reuse a session with SPID 413, which had been reset for connection pooling. The failure ID is 1. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{FDC3723D-1588-4BA3-92D4-42C430735D7D}
and APPID
{83B33982-693D-4824-B42E-7196AE61BB05}
to the user Domain\user SID (S-1-5-21-1482476501-1715567821-725345543-24374) from address LocalHost (Using LRPC) running in the application container Unavailable SID (Unavailable). This security permission can be modified using the Component Services administrative tool.
It looks like someone was on the server and trying to login as SA. We checked internally and everyone says they were not, we have a consultant at the moment who has his own sql server id, we asked him and he says he was not trying to login as SA during the time frame. It looks like he was logged in at the time this happened because his username is what is shown in that event log message regarding 'application-specific permission'. The consultant does not have the SA credentials.
Is there anyway I can check who was trying to login as SA and how the account ended up getting unlocked? Is there any auditing I can setup for the future in case this happens again? This is the first time we have seen this.
Best Answer
Thanks Ross and James for input in the comments. I found that our sa account has enforce password policy turned on, and our local security policy will unlock an account after 15 mins, so that answers how it got unlocked. Regarding future I think server audit of login failed event class will help us catch more information if this happens again. Internally we have a goal to move away from using sa and rename/disable it.