Using Windows Authentication to Connect to SQL Server from Outside the Domain

Have you ever wanted to connect to SQL Server using Windows Authentication from a computer that is not joined to the domain?

Some might find such an idea inconceivable.  But, that word, I do not think it means what they think it means.

In fact, it is quite possible to connect to tables in SQL Server using Windows Authentication from a non-domain-joined computer.

Prerequisites

Of course, you do still need an Active Directory account with appropriate SQL Server permissions on the target server.

You also need to be able to establish a network connection to the SQL Server computer, whether that's via WiFi, network cable, or VPN.  

Step-by-Step Instructions

For this example, let's assume that I log on to my non-domain-joined device with a local account named "Mike", but I have an Active Directory account named "mwolfe" on a domain named "MyDomain":

1. Open a command prompt ( [Win] + [R] > cmd > [Enter])
2. From the command prompt: runas /user:MyDomain\mwolfe /netonly cmd
3. Enter your password (nothing will show on screen as you type your password)
4. A new command prompt will open with the title "cmd (running as MyDomain\mwolfe)"
5. Map any required network drives via the net use command
6. Launch your desired application from this command window (MSAccess.exe, Ssms.exe, etc.)

Returning the User Name

As the name of the /netonly flag implies, the credentials you supply to the runas command will be used only when communicating with the network.  For all other purposes, the application will be executing under your local account.  

So, for instance, if I use local functions to access my user name–such as Environ("USERNAME") or even the more trustworthy WNetGetUser API call–the name it returns will be "Mike."

However, if I request the user name from SQL Server–say, via the SYSTEM_USER function–the name it returns will be "MyDomain\mwolfe."

An Environment of its Own

Python has a concept known as virtual environments.

You can think of these as sandboxes that are walled off from each other.  They allow you as a developer to run different applications under different conditions.  In Python, that includes installing different library versions in each of these virtual environments.  They are also persistent, meaning that you can return to a saved virtual environment again in the future.

The /netonly command window–the one with "(running as mydomain\mwolfe)" in the title–is sort of like a "mini virtual environment."  You can't save it for later.  You can't install anything in it.  But you can have multiple instances of these command windows open at the same time, and they are walled off from each other to a degree.

Obviously, each /netonly command window will be using different network credentials than a typical command prompt.  But, it will also have its own network drive mappings.  

This is something that is likely to trip you up the first time you run into it.  The /netonly command window will not have any network drive mappings from your main Windows experience.  In other words, network drives that appear in File Explorer under This PC, will only be available in your "runas" command window if you explicitly add them (hence, the reason for the inclusion of Step 5 above).

Running Multiple Environments

This can be a handy side effect if you want to be able to, for example, use the subst command to remap different local folders to the same drive letter.  In fact, since the credentials are only used for network access, you can create multiple command windows using bogus credentials as a way to take advantage of these "mini virtual environments."

External references

Image by Merio from Pixabay