Map to Network Drive By SQL SP

Question

Is there any way to map to a network drive by using a stored procedure? I have tried:

xp_cmdshell 'net use Q: [shared_network_drive] [pwd] /user:[username]'

but I got an error saying something like

'System error 1312 has occurred.'
'A specified logon session does not exist. It may already have been terminated.'

However, when I run the same command in a cmd console in the same box as the SQL server machine, there is no problem at all. It looks like that SQL process does pass correct logon credential information. Any way to do it by SQL SP?

Answer 1

Do you have a proxy account set up for xp_cmdshell? If you are not a member of sysadmin, it requires a proxy account. The documentation for xp_cmdshell and sp_xp_cmdshell_proxy_account will explain it in detail, but try executing the following:

EXEC sp_xp_cmdshell_proxy_account 'DOMAIN\Username', 'password'

Of course, replace the user and password information with the account information on your network under which you want this to execute.

WARNING: xp_cmdshell can be a HUGE hole in security. Make sure that at the very least the proxy account that you use has very limited security. Don't use any kind of admin account and don't use an actual user's account.

Answer 2

Why would you even want to do this?!? Even if it is possible, you should be carefully asking yourself why you would want to do so.

My suggestion: don't. My impression is that you need to change your approach--whatever purpose that network drive mapping might serve should be pursued in a more appropriate place outside of the database engine. Once you choose that appropriate place, actually invoking the mapping will probably become trivial.

Answer 3

Rod, you are right. It is a big risk to do that in SQL server and not all the SQL admins would allow to open this door. What I am doing is to migrate legacy process to a scheduled SQL job so that required data will be available for another department's application to fetch data in another scheduled process.

I guess that a windows service job might be a better application to pull data out and transfer the result to the required network drive.

Answer 4

Actually, use Win Scheduled Tasks tool is what I need. I can create a .Net app to do the job and then add the application to the scheduled tasks.