Sql-server – “Access is denied.” for xp_cmdshell for network share

sql serversql server 2014xp-cmdshell

On a SQL 2014 SP 1 instance a SQL Agent job I'm using xp_cmdshell to return a list of files from a network share. This has recently stopped working and returns "Access is denied."

The command is EXEC master..xp_cmdshell 'DIR "\\backups\marshall\master" /A:-D /S /B'

The command will work for a local folder.EXEC master..xp_cmdshell 'DIR "C:\Temp" /A:-D /S /B'

Running EXEC master..xp_cmdshell 'whoami' returns the Windows account that is used for the SQL Engine and SQL Agent. This account has Full Control for the backup folder. I've deleted and re-added permissions for this account. I can manually add and delete files from this folder.

I will be changing the process to no longer use xp_cmdshell, but can't do that today. Any ideas on what is going wrong?

Best Answer

Create a proxy account to run xp_cmdshell and give the proxy access to your share.

Link

Related Solutions

SQL Server xp_cmdshell – Access Denied When Deleting Files with xp_cmdshell

You need to have a connection as well as permissions. SQL Server won't automatically connect to a network resource. You can do this either inside or outside of SQL Server but the connection has to be made on the machine. If there is a permission issue, then the 'net use' in the SQL query will return more info than just 'Access Denied'.

exec xp_cmdshell 'net use \\morrison\backups\angelia\AS_ReportingServicesDW_MOLAP mypassword /USER:ntdomain\angeliasql'

exec xp_cmdshell 'del \\morrison\backups\angelia\AS_ReportingServicesDW_MOLAP\ReportingServicesDW_MOLAP-20151116.abf'

Sql-server – CMDEXEC The process could not be created for step… (reason: Access is denied). The step failed

ok, so instead of using a CMDEXEC job step, I changed it back to a t-SQL script step replaced the batch file name with the full string:

EXEC xp_cmdshell 'c:\dircommand.bat'

and it works. So I am not sure what the problem is with Operating System job steps but at this point I am passed the hurdle. funny thing is, I have another server setup the other way and it works fine. Thanks for the offers of assistance.

Best Answer

Related Solutions

SQL Server xp_cmdshell – Access Denied When Deleting Files with xp_cmdshell

Sql-server – CMDEXEC The process could not be created for step… (reason: Access is denied). The step failed

Related Question