Yesterday early morning I got a call that the SQL Server Agent on a SQL Server 2000 instance was not starting after a scheduled reboot of the server. I was told that there was an error message in SQLAgent.out file which read Login failed for user ‘sa’. Instead of relying on that information I got on to a remote help session with my team member.
I could see that whenever we tried starting the SQL Server Agent service, SQLAGENT.out file had this information in it.
This server belongs to a Line of Business where we have been hardening the servers of late. Hence the first thing I checked that the service account had “sa” privileges on that instance, assuming that we had revoked access to BuiltinAdministrators and did not grant the new service account sufficient privileges. Since this instance is SQL Server 2000 this article explains that lack of privileges can result in such errors. But the SQL Server and Agent services were running under the credentials of a Domain Account which had “SA” privileges on that instance.
Since the first option was ruled out, the next step was to check the “Connection” tab in SQL Server Agent –> Properties. Here is what I saw.
Gotcha! For some unknown reasons someone had configured SQL Server Agent to connect to the SQL Server instance using “SA” credentials. I remembered that the SA account was moved into a “Vault” last night. The “Vault” application resets the password for the first time and then it manages the password on its own by changing it every 15 days or when someone fetches the password through it. This is the reasons why SQL Server Agent was failing to start with login failed for sa error. Now all I had to do was to change the connection property to Use Windows Authentication. Thats all. SQL Server Agent started without any errors after I did this change.
There are many more servers that we are “hardening” and I will not be surprised to get few more unpleasant surprises in the coming days.