Monday night my team called me up to inform that were having some issues while trying to start SQL Services on an instance. The services did not start automatically after the server was rebooted. When they tried starting the services manually, the following error message was thrown.
The error message is usually associated with a wrong password for the Startup Account. But as far as I knew no Change Controls were implemented on this instance and the password of this account was never changed. Quickly looked up in Active Directory Users and Computers to check if this account was locked out. No, it was not locked out.
Since it is not new to hear “someone” or “something changed” stories on the servers, I decided to re-enter the password in the SQL Server service properties. After punching in the password, the following message popped up.
Strange! The same account was used to start the SQL Server service for a very long time, now it got the Log On As a Service right? Something must be wrong. On this server SQL Server and SQL Server Agent have different startup accounts. Before re-entering the password for the SQL Server Agent service, I decided to check the settings of Log on as a Service in Local Security Settings on the server.
I could see the SQL Server service’s startup account listed here (since I punched in the password again) but the one for SQL Server Agent service was missing! This made it clear why the services were failing with logon failure error. The Service Accounts did not have Log on as a Service rights! For any service to start, it should have this right. How did this got changed? A quick look in the Change Management application revealed that a series of Windows Security hot fixes were applied some time back. One of these hot fixes would have wiped out the permissions for the SQL Server startup accounts.
Since the root cause has been figured out, I happily entered the password for the SQL Server Agent service. Same informational message that it has been granted the rights. The services started and Pradeep closed the lid of the laptop to catch some sleep.