SQL SERVER – xp_cmdshell and Net Use ERROR: The Local Device Name is Already in Use

People generally ask me; how do I manage to find a blog topic for every day blogging? And my answer is – “Connect yourself to the ground, try to help people with the basics, have open eyes & ears and promise yourself to learn one new thing every day”. If one can achieve these, every day blogging is easier. As a part of my consulting, I was helping a client to run some scripts and while testing, I learned something new. In this blog, we would learn about cause and solution of the error, the local device name is already in use.

So, while running some script for testing, I encountered an interesting error which I have not seen earlier.

SQL SERVER – xp_cmdshell and Net Use ERROR: The Local Device Name is Already in Use net-use-xp-cmdshell-01

Here is the script using which I am mapping a share on the local machine as Y drive.

exec xp_cmdshell 'net use Y: \\127.0.0.1\Shared aVeRyStR0nGP@s$w0rd@123 /USER:Administrator /PERSISTENT:yes'
go

output
—————————————–
System error 85 has occurred.
NULL
The local device name is already in use.
NULL
NULL

When I checked My computer, I didn’t see Y: drive mapped there.

SQL SERVER – xp_cmdshell and Net Use ERROR: The Local Device Name is Already in Use net-use-xp-cmdshell-02

WORKAROUND

While searching on the internet, I learned that drive mapped with in SQL (via net use command) would not be visible in the OS as its running as a service and it doesn’t have it desktop. I have not tried this, but I think if I login with service account to the operating system, we should be able to see the drive in OS also. Again, I have not tested this so if you find answer to this question then please share via comments.

To confirm this, we can use below command.

exec xp_cmdshell 'net use'

and as mentioned in error message, Y is already in use.

SQL SERVER – xp_cmdshell and Net Use ERROR: The Local Device Name is Already in Use net-use-xp-cmdshell-03

To delete Y drive, we need to use below command.

exec xp_cmdshell 'net use Y: /delete'

Once the command is completed, we were able to map the drive again.

Hope this blog would help someone to fix the error involving net use and xp_cmdshell.

Reference : Pinal Dave (https://blog.sqlauthority.com)

Quest

Command Line, SQL Command, SQL Error Messages, SQL Scripts, SQL Server, SQL Stored Procedure
Previous Post
SQL SERVER – Linked Server Error: TCP Provider: No Connection Could be Made Because the Target Machine Actively Refused It
Next Post
SQL SERVER – Log Shipping Monitor Not Getting Updated

Related Posts

5 Comments. Leave new

  • André Melancia
    December 29, 2017 7:28 am

    I’ve seen this kind of problem a few times.
    Drive assignments are actually user-dependant, so the Database Engine service user will be able to assign drive letters that aren’t visible to the normal logged in user (and vice versa).
    Running “NET USE” on different users gives you an idea of what’s happening in each case (as the example in the post).
    Also, this is very scary on reboots (loses assigned drives), so when possible I’d avoid this technique.

    Reply
  • wow – that’s horrible!
    why would someone map a share instead of using unc paths?
    br and happy new year
    gerald

    Reply
  • I have s server in which I do backup to a network share and did not mount-map any remote share. I did so by giving access rights on the remote share permissions tab to the windows server running sql server like SERVER$

    Reply

Leave a Reply