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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Jim P.Commented:
try it as:
exec xp_cmdsell 'mycmd:
and see what happens.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Server 2003

From novice to tech pro — start learning today.