It works when I run it this way but not when I run it through xp_cmdshell!

It’s a permissions issue. When you run xp_cmdshell you are running under the ??? account.

Little bit more detail. The extended stored procedure xp_cmdshell creates a windows command shell. This shell has to be run under a windows/active directory account. Obviously you can’t get access to a windows resource (a directory for example) using a SQL Server login. The trick is to be able to tell them what account xp_cmdshell is using within that shell. There are two possibilities here.

I’m running xp_cmdshell as a sysadmin.

If you are running xp_cmdshell as a sysadmin then it’s running as the service account that is running SQL Server. The easiest way to tell what that is, is to look at the output for sys.dm_server_services.

SELECT * FROM sys.dm_server_services;

Look at the service_account column on the row for the SQL Server entry.

I’m running xp_cmdshell as anything other than sysadmin.

I’m making the assumption that xp_cmdshell is set up correctly and is working otherwise. In this case you are looking at the proxy account set up for xp_cmdshell. The easiest way to find it is to look at sys.credentials.

SELECT * FROM sys.credentials WHERE name = '##xp_cmdshell_proxy_account##';

This time you want to look in the credential_identity column. If there isn’t an entry at all then the proxy for xp_cmdshell isn’t set up and it shouldn’t be working for a non-sysadmin.

Other

All of that said, if you are the one running xp_cmdshell the easiest way to see what account your shell is running under is to use whoami.

EXEC xp_cmdshell 'whoami';

And just look at the output column.