Overflow Community,
I have an issue I have been butting my head against for hours upon hours now. I have scoured Google for solutions and tried (what feels like) them all.
In an attempt to bring our TEST environment more in line with our PROD environment, I recently updated the SQL Server Agent 'Log On As' to use a Managed Service Account and can now no longer start the SQL Server Agent Service.
If I try to start the service, I get a "Failed to initialize SQL Agent log. Access is denied". If I try to change the 'Log On As' back to ANY other account, I get a "WMI Provider Error. The process terminated unexpectedly [0.8007042b]".
E.g:
I literally cannot get this service to start now.
I can confirm that the Managed Service Account has all permissions (except Full Control) to all folders the SQL Server Agent log sits in. I have confirmed that both the registry and the service properties contain the correct SQL Server Agent log directory.
Looking through the Event Viewer shows exactly as explained above under the Windows Logs > Application folder: "Failed to initialize SQL Agent log. Access is denied".
Is anyone able to offer any guidance on this, please? Please let me know if there is further info required and I shall provide it.
Unfortunately I had to follow through with my comment above. The only way I could get out of this awful loop was to do a reinstall of SQL. For me this included doing the following:
Backup the databases > uninstall all SQL programs and components > delete all SQL files and folders on all drives > reinstall SQL (ensuring the correct accounts were allocated to the services!) > restore all DB's from backup > check services are running correctly.
It's worth reminding that this fortunately this was in a TEST environment so I had some room to play around. Despite this, this was the only way I found around the problem. I also want to clarify that I came into this task with no prior history in setting up these environments.