Roxanne25
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
And as mentioned by acperkins, you will need to restart the service.
ASKER
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....
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....
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Right....
Is the only way to do that to create a command file and code appropriately?
Is the only way to do that to create a command file and code appropriately?
You have a permissions problem, not a code problem. However you execute your program it has to have the right permissions.
ASKER
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.
ASKER
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!
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!
>>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
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
ASKER
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
I did it like this:
SET @CMDSTRING = 'net use z: \\servername\d$ ' + @PASSWORD + ' /USER:HHQ\' + @USERNAME + ''
exec xp_cmdshell @CMDSTRING
I cannot see how that can work for you, but if it does more power to you.
Good luck.
Good luck.
ASKER
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\ru
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. :)
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\ru
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. :)
ASKER