Restarting SQLServerAgent

Question

Restarting SQLServerAgent

Eliyahu

Ten Centuries

Points: 1071
More actions
January 3, 2018 at 7:23 am

#325283

Since I have jobs and stored procedures that need to write to network locations, I run both SQL Server and SQL Server Agent under local Windows accounts. I entered the accounts in the Configuration Manager letting it to set up all the security. The only problem I have is that the SQLServerAgents sometimes terminates unexpectedly and the SQLServerAgent Monitor fails to restart it. This is the error in the Windows Event Log:
SQLServerAgent Monitor failed to restart SQLServerAgent after SQLServerAgent terminated unexpectedly (reason: SQLSCMControl() returned error 5, 'Access is denied.').
I set up the agent recovery in Windows Services, but I still prefer it to be done between the SQL Server and the Agent.
I suspect the SQL Server account is missing some Windows permissions. What permissions shall I check/add?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

rvsc48 SSCertifiable Points: 7687 More actions · Answer 1

I would start here - https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/start-stop-pause-resume-restart-sql-server-services

Sue_H SSC Guru Points: 90860 More actions · Answer 2

Eliyahu - Wednesday, January 3, 2018 7:23 AM
Since I have jobs and stored procedures that need to write to network locations, I run both SQL Server and SQL Server Agent under local Windows accounts. I entered the accounts in the Configuration Manager letting it to set up all the security. The only problem I have is that the SQLServerAgents sometimes terminates unexpectedly and the SQLServerAgent Monitor fails to restart it. This is the error in the Windows Event Log:
SQLServerAgent Monitor failed to restart SQLServerAgent after SQLServerAgent terminated unexpectedly (reason: SQLSCMControl() returned error 5, 'Access is denied.').
I set up the agent recovery in Windows Services, but I still prefer it to be done between the SQL Server and the Agent.
I suspect the SQL Server account is missing some Windows permissions. What permissions shall I check/add?

The permissions needed by the service accounts are listed in the following document:
Configure Windows Service Accounts and Permissions

Sue

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 3

Eliyahu - Wednesday, January 3, 2018 7:23 AM
Since I have jobs and stored procedures that need to write to network locations, I run both SQL Server and SQL Server Agent under local Windows accounts. I entered the accounts in the Configuration Manager letting it to set up all the security. The only problem I have is that the SQLServerAgents sometimes terminates unexpectedly and the SQLServerAgent Monitor fails to restart it. This is the error in the Windows Event Log:
SQLServerAgent Monitor failed to restart SQLServerAgent after SQLServerAgent terminated unexpectedly (reason: SQLSCMControl() returned error 5, 'Access is denied.').
I set up the agent recovery in Windows Services, but I still prefer it to be done between the SQL Server and the Agent.
I suspect the SQL Server account is missing some Windows permissions. What permissions shall I check/add?

To be honest, you're working on the wrong problem. You need to find out what and who is causing the "Access Denied" errors because it could be illegal attempts to access data. The SQLAgent is trying to protect you and you're trying to override that protection.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eliyahu Ten Centuries Points: 1071 More actions · Answer 4

Jeff Moden - Wednesday, January 3, 2018 8:12 AM
Eliyahu - Wednesday, January 3, 2018 7:23 AM
Since I have jobs and stored procedures that need to write to network locations, I run both SQL Server and SQL Server Agent under local Windows accounts. I entered the accounts in the Configuration Manager letting it to set up all the security. The only problem I have is that the SQLServerAgents sometimes terminates unexpectedly and the SQLServerAgent Monitor fails to restart it. This is the error in the Windows Event Log:
SQLServerAgent Monitor failed to restart SQLServerAgent after SQLServerAgent terminated unexpectedly (reason: SQLSCMControl() returned error 5, 'Access is denied.').
I set up the agent recovery in Windows Services, but I still prefer it to be done between the SQL Server and the Agent.
I suspect the SQL Server account is missing some Windows permissions. What permissions shall I check/add?
To be honest, you're working on the wrong problem. You need to find out what and who is causing the "Access Denied" errors because it could be illegal attempts to access data. The SQLAgent is trying to protect you and you're trying to override that protection.

I understood that the "Access is denied" is the reason why the SQLServerAgent Monitor failed to restart the SQLServerAgent. It doesn't come from the agent. The agent is lying dead at that time.