Solved

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

Posted on 2012-03-23
5
657 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
YESTERDAY YESTERDAY.BAT is inspired by a previous article I wrote entitled: TOMORROW.BAT (http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/MS_DOS/A_4196-Advanced-Batch-File-Programming-TOMORROW-BAT.html). The crux of this batch f…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

739 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