UnderSeven
asked on
sqlserver cmdexec job failed access denied
I have a simple job running in sqlserver calling a cmdexec which is failing due to 'access is denied' I have added the account sql service agent is using as a local computer administrator and that didn't help at all. Any ideas on what other things I can try?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm not sure what you mean, that throws a different error, if I for instance put this in the cmd window:
exec xp_cmdsell 'mycmd: d:'
exec xp_cmdsell 'mycmd: d:'
I had a typo in my first comment. It should be
exec xp_cmdshell 'MyCmd D:'
Also it is helpful if you post the error message.
exec xp_cmdshell 'MyCmd D:'
Also it is helpful if you post the error message.
ASKER
The error msg when using your suggestion:
Message
Executed as user: PATH\path-sagent. The process could not be created for step 1 of job 0x111C5BD58A196B468DAE7AC1
The error message when I do it the way I had it set before:
Executed as user: PATH\path-sagent. The process could not be created for step 1 of job 0x111C5BD58A196B468DAE7AC1
Message
Executed as user: PATH\path-sagent. The process could not be created for step 1 of job 0x111C5BD58A196B468DAE7AC1
The error message when I do it the way I had it set before:
Executed as user: PATH\path-sagent. The process could not be created for step 1 of job 0x111C5BD58A196B468DAE7AC1
Is PATH\path-sagent the SQL Server Agent user id? And you made him a local admin?
Which version of SQL Server are you running?
Which version of SQL Server are you running?
ASKER
Sagent is the account, it is an admin (which doesn't seem to have changed anything). The version is server 2003 R2 enterprise x64 service pack 2
>> (which doesn't seem to have changed anything)
Did you restart the SQL Service after making the change?
I meant which version of SQL Server. You can find that out by doing a new query window. Hit <Ctrl>+<T> and then type "select @@version" minus the quotes and hitting <F5>.
Did you restart the SQL Service after making the change?
I meant which version of SQL Server. You can find that out by doing a new query window. Hit <Ctrl>+<T> and then type "select @@version" minus the quotes and hitting <F5>.
ASKER
Microsoft SQL Server 2005 - 9.00.4035.00 (X64)
By restarting the service do you mean in SQL server configuration? I assume you mean stopping and restarting SQL server agent.
I tried that just now, no change.
By restarting the service do you mean in SQL server configuration? I assume you mean stopping and restarting SQL server agent.
I tried that just now, no change.
In order to run xp_cmdshell you need to be a Sysadmin. If SQL Agent account is not a sysadmin in the Server roles, you could have this problem.
ASKER
Sagent is a sys admin.
Right click on the folder where the operation occurs and grant permission on that folder to both the agent and principal account.
It appears to be related to file folder permissions. Correct those and you should be OK
ASKER
So if I change the step to just change drive letters, I gave full access to both drives (the one I assume changed from and the one changed to) full access to sagent. This step stil fails with the same access denied error message. I did the access in right click on the drives, security.
You will also have to restart the Services that have sagent as the logon id. They need to re-authenticate.
<<>This step stil fails with the same access denied error message. I did the access in right click on the drives, security. >
Please validate the following:
> The account doing the folder access
> That permissions set on the drive include both READ and WRITE credentials
Please validate the following:
> The account doing the folder access
> That permissions set on the drive include both READ and WRITE credentials
Also doublecheck that your proxy agent, if any, has sufficient priviledges on the drive/folder
ASKER
I'm getting this error now:
Message
Executed as user: PATH\path-sagent. C:\WINDOWS\system32>d: D:\>cd "D:\Microsoft SQL Server\MSSQL.1\MSSQL\Backu
basically I changed it to go to a batch file instead of attempting to just do the whole task. I tried to keep it as simple as possible. The batch process works if I just run it manually. nothing else was working.
Message
Executed as user: PATH\path-sagent. C:\WINDOWS\system32>d: D:\>cd "D:\Microsoft SQL Server\MSSQL.1\MSSQL\Backu
basically I changed it to go to a batch file instead of attempting to just do the whole task. I tried to keep it as simple as possible. The batch process works if I just run it manually. nothing else was working.
Please make sure the PATH\path-sagent has full control over both Z and D drives....Then retry
HTH
HTH
Also are the D: and Z: local drives or mapped. If it the Z: is a mapped you need to use the UNC "\\MySrvr\MyShare\MyPath".
<<Also are the D: and Z: local drives or mapped. If it the Z: is a mapped you need to use the UNC "\\MySrvr\MyShare\MyPath".
Finally, if the share is a remote then you also need to make sure that the drive on the *destination* remote box also gives read/write access to the PATH\path-sagent account.
HTH
Finally, if the share is a remote then you also need to make sure that the drive on the *destination* remote box also gives read/write access to the PATH\path-sagent account.
HTH
ASKER
So this didn't end up being the approach I was trying in the beginning, but after mucking about with permissions for the better part of a week to get sqlserver to do a simple thing that I could have easily done with a scheduled batch job from the beginning I finally quit and decided this is a badly designed piece of software. Instead I used the spirit of this suggestion to run the batch as a sql job instead of a cmdexec job and it worked. So same outcome, far fewer vague hoops to attempt to jump through. Thank you all for your help.
ASKER
Is this different than the SQL server agent account?