Solved

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

Posted on 2012-03-23
5
658 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

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…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

726 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