By: Aaron Bertrand | Last Updated: 2014-06-10 | Comments (35) | SQL Server Management Studio
You may find that you need to connect remotely to a SQL Server that is in another domain. Since I work from home, I face this challenge all the time - almost every SQL Server I connect to is in another domain (and usually behind a VPN, a stingy firewall, and a stingier network team). This is a problem when SQL authentication is not an option, because my Windows machine is in its own domain; Management Studio cannot directly override the local Windows credentials and pass the credentials for a remote domain user.
The easy answer is to simply establish a remote desktop session to the machine in question, and use Management Studio and other tools locally, but there are several challenges with that. Many corporations have policies in place that prevent tools like SSMS from being installed on the server, and some even resort to Server Core at least in part as a means to enforce this policy. They may not have a dedicated "jump" box to support remote desktop connections to the SQL Server machine, or they may have such a box, but without all of the tools you require.
Aside from pushing for SQL Authentication, adding your machine to the remote domain, or making your local domain trusted, there are two approaches commonly used to get around this problem. Both work equally well for Management Studio, Visual Studio, SSDT, Excel, and many other applications that support passing Windows credentials. To keep things simple, I'm going to stick to the most common use case, Management Studio.
The method I use is the Windows built-in runas command. This allows you to run an application with Windows credentials you provide on the command line. The syntax is relatively simple:
runas /netonly /user:domain\username "c:\path\ssms.exe"
At this point, you are prompted for the password for the remote user and, once provided, you are told that it is attempting to run the program as that user. With this instance of the application, when you attempt to connect to the remote server using Windows Authentication (say, with a New Query window), it will *look* like it is using your local Windows credentials in the connection dialog, but in reality - behind the scenes - it is using the username you passed on the command line.
In the above screen shot, I did not need to pass the full path to ssms.exe, because the path to the executable is already defined in my environment variables. You may need to specify the full path, particularly if you have multiple versions of SSMS installed.
If you are going to be connecting to the same remote domain many times, you may want to set up a shortcut to allow you to bypass entering all this information on the command line every time. Right-click the desktop and choose New > Shortcut. Then enter the same information as above in the command line:
And give the shortcut a label that will make it easy to identify:
Now you can double-click the shortcut and, after simply providing the remote password, it will launch an instance of Management Studio that will use those remote credentials every time you use Windows Authentication and try to connect to a remote server:
(You can achieve similar results using Microsoft's SysInternals tool, ShellRunas.)
Another method you can use to connect to remote domains using Windows Authentication is to use the Credential Manager built into Windows. This can be a little tricky to setup, especially if you are connecting over a VPN (and an added complication in my environment is that I am using a Mac host, connected by VPN to a remote domain in a data center, and connecting from Management Studio inside a Windows virtual machine).
The part that is tricky is ensuring that name resolution matches exactly. The
Windows machine needs to identify the remote server by IP or fully-qualified domain
name (FQDN), and may need to explicitly specify the port. I was finally able to
make this work once I added the remote server names to my
and then ran
ipconfig /dnsflush. I am not sure if the complication
here is due to the VPN on its own, or the fact that I have an extra network translation
layer due to the interaction with an additional operating system. Or maybe both.
Once you are sure you have the right FQDN (make sure you can ping it by name, or better yet, telnet to the server name on the SQL Server port (usually 1433)), go to Control Panel > Credential Manager, choose Windows Credentials, and Add a Windows Credential:
Then specify the specific server name (potentially you may need
notation), the domain user (including the domain name), the password, and click
Now, when you connect from Management Studio, make sure you use the exact same
server name as the one you entered in the credential, including the port number
(only now, use a comma instead of colon if specifying the port number:
Like with the runas /netonly solution, it will *look* like you are connecting using
your local Windows credentials, but - as long as the credential store doesn't have
any issues in matching the server name - those really are the remote credentials
that are being passed under the covers.
You will know quite quickly if the credentials are not matched - if you do not establish connectivity immediately, you will receive one of these errors:
Login failed for user ''. The user is not associated with a trusted SQL Server connection.
These could be because the local credentials were passed, or because the username didn't include the domain prefix or used the wrong domain name. Double check all of the properties of the stored credential and recreate it if you need to (you can modify the username and password, but if the server name is wrong, you'll need to re-create it).
Once it is working, the difference in behavior here is that the credential is looked up every time you try to connect, based on the IP or server name entered, rather than when you launch the application. You will need to create a separate Windows Credential for every server you need to connect to; it does not work across all servers in a domain.
Those are two ways you can connect to SQL Servers in a remote domain and continue using Windows Authentication. In my opinion, the runas option is better if you only use SSMS to connect to a couple of remote domains (or a lot of different servers within one remote domain), since it is much quicker to set up and manage, and because the same Windows credentials will be passed for all of the servers you connect to from that instance of SSMS. The Credential Manager solution - while a little more cumbersome to set up, and a little trickier to troubleshoot - is better if you use the same instance of Management Studio to connect to many different servers, especially if they are in different environments, since it will automatically pick the right domain credentials based on server name, not based on the application's startup arguments.
Of course, some of the reasons that prevent you from being able to connect to SQL Server inside the domain may also prevent you from using it outside, regardless of what application or credential passing mechanism you are using; for example, the port you need to connect to may not be open, or you may need to experiment with both IP address and FQDN to establish connectivity. In some cases, obtaining access to the SQL Server may still need further action by the network operations team in charge of the domain you're trying to access. Note that this is not really any different from connectivity issues when using SQL Authentication.
- Consolidate your remote connections using runas shortcuts or Windows Credentials.
- Review the following tips:
Last Updated: 2014-06-10
About the author
View all my tips