3

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?

  • 1
    Have you confirmed both the file-system security and the share-level security allow access to the SQL Server service account? – Max Vernon Jun 9 '16 at 18:02
  • any additional security software added, better filtering, or a layer 7 firewall in between per chance? – Ali Razeghi Jun 9 '16 at 18:02
  • No additional security or firewall in place. The SQL Server service account can write/delete backup (.bak and .trn) files via maintenance plan. – tpet Jun 9 '16 at 18:06
  • this likely sounds stupid, but have you tried running SSMS "As Administrator" – Max Vernon Jun 9 '16 at 19:42
  • Yes, I've ran SSMS as the SQL Server service account, as Administrator and the combo of those two. Same results. – tpet Jun 9 '16 at 20:25
-1

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

Link

  • I think this answer could do with some more detail. Creating a proxy account could open a large security hole depending on the Windows account chosen. – James Anderson Mar 21 '17 at 16:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.