?
Solved

xp_cmdshell help?

Posted on 2009-12-18
16
Medium Priority
?
692 Views
Last Modified: 2012-05-08
Hi there,

I am having an issue with the xp_cmdshell command in sql server.  I have a batch file I need to execute on a remote server \\myservernamehere\D$ ... I tried doing the cmdshell with the UNC path and it said it couldn't find the specified path.  I also tried a couple other things with it and it said UNC paths are not supported.

So, I remoted into the server and mapped the network path to a local drive on the server.  I had to specify a username and password to do the mapping.  I mapped it to K:\

I can click start > run > cmd on the server and get to the K:\ drive there.  When I go back to my desktop and try to execute the cmdshell from my stored procedure it still says it cannot find the drive specified.  

I have tested it doing other things... I can see the c: drive and the d: drive on the server via the xp_cmdshell (so I know it isn't a sql permission thing with the cmdshell) ...

What am I doing wrong?  Is the cmdshell incapable of running something on a network share?  I'm really desperate to get this fixed.... any help is appreciated.

We are running SQL Server 2000.
0
Comment
Question by:Roxanne25
  • 7
  • 5
  • 4
16 Comments
 
LVL 41

Accepted Solution

by:
ralmada earned 1200 total points
ID: 26082751
You need to make sure that the Service account running SQL Server has permission on that network drive. Check this links for further reference:
 http://sql-server-performance.com/Community/forums/p/25049/139347.aspx#139347
http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/ab71bdf7-10d7-4cc8-8386-4c357c80920a 
And this other one to identify your service account (click on Listing 1, to see the SQL code)
http://www.sqlmag.com/Article/ArticleID/46106/sql_server_46106.html 
0
 

Author Comment

by:Roxanne25
ID: 26082902
I created that stored procedure in the third link... it says my service is running under the LocalSystem account?  That doesn't sound very helpful! :)
0
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 1200 total points
ID: 26082965
Well, actually it is very helpful, because that means that you are trying to access a network resource with a local account. So you need to use an account with access to the domain, and in particular to your share folder. Check the second link above.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 800 total points
ID: 26083057
>>it says my service is running under the LocalSystem account? <<
For security reasons MS SQL Server is installed with the SQL Server service account set to the Local System account which cannot access anything beyond the local box.  If you need it to access another server, than you are going to have to change it to a domain account with the appropriate permissions.  This will require you to restart the services.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 26083108
As indicated in the second link above: "To change the local account, Go to SQL Server Configuration Manager. Right click on Sql Server, click properties and change the user account to run under a domain account that has access to the shared resource."
And as mentioned by acperkins, you will need to restart the service.

 
0
 

Author Comment

by:Roxanne25
ID: 26083163
Ok thanks, unfortunately I can't do that.  We are the "development" shop and would require lockheed martin to go in and make these changes... and I don't think we have a domain account that would have ALL the necessary permissions needed to access everything....

Hmmm... my coworker suggested another way to do this in that he has a command file that he uses to do a "net use" dos command?  He said that is how he accesses across the network....
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 800 total points
ID: 26083225
>>Hmmm... my coworker suggested another way to do this in that he has a command file that he uses to do a "net use" dos command?  He said that is how he accesses across the network....<<
Most definitely that will work, it is just that you will not be able to use xp_cmdshell to do that.
0
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 1200 total points
ID: 26083227
>>he has a command file that he uses to do a "net use" dos command?  He said that is how he accesses across the network....<<
Yes, but he is using a domain account to do that. not the SQL service account.
0
 

Author Comment

by:Roxanne25
ID: 26083267
Right....

Is the only way to do that to create a command file and code appropriately?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26083293
You have a permissions problem, not a code problem.  However you execute your program it has to have the right permissions.
0
 

Author Comment

by:Roxanne25
ID: 26083405
Thanks ac, but I'm now talking about using the net use command instead of using the xp_cmdshell .... I'm aware that the permissions are not correct for xp_cmdshell.
0
 

Author Comment

by:Roxanne25
ID: 26083650
Thanks again for all the input!

I can still use the xp_cmdshell actually.... I just did a net use command from within sql server and created a drive .... and now everytime I run a xp_cmdshell it knows what drive I want it to use! :)

Just gotta remember to delete the net use drive when I'm done.

Thanks again!
0
 
LVL 41

Expert Comment

by:ralmada
ID: 26083668
>>using the net use command instead of using the xp_cmdshell <<
I'm not sure I'm understading your comment here. But basically you cannot do something like this:
xp_cmdshell 'NET USE K: \\server\yourfolder'
Because again SQL service account don't have access to the shared resource
 
0
 

Author Comment

by:Roxanne25
ID: 26083703
You are correct... I didn't do it like that...

I did it like this:  

SET @CMDSTRING = 'net use z: \\servername\d$ ' + @PASSWORD + ' /USER:HHQ\' + @USERNAME + ''

exec xp_cmdshell @CMDSTRING
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26085442
I cannot see how that can work for you, but if it does more power to you.

Good luck.
0
 

Author Comment

by:Roxanne25
ID: 26085451
Well I'm assuming it works because I'm mapping the drive within the SQL Server command instance and giving it a username and password that DOES have access to the network drive.  

It also seems that after I execute the NET USE in the xp_cmdshell I can then run subsequent commands in the same session with the drive that I map...

For exampe... I use the above code
SET @CMDSTRING = 'net use z: \\servername\d$ ' + @PASSWORD + ' /USER:HHQ\' + @USERNAME + ''

exec xp_cmdshell @CMDSTRING

the return output from this is: command executed successfully.

Next, I run another xp_cmdshell like this:

exec xp_cmdshell 'z:\somepath\enter\here\run.bat @Insert parameters

the return output then tells me it performed the requested task that I wanted it to.

Next, I run this:

xp_cmdshell 'NET USE z: /DELETE'

And that gets rid of the mapping I just made.  I personally don't know why that works and it doesn't work when I try to access a mapped drive on the server ...but it saved me a lot of headache today. :)
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

850 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