May 13, 2008 at 1:18 pm
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
[font="Courier New"][SC] OpenService FAILED 5:
Access is denied.[/font]
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:
[font="Courier New"]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[/font]
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 13, 2008 at 1:56 pm
xp_cmdshell on some servers is set up to run under a different account than the SQL service. Have you checked that?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 13, 2008 at 1:59 pm
John Carlson (5/13/2008)
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
[font="Courier New"][SC] OpenService FAILED 5:
Access is denied.[/font]
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:
[font="Courier New"]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[/font]
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.)
- Who is job owner ?
- make "sa" job owner so it uses the sqlagent service account to execute.
- better would even be to create a proxy account which is granted the needed auth to perform the statement at the remote location and assign that proxy account to that job step.
Use a "operating system (cmdshell)" job step to do what you want, this way you don't have to enable xp_cmdshell.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 13, 2008 at 2:30 pm
GSquared (5/13/2008)
xp_cmdshell on some servers is set up to run under a different account than the SQL service. Have you checked that?
I was not aware of that. Any idea which service xp_cmdshell is linked to?
Thanks
May 13, 2008 at 2:34 pm
ALZDBA
- Who is job owner ?
- make "sa" job owner so it uses the sqlagent service account to execute.
- better would even be to create a proxy account which is granted the needed auth to perform the statement at the remote location and assign that proxy account to that job step.
Use a "operating system (cmdshell)" job step to do what you want, this way you don't have to enable xp_cmdshell.
The job owner is 'sa'.
Using the "operating system (cmdshell)" job step gives me the exact same error as xp_cmdshell.
I will look into the proxy account and see if that works.
Thanks.
May 13, 2008 at 2:57 pm
More information:
When running the command
exec xp_cmdshell 'whoami'
it gives me the domain\username of the sql service account that I was originally expecting.
So, I guess I'm back to my original question as to why when using xp_cmdshell or an Operating system (CmdExec) job step, I get the error?
Thanks
May 13, 2008 at 3:52 pm
Because the SQL Server service account does not have rights to stop services on that machine.
What rights does the SQL Server service account have on the machine where the service to be stopped is?
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
May 13, 2008 at 8:22 pm
Scott Duncan (5/13/2008)
Because the SQL Server service account does not have rights to stop services on that machine.What rights does the SQL Server service account have on the machine where the service to be stopped is?
Yes, the SQL Server service account does have rights to start and stop services on the application server (the machine where the service to be stopped is). It is in the Power Users group on the application server machine (which is all that is needed to start and stop the service, but we tried putting in the administrators group also, and that didn't help either).
As a matter of fact, when I log in to the SQL Server machine with the SQL server service account login, I can start and stop the service on the application server through the Command Prompt window.
I just can't do it using the CMD Exec through a job, nor with the XP_cmdshell.
May 14, 2008 at 6:37 pm
How about the SQL Server Agent account? What rights does that have?
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
May 15, 2008 at 8:51 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.
May 15, 2008 at 3:55 pm
That's probably due to the account SQL Server is using having been logged in before the additional rights were granted. Same as when a user is logged in and you grant them rights to a folder - they have to log out and back in again so their security token is updated with the additional rights.
Hence why MS recommend AGLP practice - add Account to Global group, add Global group to Local group, grant Permissions to local group. That way, you can add an account to the global group & user/account has rights straight away, no logoff required.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
May 15, 2008 at 9:02 pm
Scott Duncan (5/15/2008)
That's probably due to the account SQL Server is using having been logged in before the additional rights were granted. Same as when a user is logged in and you grant them rights to a folder - they have to log out and back in again so their security token is updated with the additional rights.Hence why MS recommend AGLP practice - add Account to Global group, add Global group to Local group, grant Permissions to local group. That way, you can add an account to the global group & user/account has rights straight away, no logoff required.
Regarding AGLP. I will talk with our Security Admin to double-check. I believe the account was added to the global group, and then the global group was added to the local group (Power Users). However, the last step to grant Permissions to local group would not apply, as the local group is "Power Users", which inherently has permissions to start and stop services.
Since it does inherently have the permissions, I guess that would be a problem in the AGLP recommendation since the "chain" wasn't followed by virtue of the permissions already being there?
May 18, 2008 at 2:41 pm
If the global group was not already in the local group when the account was added to the global group and restart wasn't done after adding to the global group, that would explain the required restart, for the same reasons as for just adding the account to the local group. I think.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply