Getting errors while trying to get SQL Server Agent to work

0

I am trying to get SQL Server Agent on an instance of 2014 and an instance of 2016 to run. Currently both of them start up but then immediately stop with the message: The SQL Server Agent (VERSION16) service on Local Computer started and then stopped. Some services stop automatically if they are not in use by other services or programs.

I read on other issues that this is typically due to a permission issue. I checked the event viewer but all I saw was Event ID 101: SQLServerAgent service successfully started. followed by Event ID 102: SQLServerAgent service successfully stopped.

In order to rule out a permissions issue I went into SQL Server Configuration Manager running as an admin, went to the agent properties and switched Log on to the Built-in account: Local System. When I click OK I am getting the following error: The process terminated unexpectedly. [0x8007042b].

I am unsure where to go from here. I need this agent up as there are several nightly jobs that need to be run on a production database running on this instance. One of these is a nightly backup that is currently never executing. I am logged into the system as a user that is both a local and domain admin so there shouldn't be any permission issue here, please let me know if you can think of anything else I can try to get this working.

Here is the error log:

2017-08-31 15:25:10 - ? [100] Microsoft SQLServerAgent version 13.0.4001.0 (X64 unicode retail build) : Process ID 19680 2017-08-31 15:25:10 - ? [495] The SQL Server Agent startup service account is NE-REVAL\NEREVAL$. 2017-08-31 15:25:11 - ? [393] Waiting for SQL Server to recover database 'msdb'... 2017-08-31 15:25:11 - ! [298] SQLServer Error: 229, The EXECUTE permission was denied on the object 'sp_sqlagent_update_agent_xps', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (DisableAgentXPs) 2017-08-31 15:25:11 - ! [000] The EXECUTE permission was denied on the object 'sp_sqlagent_update_agent_xps', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229) 2017-08-31 15:25:11 - ! [298] SQLServer Error: 229, The EXECUTE permission was denied on the object 'sp_sqlagent_update_agent_xps', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (DisableAgentXPs) 2017-08-31 15:25:11 - ! [000] The EXECUTE permission was denied on the object 'sp_sqlagent_update_agent_xps', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229) 2017-08-31 15:25:11 - ? [098] SQLServerAgent terminated (normally)

sql-server
sql-server-2016
sql-server-agent
asked on Stack Overflow Aug 31, 2017 by dducharme • edited Aug 31, 2017 by dducharme

1 Answer

1

Did it stop working, or is this a new installation?

Try changing the SQL Server Agent to use the same account that SQL Server is using.

If that doesn't work, create a new local user (like "mssqlsvc2014" or "mssqlsvc2016") and use the SQL Server Configuration Manager to have the SQL Server Agent use that user.

answered on Stack Overflow Aug 31, 2017 by wmeitzen

User contributions licensed under CC BY-SA 3.0