If you’re a consultant connecting to remote client servers, or if you have a heterogenous network environment with different Active Directory forests without established trust relationships, you’ll have a few extra challenges connecting to SQL Server using Windows authentication, and SQL Server authentication may not be available.
The easy way to solve this is to just log on directly to the remote server using Remote Desktop and use Management Studio on that session, but this is not really desirable for several reasons: not only will your Remote Desktop session consume quite a bit of memory and server resources, but you’ll also lose all the customizations and scripts that you may have handy in your local SSMS configuration.
Your mileage may vary with these solutions, and I don’t have the requisite skills to elaborate on the finer points with regards to when one solution will work over another, so just give them a try and see what works for you.
Option 1
You can establish a SMB (network file share) connection to the server from your workstation. First, open the “Run…” dialog, then enter the name of the server, prefixed by two backslashes:
When you click OK, it takes anywhere from a few seconds to even a minute or two to establish the network connection. You’ll get this authentication dialog, where you have to provide your credentials:
Enter the fully qualified username that you want to connect with, for instance user@domain, domain\user or server\user (for a local account on the server).
Once you’ve been authenticated, try starting SQL Server Management Studio and connect to the server using Windows authentication. In my experience, Windows will also remember these credentials the next time you want to connect, as long as you check the “Remember my credentials” box.
The screenshots are from Windows 10, but I’ve used this technique on a multitude of different Windows versions.
Option 2
You can launch Management Studio using the command-line utility “runas.exe“. You can do this from the “Run…” prompt or by altering the start menu shortcut, depending on your needs.
runas.exe gives you a lot of other options, as detailed in the documentation, but it’s a little more work in my opinion. Remember to make corrections for your path to the SSMS executable.
runas.exe /netonly /user:domain\user "C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\Ssms.exe"
If runas.exe doesn’t work, double-check that the “Secondary logon” service is running on your local machine. The “/netonly” option tells Windows that this process is only for remote connections.
Again, your mileage may vary. Try both options to see which one will work for you.