How to map network drive in SQL query without using XP_CMDSHELL

C-COOP picture C-COOP · Oct 8, 2016 · Viewed 9k times · Source

Looking to just map a network drive with a different AD account in a SQL query.

XP_CMDSHELL is disabled in our environment. I could technically turn it on, then "net use" the drive, and turn it off again in a query, but was looking for a cleaner solution??

Answer

gofr1 picture gofr1 · Oct 12, 2016

You could create two jobs, in each just one step (with the type: Operating system (CmdExec) it will launch command in cmd).

  • In first job net use to create drive
  • In second - delete it

And then run them.

But be aware that in that case job will start and you need to wait few seconds to drive creation took place with the help of wait delay.

Example:

Create job NetUse. In steps create 1 step with the type: Operating system (CmdExec) In command part write net use like:

net use z: \\HOST\FOLDER pa$$word /user:DOMAIN\USER /savecred /p:yes

I want to map my z: drive to the FOLDER shared folder on HOST. I want to connect as another user account I have [/user] by the name of USER that's stored on the DOMAIN domain with a password of pa$$word.

I don't want to map this drive manually every time I start my computer [/p:yes] and I don't want to enter my username and password each time [/savecred].

The first job is done.

Second job same but with another command:

net use z: /delete

Then you can start it like:

EXEC dbo.sp_start_job N'Job Name Here' 

It will workout with some delay so you need to use:

WAITFOR DELAY '00:10'; --10 seconds delay before next statement