xp_cmdshell help?

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.
Roxanne25Asked:
Who is Participating?
 
ralmadaConnect With a Mentor Commented:
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
 
Roxanne25Author Commented:
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
 
ralmadaConnect With a Mentor Commented:
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Anthony PerkinsConnect With a Mentor Commented:
>>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
 
ralmadaCommented:
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
 
Roxanne25Author Commented:
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
 
Anthony PerkinsConnect With a Mentor Commented:
>>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
 
ralmadaConnect With a Mentor Commented:
>>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
 
Roxanne25Author Commented:
Right....

Is the only way to do that to create a command file and code appropriately?
0
 
Anthony PerkinsCommented:
You have a permissions problem, not a code problem.  However you execute your program it has to have the right permissions.
0
 
Roxanne25Author Commented:
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
 
Roxanne25Author Commented:
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
 
ralmadaCommented:
>>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
 
Roxanne25Author Commented:
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
 
Anthony PerkinsCommented:
I cannot see how that can work for you, but if it does more power to you.

Good luck.
0
 
Roxanne25Author Commented:
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
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.

All Courses

From novice to tech pro — start learning today.