CODEWITHSUNDEEP
sqlsql-server-2005
David.Chu.ca
David.Chu.ca

Reputation: 38704

Map to Network Drive By SQL SP

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?

Upvotes: 8

Views: 10596

Answers (7)

KuvaldaMan
KuvaldaMan

Reputation: 21

I spent one hour, but the problem solves: You must specify [HOST]\[username] If you write like this: u:/username - it doesn't work

Upvotes: 2

Lizz
Lizz

Reputation: 1470

The problem happens when using Distributed File System, no matter which operating system you're using. Instead, get the actual name of a server hosting the DFS content.

Upvotes: 0

Martin
Martin

Reputation: 10888

In an other forum someone posted a tip about how to solve the System error 1312 has occurred. problem by specifying the username including its domain like this:

net use Q: \\[host]\[folder] [pwd] /user:[host]\[username]

I tried it and it worked for me.

Upvotes: 13

David.Chu.ca
David.Chu.ca

Reputation: 38704

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.

Upvotes: 0

David.Chu.ca
David.Chu.ca

Reputation: 38704

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.

Upvotes: 0

Rob Williams
Rob Williams

Reputation: 7921

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.

Upvotes: 0

Tom H
Tom H

Reputation: 47402

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.

Upvotes: 1

Related Questions