Solved

using xp_cmdshell and DOS "DIR" cmd to get info from another server

Posted on 2012-03-23
5
656 Views
Last Modified: 2012-03-23
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
Comment
Question by:Eric3141
  • 3
  • 2
5 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37758490
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
 
LVL 2

Author Comment

by:Eric3141
ID: 37758533
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 37758574
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
 
LVL 2

Author Comment

by:Eric3141
ID: 37758750
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37758837
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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question