using xp_cmdshell and DOS "DIR" cmd to get info from another server
I have written a stored procedure to pull info about files from another server that is not a db server. In the proc is this code:
SET @vchCMD = 'dir \\server_name\folder_name\
INSERT INTO #DirList
exec master..xp_cmdshell @vchCMD;
When I run this code on the db server itself it works. When I use the path to a folder on another server I get the error "access denied". Anyone know why? What to do to resolve?
SET @vchCMD = 'dir \\server_name\folder_name\
INSERT INTO #DirList
exec master..xp_cmdshell @vchCMD;
When I run this code on the db server itself it works. When I use the path to a folder on another server I get the error "access denied". Anyone know why? What to do to resolve?
ASKER
When I manually run this from a query window
The SELECT above returns:
Access is denied.
NULL
declare @CMD varchar(1000);
create table #TempTable(FileInfo varchar(1000));
set @CMD = 'dir \\server_name\folder_name\ /b /s';
INSERT INTO #TempTable
execute xp_cmdshell @CMD;
SELECT * FROM #TempTable;The SELECT above returns:
Access is denied.
NULL
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
SQLSERVER service was running under Local System account.
I changed it to use my own account then restarted SQLSERVER service and the proc works fine. My account's password expires periodically so can't use that one on a permanent basis -- working with network team to either give correct permissions to Local System account or use an account that has those permissions and password does not expire.
Thx!
I changed it to use my own account then restarted SQLSERVER service and the proc works fine. My account's password expires periodically so can't use that one on a permanent basis -- working with network team to either give correct permissions to Local System account or use an account that has those permissions and password does not expire.
Thx!
You won't be able to give remote permissions to a Local account. If you want the SQL account to have permissions to remote "objects" (files, dirs, etc.), you will have to switch it to a domain account.
EXEC master.dbo.xp_cmdshell @vchCMD;
yourself and see what error msg you get. The command should echo back the system error msg (usually).
It's most often a permissions issue. Does the SQL Server service account have authority to access that remove path?