June 19, 2006 at 3:32 pm
I have a job that is trying to create a text file on a different server using xp_CmdShell. I am getting an Access Is Denied error when doing this. I tried to do a DIR on the same folder with xp_CmdShell and it says The system cannot find the file specified. The Owner of the job is sa and the account that SQL Agent runs under does have access to the folder. Another bit of info is I can successfully run xp_GetFileDetails, xp_FileExists and xp_DirTree on the folder. Any ideas of why xp_CmdShell wouldn't be able to access this folder when it seems like everything else can?
June 19, 2006 at 3:37 pm
You need to be aware of what security context xp_cmdshell is actually executing under:
"When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. "
June 21, 2006 at 9:58 am
I'm having a similar issue, if not the same. I want to run a process from QA to zip and move backup files from the server each night but I get "access denied" when I run directory commands with xp_cmdshell.
I found two ways to fix the issue:
If you use a network path (ie \\sharename\directory) in the command then you may need to modify your startup service account (security tab on server properties) to use "This Account" as opposed to the default System Account. Then put in your domain/username and password that have rights to the directory you are attempting to work with. I was able to get this to work on my local server, however the production server has this option greyed out so it's not a solution for me at this point.
The other solution appears to be the better one. Simply construct the XP_cmdshell commands with the directory path as if you were working on the server (ie d:\directory). In other words, the directory path your sql server uses when creating a backup to itself.
Here's a before and after:
Before:
exec master..xp_cmdshell 'dir \\srv\bkup\ /b'
results in "access denied" even thou I can run a directory from a dos command prompt
After:
exec master..xp_cmdshell 'dir d:\bkup\ /b'
works great
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply