Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2012-03-23
5
Medium Priority
?
665 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 70

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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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 70

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.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

606 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