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.