[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 663
  • Last Modified:

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\ /b /s';      
      
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?
0
Eric3141
Asked:
Eric3141
  • 3
  • 2
1 Solution
 
Scott PletcherSenior DBACommented:
Run the

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?
0
 
Eric3141Author Commented:
When I manually run this from a query window

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;

Open in new window


The SELECT above returns:

Access is denied.
NULL
0
 
Scott PletcherSenior DBACommented:
Yep.  Typically it's a permissions issue of some sort.

I assume the id you log into SQL with has permissions to get to that dir and to read/write the files there (? right).

Does the SQL Server service account have authority to access that remove path?  (If you have sysadmin authority, SQL may run the xp_cmdshell under *SQL's* account rather than your account.)
0
 
Eric3141Author Commented:
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!
0
 
Scott PletcherSenior DBACommented:
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.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now