Stop/Start SQL Server Agent

Question

Stop/Start SQL Server Agent

dwilliscp

SSCrazy Eights

Points: 9270
More actions
December 31, 2013 at 9:09 am

#280848

Is there anyway to stop and start the agent, if you are not a local/network admin? As a DBA IT will not give me local or network admin permissions.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

What privs do you have on SQL Server itself? Do you have "SA" privs? If not, you're pretty much stuck. If you do, then it may be that you have to start SSMS a little differently. I keep an icon on my desktop to start SSMS. If hold down the {Shift} key and right click on the icon, you should see a selection of "Run as Administrator". Click on that and see if you now have control over the agent from SSMS.

--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)

SQLBlimp SSCertifiable Points: 6340 More actions · Answer 2

dwilliscp (12/31/2013)
Is there anyway to stop and start the agent, if you are not a local/network admin? As a DBA IT will not give me local or network admin permissions.

Does the account under which SQL Server runs have machine admin permissions? If so, then you could try enabling and using xp_cmdshell to run NET START for the Agent service (please disable xp_cmdshell as soon as you do this; it is a huge security hole). However, you must have sa permissions on the SQL server to do so.

However, the above is a lousy solution. There is no reasonable grounds for not allowing DBA to be machine admin on any SQL Server instance. In fact, I'd argue that such rights are vital to the job of the production DBA.

Thanks

John.

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

JohnFTamburo (12/31/2013)
(please disable xp_cmdshell as soon as you do this; it is a huge security hole)

No it's not. Poor ol' xp_CmdShell gets blamed for other very poor security practices a lot. It's not actually a security risk. Bad security is a security risk.

Stop and think about it. Who can use xp_CmdShell? If you've done it right, only people who have "SA" privs can use it. Who can turn it on or off? Right again... only those folks that have "SA" or "Control Server" privs.

If an attacker breaks in as a non-SA prived user, can he use xp_CmdShell if even if it's enabled? No. If an attacker breaks in as an SA prived user, can he use xp_CmdShell even if it's disabled? Since he also has the privs to enable it, the answer is "Yes". Even if xp_CmdShell could be permanently disabled, an unauthorized person getting into your system can still get to other systems using the command block of a job, PowerShell, and SQLCmd. The REAL security risk isn't having xp_CmdShell enabled... the real security risk is allowing unauthorized people in as "SA".

What about DBAs using it to elevate their privs as you have just done? It's not the usage of xp_CmdShell that caused that risk. It's the fact that you think DBAs have to have "SA" privs to do their job. 😉

"DBA" is an abbreviation for "Database Administrator"... not "System Administrator". If your DBAs have SA privs and you don't actually want them to be a "security risk", then give them the correct privs for the job instead of blaming xp_CmdShell.

Stop blaming xp_CmdShell for otherwise poor security. Instead, fix the security holes that allow for its misuse.

--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)

SQLBlimp SSCertifiable Points: 6340 More actions · Answer 4

Jeff Moden (12/31/2013)
What about DBAs using it to elevate their privs as you have just done? It's not the usage of xp_CmdShell that caused that risk. It's the fact that you think DBAs have to have "SA" privs to do their job. 😉
"DBA" is an abbreviation for "Database Administrator"... not "System Administrator". If your DBAs have SA privs and you don't actually want them to be a "security risk", then give them the correct privs for the job instead of blaming xp_CmdShell.
Stop blaming xp_CmdShell for otherwise poor security. Instead, fix the security holes that allow for its misuse.

Sorry, I was not intending to blame xp_cmdshell for anything.

With respect to the proper level of privileges to grant to a production DBA, I agree to disagree. A DBA should have the rights necessary to do her or his job. Generally that requires SA level privileges for the production level DBA, not for the world. The question then becomes: do you trust your DBA? In the case of the original poster of this thread, the poor guy cannot even start the SQL Server agent without jumping through hoops. This seems to me to be excessive control for its own sake, not prudent access management.

I've been in environments where connection strings explicitly log an application in as sa. Luckily, where I am now, that is no longer the case. A DBA's job sometime requires that person to stop and start services, etc. Machine admin is not unreasonable.

Thanks, no worries.

John

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 5

JohnFTamburo (12/31/2013)
Jeff Moden (12/31/2013)
What about DBAs using it to elevate their privs as you have just done? It's not the usage of xp_CmdShell that caused that risk. It's the fact that you think DBAs have to have "SA" privs to do their job. 😉
"DBA" is an abbreviation for "Database Administrator"... not "System Administrator". If your DBAs have SA privs and you don't actually want them to be a "security risk", then give them the correct privs for the job instead of blaming xp_CmdShell.
Stop blaming xp_CmdShell for otherwise poor security. Instead, fix the security holes that allow for its misuse.
Sorry, I was not intending to blame xp_cmdshell for anything.
With respect to the proper level of privileges to grant to a production DBA, I agree to disagree. A DBA should have the rights necessary to do her or his job. Generally that requires SA level privileges for the production level DBA, not for the world. The question then becomes: do you trust your DBA? In the case of the original poster of this thread, the poor guy cannot even start the SQL Server agent without jumping through hoops. This seems to me to be excessive control for its own sake, not prudent access management.
I've been in environments where connection strings explicitly log an application in as sa. Luckily, where I am now, that is no longer the case. A DBA's job sometime requires that person to stop and start services, etc. Machine admin is not unreasonable.
Thanks, no worries.
John

You'll get no argument from me that DBAs should have "SA" privs... especially since it allows them to do useful things with xp_CmdShell. 😉 Nor will you get an argument from me that you should hire someone that you can trust with the proverbial keys to the city.

I just hate to see something like the incredibly useful xp_CmdShell take it on the chin as a "security risk" when it's usually something else that's the real security risk.

--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)

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 6

JohnFTamburo (12/31/2013)
dwilliscp (12/31/2013)
Is there anyway to stop and start the agent, if you are not a local/network admin? As a DBA IT will not give me local or network admin permissions.
Does the account under which SQL Server runs have machine admin permissions? If so, then you could try enabling and using xp_cmdshell to run NET START for the Agent service (please disable xp_cmdshell as soon as you do this; it is a huge security hole). However, you must have sa permissions on the SQL server to do so.
However, the above is a lousy solution. There is no reasonable grounds for not allowing DBA to be machine admin on any SQL Server instance. In fact, I'd argue that such rights are vital to the job of the production DBA.
Thanks
John.

No John, I do not have admin (local or Domain).

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 7

Jeff Moden (12/31/2013)
What privs do you have on SQL Server itself? Do you have "SA" privs? If not, you're pretty much stuck. If you do, then it may be that you have to start SSMS a little differently. I keep an icon on my desktop to start SSMS. If hold down the {Shift} key and right click on the icon, you should see a selection of "Run as Administrator". Click on that and see if you now have control over the agent from SSMS.

Thanks Jeff, I do have SQL Server SA rights, and this Does work for me!

Bill (DBAOnTheGo) Hall of Fame Points: 3841 More actions · Answer 8

I have to agree with Jeff. If you do not have permissions to restart the agent, have your SA do it. If you need those permissions, put in the request and case for it. Finding a way around a security lock down is exactly why I feel DBA's aren't as trusted as they should be. If there's a declared split in responsibilities, don't work around it. If they get tired of you calling every day for menial tasks that you could do with a bit more permissions, they'll fix your permissions. If not, when that server is down at 2 am and you can't fix it, you can always site back to where you asked for permissions and it was denied.

I wouldn't risk my job over stepping when you can fix the greater issue or just have one less thing to worry about.

As A side note, I've had Sys Admin level access on all but 3 servers I've worked on. It definitely makes the job easier... but that's all. Just easier. The work still gets done and everything still gets fixed, it just requires an additional person on the call.

.

prabhu.st SSCrazy Points: 2552 More actions · Answer 9

Hi Jeff,

you have to start SSMS a little differently. I keep an icon on my desktop to start SSMS. If hold down the {Shift} key and right click on the icon, you should see a selection of "Run as Administrator". Click on that and see if you now have control over the agent from SSMS.

Thanks for the Nice Tips, so far I don't know this, its my bad I think not to know this these many years...

EdVassie SSC Guru Points: 60445 More actions · Answer 10

You can get your Windows people to grant the Service Start/Stop right for the SQL Agent service to the DBA Windows group. You can then use any of the standard Windows methods to start and stop the SQL Agent service.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara