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?
UnderSevenAsked:
Who is Participating?
 
Jim P.Connect With a Mentor Commented:
try it as:
exec xp_cmdsell 'mycmd:
and see what happens.
0
 
UnderSevenAuthor Commented:
I noticed that the run as is set as the : SQL agent service account

Is this different than the SQL server agent account?
0
 
UnderSevenAuthor Commented:
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:'
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Jim P.Commented:
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.
0
 
UnderSevenAuthor Commented:
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 0x111C5BD58A196B468DAE7AC11822177D (reason: The system cannot find the file specified).  The step failed.

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 0x111C5BD58A196B468DAE7AC11822177D (reason: Access is denied).  The step failed.
0
 
Jim P.Commented:
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?
0
 
UnderSevenAuthor Commented:
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
0
 
Jim P.Commented:
>> (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>.
0
 
UnderSevenAuthor Commented:
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.

0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
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.
0
 
UnderSevenAuthor Commented:
Sagent is a sys admin.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Right click on the folder where the operation occurs and grant permission on that folder to both the agent and principal account.  
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
It appears to be related to file folder permissions. Correct those and you should be OK
0
 
UnderSevenAuthor Commented:
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.  
0
 
Jim P.Commented:
You will also have to restart the Services that have sagent as the logon id. They need to re-authenticate.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<>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
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Also doublecheck that your proxy agent, if any, has sufficient priviledges on the drive/folder
0
 
UnderSevenAuthor Commented:
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\Backup\All_TAT\"     D:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ALL_TAT>xcopy *.* z:\ /V /Y  1>>tat_bak.log'   Invalid drive specification.  Process Exit Code 4.  The step failed.

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.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Please make sure the PATH\path-sagent has full control over both Z and D drives....Then retry

HTH
0
 
Jim P.Commented:
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".
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<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
0
 
UnderSevenAuthor Commented:
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.
0
All Courses

From novice to tech pro — start learning today.