May 14, 2008 at 2:23 pm
I originally posted this yesterday morning on the General Discussion, and thought it might be more appropriate in this forum.
I am attempting to use xp_cmdshell to start and stop a remote application server in a job.
When executing the following:
exec xp_cmdshell 'sc \\MYSERVER stop MYSERVICE'
I get the following error
[SC] OpenService FAILED 5:
Access is denied.
When executing just the command to query the service:
exec xp_cmdshell 'sc \\MYSERVER query MYSERVICE'
the query works fine, and it gives me the current state of the service:
SERVICE_NAME: MYSERVICE
TYPE : 10 WIN32_OWN_PROCESS
STATE : 4 RUNNING
(STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN)
WIN32_EXIT_CODE : 0 (0x0)
SERVICE_EXIT_CODE : 0 (0x0)
CHECKPOINT : 0x0
WAIT_HINT : 0x0
Just to add more relevant information, the account that the SQLServer & Services runs on is a power user on the application server, so it does have authority to start and stop services. As a matter of fact, if I open up a Command Prompt on my SQL Server, I can manually issue the SC commands I show above, and can START and STOP the services just fine.
Does anyone have any idea why I would get the access denied error because of running from xp_cmdshell?
(Also, just to clarify, I am logged in to SSMS with Windows Authentication using the account that starts the SQL Server and Agent services.)
May 14, 2008 at 2:59 pm
the usual cause would be that the sql accounts do not have sufficient permissions to do what you're asking.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 14, 2008 at 8:55 pm
As a matter of fact, if I open up a Command Prompt on my SQL Server, I can manually issue the SC commands I show above, and can START and STOP the services just fine.
Does anyone have any idea why I would get the access denied error because of running from xp_cmdshell?
(Also, just to clarify, I am logged in to SSMS with Windows Authentication using the account that starts the SQL Server and Agent services.)
I assume you logged into the SQL Server as yourself (or other domain account), but not with the SQL Server Service account? That makes sense if you were able to start/stop the service from the command prompt. It definitely sounds like your SQL Server Service account does not have the permissions to start/stop the service.
May 15, 2008 at 6:22 am
colin Leversuch-Roberts (5/14/2008)
the usual cause would be that the sql accounts do not have sufficient permissions to do what you're asking.
Yes, the sql account (PSSQL) has the sufficient permissions to start and stop the service. It is in the PowerUsers Group on the machine where the service is that we want to start and stop. I can log in to the SQL Server machine using this PSSQL account, and then open a Command prompt, and issue the command to remotely start and stop the service, and it works fine.
Jared Hunt (5/14/2008)
I assume you logged into the SQL Server as yourself (or other domain account), but not with the SQL Server Service account? That makes sense if you were able to start/stop the service from the command prompt. It definitely sounds like your SQL Server Service account does not have the permissions to start/stop the service.
No, I logged into the SQL Server with the SQL Server Service account ID. It is also the SQL Server Agent Service account ID. This ID (PSSQL) does have the permissions to start/stop the service on the other machine, but it just doesn't happen through the SQL Command shell.
May 15, 2008 at 8:50 am
Solution found!
Stopping and restarting SQL Server has fixed the problem. I'm not sure why this was necessary, but it now works. (Are there security attributes that stay active for the SQL Server Logon ID for the duration of the uptime?)
Thanks to everyone for their time.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply