Fixing SQL Agent Startup
Recently I had reason to deploy a new SQL Server instance for a quick POC. As many in the scenario would, I initially chose to install SQL Express, rather than messing with license requirements and all that. As sometimes happens, after everything was already set up and configured, I discovered a need to use the SQL Agent service so I could create a job that would execute from a trigger. The key problem? The SQL Agent Service wouldn’t start up…or, more specifically, it would start and then stop again. Naturally there were also precisely zero helpful errors in either the Event Logs or the SQL logs, so off to the internet I went. I found lots of suggestions on how to solve things, but most involved changing the service to run as Local System and messing with DB logins and access roles. Most such suggestions were also recommending providing full sysadmin rights which, as a security oriented guy, wasn’t something I wanted to do on a whim. In the end, I didn’t find a single source that provided the correct answer, so I thought I’d put the solution out there, in hopes it will help others in the future (or me, if I forget).
The first issue that I ran into is that, unfortunately, the SQL Server Agent service is not available for either SQL Express or SQL Express with Advanced Services, as outlined in the MS Docs here. Fortunately SQL supports a seamless upgrade of versions and, since I still wanted to avoid license constraints for a POC, I went with Developer edition, which has all the same capabilities as Enterprise as long as you aren’t using it for production purposes. The upgrade process itself is really quite simple:
- Download the ISO for SQL Developer Edition and mount it in your server
- Start up the installer and go to Maintenance
- Select Edition Upgrade and follow the steps from there
When upgrading to Standard or Enterprise, you’ll likely need to provide a license key, depending on the media used, but Developer doesn’t require any key entry, and the edition change takes almost no time at all.
Now that our SQL Edition is upgraded to one that supports SQL Server Agent, we should theoretically have been good to go, but the behavior was unchanged. More work on Google wasn’t producing anything usable either, as most suggestions involved running SQL commands and changing other elements that actually don’t need changing. The fix, it turns out, is super simple.
Beginning with Server 2008 R2, Windows (and SQL) have supported the use of ‘virtual’ service accounts. These accounts are automatically managed by the OS and can seamlessly leverage the system context for accessing the network, though they should only be used in stand-alone DB configurations. When you install SQL Express, since the SQL Agent isn’t supported, it’s configured to use ‘Network Service’, which doesn’t actually have the required permissions within SQL itself. When you convert the editions, you’d think that this would be changed to the correct value, but this doesn’t happen, so manual intervention is required.
- First, open up Services.msc and find the SQL Agent service
- Set the Startup type to Automatic
- Change the user to ‘NT Service\SQLSERVERAGENT’
- Note: If you are using a named instance, that has to be appended to the above name like so ‘NT Service\SQLAGENT$<instance_name>’
- Leave the password blank and click OK
After this change, the service should be able to start up without further changes. If, for some reason, the service still fails to stay running, open up the SQL Server Management Studio and execute the following TSQL:
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Agent XPs', 1
GO
RECONFIGURE
GOI did these steps in the reverse order, having tried the above SQL first and, when that didn’t work, I looked up the default account and tried that. It’s possible that simply setting the correct account was all that was required to solve my challenge.
