Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Stop/Start SQL Server Agent Expand / Collapse
Author
Message
Posted Tuesday, December 31, 2013 9:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 21, 2014 2:32 PM
Points: 293, Visits: 500
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.
Post #1526778
Posted Tuesday, December 31, 2013 9:35 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:22 PM
Points: 36,016, Visits: 30,308
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1526786
Posted Tuesday, December 31, 2013 10:45 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, April 13, 2014 12:44 PM
Points: 516, Visits: 300
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.
Post #1526811
Posted Tuesday, December 31, 2013 4:42 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:22 PM
Points: 36,016, Visits: 30,308
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1526861
Posted Tuesday, December 31, 2013 7:50 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, April 13, 2014 12:44 PM
Points: 516, Visits: 300
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
Post #1526873
Posted Tuesday, December 31, 2013 10:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:22 PM
Points: 36,016, Visits: 30,308
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1526879
Posted Thursday, January 02, 2014 8:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 21, 2014 2:32 PM
Points: 293, Visits: 500
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).
Post #1527131
Posted Thursday, January 02, 2014 8:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 21, 2014 2:32 PM
Points: 293, Visits: 500
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!
Post #1527133
Posted Thursday, January 02, 2014 9:10 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, April 21, 2014 9:15 AM
Points: 604, Visits: 481
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.


.
Post #1527176
Posted Friday, January 03, 2014 6:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 2:04 AM
Points: 48, Visits: 130
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...
Post #1527501
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse