Cannot "net start" with xp_cmdshell

Question

Cannot "net start" with xp_cmdshell

VD

Ten Centuries

Points: 1149
More actions
January 11, 2007 at 9:47 am

#171776

I have SQL2005 sp1. Here is my problem. I need to start SQLServerAgent with net start. When I do from cmd prompt it works fine. When I run this (master..xp_cmdshell 'net start SQLServerAgent') from SSMS it gives me "access denied" error. The account is a domain user and local admin. Does anyone have any idea what I am missing?

thank you, Veronika

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

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

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

Unless you specify the xp_cmdshell Proxy Account XP_cmdshell will execute under the rights of the user that the SQL Server runs as. If that account doesn't have rights to start the the SQL Agent Service it will fail.

when you are running the Net Start on the cmd line it should be executing with your credentials.

http://msdn2.microsoft.com/en-us/library/ms175046.aspx

To help us help you read this[/url]For better help with performance problems please read this[/url]

VD Ten Centuries Points: 1149 More actions · Answer 2

The account SQLServer is running under has local admin rights on the server and I was logged in as that account. So the xp_cmdshell and cmd line had the same credentials. and that's what is really confusing.

I will check out the proxy account as well. thank you for your reply.

MohammedU SSCoach Points: 19644 More actions · Answer 3

Try exec xp_servicecontrol 'start' 'sqlserveragent'

MohammedU
Microsoft SQL Server MVP

VD Ten Centuries Points: 1149 More actions · Answer 4

that is what I had originally. It gives me the same "Access Denied". I will try again tomorrow with a "fresh head". I am probably missing something really stupid.

MohammedU SSCoach Points: 19644 More actions · Answer 5

I tested it on my machine... it is working fine.. output is as follows...

Service Started.

Msg 22003, Level 1, State 0

(0 row(s) affected)

Under what login SQLagent is configured?

MohammedU
Microsoft SQL Server MVP

VD Ten Centuries Points: 1149 More actions · Answer 6

it is a domain user. Since both SQL server and SQL server agent run under the same account this user is also a memmber of a bunch of SQLServer groups created by the installation. It is a part of the local admin group as well right now.

Ian-367027 SSC Journeyman Points: 99 More actions · Answer 7

Hi,

Have you enable xp_cmdshell in the SQL Server Surface Configuration Manager.

Regards,

Ian.

Moreno-84941 Hall of Fame Points: 3258 More actions · Answer 8

If xp_cmdshell is not enabled error:

"Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1

SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online"

should be issued. After you made the domain user member of the local administrators group have you restarted the sql service ? I expirienced one a similar problem and I found out that a restart of the service is needed if permissions of users are changed.

Regards,

Moreno

Luke L SSC-Dedicated Points: 35547 More actions · Answer 9

RE:Moreno

That's absolutely correct, when you change permissions for a user running a service, it's the same as changing rights for your own account. You need to log off and back on to get a new access token with your new rights (i.e. added to a group membership or some such change). Stopping and starting the service basically does the log on/log off bit to get that user the new access token.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

VD Ten Centuries Points: 1149 More actions · Answer 10

that was it! Restarting the SQL Server service solved all my problems. Thank you all for your help!