xp_cmdshell Behavior

[Ed Molinari]

Hi all,
I have a question regarding some very odd NT/SQL behavior that is exhibited when running a shell command through xp_cmdshell. If I do a 'net use z: \servernamesharename' from the xp_cmdshell, it shows up in Explorer with the icon for a local drive! This would not be more than an ordinary M$ bug, except for the fact if go to a command line, I can't do a 'net use z: /delete' because it says that the network drive isn't there! Can someone shed some light on this?

Thanks,
Ed Molinari
Emerald Solutions

[Kenneth Wilhelmsson]

Hi Ed.

Are you running the SQL Server as a Local System?
Wild guess, but could it have something to do with who owns z: after it's mapped?
I believe that Local System account is not quite the same as Administrator.
I replicated your scenario on a Local System server, and sure enough it does what you say. Haven't tried it in a server running a domain account, though.

However, it's no problem deleting with xp_cmdshell 'net use z: \servershare /delete'
The server can close what it opened alright.

Could be a bug, but on the other hand I can't really see any use for mapping a network drive through xp_cmdshell for other users than the SQLserver service.

/Kenneth
------------
Ed Molinari at 5/28/99 2:12:44 PM

Hi all,
I have a question regarding some very odd NT/SQL behavior that is exhibited when running a shell command through xp_cmdshell. If I do a 'net use z: \servernamesharename' from the xp_cmdshell, it shows up in Explorer with the icon for a local drive! This would not be more than an ordinary M$ bug, except for the fact if go to a command line, I can't do a 'net use z: /delete' because it says that the network drive isn't there! Can someone shed some light on this?

Thanks,
Ed Molinari
Emerald Solutions

[Kenneth Wilhelmsson]

Hi Ed.

I'll post your reply to my e-mail account for others to share. After all, that's was makes a discussion live. =;-)

Can't you use UNC paths like \serversharefile to do whatever the proc is doing? It should certainly work for moving files and such.
Don't sure if the mssqlserver account needs to be a proper domain user though.
At any rate, it needs to have the appropriate permissions set on the remote share for whatever it wants to do.

If UNC style works for you, you should be able to drop the net use approach.
/Kenneth

Hi Kenneth,
Thanks for answering the SQL post in swynk.com. I just wanted to give you
the background dope on what's going on.
The MSSQLServer and SQLExecutive services are running as Local Admin
accounts. We have to manage our servers through local accounts because our
client won't give us domain access. But these services are not running as
LocalSystem.
The purpose of the mappings is a stored procedure that runs as a scheduled
task that automates the processing of ftp feeds that come into a web server.
The stored procedure connects to the mapped drives, does what it does, then
disconnects the mappings. Intermittently, it does not disconnect the
mappings. When this happens, the drives show up as you saw them: local
drives. If you try to disconnect them with the 'cmd' prompt, you will be
told that the network drive does not exist. This causes the stored
procedure to fail.
Theory would have it that the xp_cmdshell would then be able to delete the
drive mapping. Ah..if life were so easy. It seems that if the disconect
fails the first time, it will fail on a successive attempt. I have the 'net
use x: /delete' at the beginning and end of the stored procedure in case
another admin has mistakenly left a drive mapped. I wind up having to stop
the WorkStation service to get rid of the mapping.
I can't find a pattern to this. I could live with the obscurity if it did
not interfere. Any thoughts on another way to get at the data without
going through this?

Thanks so much,
Ed Molinari
Senior Technical Architect
Emerald Solutions
9086585227