sql-servert-sqlxp-cmdshellnet-use

How to map network drive in SQL query without using XP_CMDSHELL


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


Solution

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

    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