Connect using Windows authentication across domains

You’re a consultant or remote worker, and you’re connecting into your work or client network using a VPN. But when you try to connect to the SQL Server, you get this.

There are a few simple workarounds, each with their own pros and cons.

The jump box

A jump box is a virtual desktop on the client’s domain that you can connect to using Remote Desktop. You’d obviously have to ask somebody for access to one, and you’d have to set up your development environment from scratch. This may not be a big issue if you’re in SSMS all of the time, but when you need the Power BI Desktop, Excel or even Visual Studio, this setup can take some time (not to mention asking for local admin credentials on the jump box).

SQL Server authentication

Using SQL Server authentication requires mixed authentication on the server, which may not be popular in many environments. You’ll miss out on some domain-connected features that come with a proper Windows authenticated connection.

Windows Credentials

Windows has a built-in control panel called “Credential Manager”.

Select the Windows Credentials tab, then click “Add a Windows credential”:

Qualify your Windows user name with the domain name, like so:

domain\username

Now, retry the connection in SSMS and if the stars align properly, you’re in.

Making it work with named instances

Named instances are special. For starters, they typically use a non-standard TCP port, as opposed to the usual port 1433. To make that work, you’ll need to find out what that port number is and add it to your credential. There are a number of ways to find out which port your instance is using. You could check the SQL Server Configuration Manager (if you have access to it), or you could query existing TCP connections using a DMV:

SELECT DISTINCT local_tcp_port
FROM sys.dm_exec_connections
WHERE local_net_address IS NOT NULL;

Once you’ve figured out what the instance port number is, add the port number to the server name, separated by a colon.

server.company.com:1455

For best results, I try to keep the name of the server in the credential and SSMS the same, so if you you use the fully qualified domain name (FQDN) of the server in your credential, do the same when connecting to the server. I’ve found that you can often use the instance name in the SSMS connection name,

server.company.com\instance

But if that doesn’t work, try the port number (delimited by a comma, because reasons)

server.company.com,1455

Gotchas

I find that the authentication process takes more time, so your connections may take a few seconds to establish, or they may even time out. A 30 second connection timeout will usually be quite sufficient.