2

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??

C-COOP
  • 123
  • 1
  • 3
  • 12
  • What are you doing within T-SQL that requires a mapped drive? – Dan Guzman Oct 08 '16 at 01:19
  • `net use` via `xp_cmdshell` is the easiest way, I guess :) – gofr1 Oct 09 '16 at 18:14
  • @Dan Guzman I am pulling import files from a network folder with specific security. I was given an AD account to use that has access to that folder... – C-COOP Oct 11 '16 at 14:00
  • @gofr1 Cannot use xp_cmdshell - it is disabled in our prod database.... – C-COOP Oct 11 '16 at 16:30
  • @C-COOP do you have a permission to create/start job? If yes, you could create two jobs, in each just one step, in first job net use to create drive in second - delete l. 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 – gofr1 Oct 11 '16 at 16:56
  • @gofr1 I can create a job, but could you clarify - I don't understand... – C-COOP Oct 11 '16 at 20:19
  • @C-COOP that's too large for comment, I add an answer. Maybe that will help you. – gofr1 Oct 12 '16 at 07:17

1 Answers1

1

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
gofr1
  • 15,741
  • 11
  • 42
  • 52
  • OK, I get it - good solution. I am having a problem with my net use command... do you see an issue with it? – C-COOP Oct 13 '16 at 17:26
  • cmd.exe /c if not exist z:\* net use z: \\server-fp-01\Finance PASSWORD /U:USERID – C-COOP Oct 13 '16 at 17:28
  • Get rid of `cmd.exe`. The command you wrote will be executed within cmd automatically. – gofr1 Oct 13 '16 at 17:31
  • `if not exists z:\ ( net use ... ) else ( ... )` – gofr1 Oct 13 '16 at 17:35
  • That did work, are there any ramifications that this method? Am I affecting the sql server agent's drive mapping for others? I am wondering if there was already another Z: drive mapped by the service account running the agent service? – C-COOP Oct 21 '16 at 17:02
  • It is the same, if you net use the drive by yourself to this server. Ramifications? Well, somebody could run the same net use to add some other share folder to the same z drive... And will get error. There could be another drive mapped already. But no matter, you do it with cmd by yourself, run xp_cmdshell or use job, or PowerShell script all this methods could make someone's other scripts with net use z fail. – gofr1 Oct 21 '16 at 17:14
  • What is the purpose of net use in your case? Copy files? Run some SSIS to export data? – gofr1 Oct 21 '16 at 17:22