Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Connect to SQL Servers in another domain using Windows Authentication


By:   |   Read Comments (25)   |   Related Tips: More > SQL Server Management Studio

Problem

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.

Solutions

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.

runas /netonly

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.)

Credential Manager

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 hosts file, 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 server:port notation), the domain user (including the domain name), the password, and click OK:

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: server,port). 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. The login is from an untrusted domain and cannot be used with Windows authentication.

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.

Conclusion

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.

Next Steps


Last Update:






About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand is a Senior Consultant at SQL Sentry, Inc., and has been contributing to the community for about two decades, first earning the Microsoft MVP award in 1997.

View all my tips
Related Resources





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, March 15, 2017 - 2:21:39 PM - Douglas Correa Back To Top

 

Why not use shift + right click -> Run as different user ?

 


Monday, March 13, 2017 - 7:36:40 PM - Mike Russell Back To Top

 Thanks!  Very clear and solved exactly the problem that was driving me nuts.

 


Tuesday, January 31, 2017 - 6:19:51 PM - marcelo Back To Top

 Nice tip! Thank you!


Friday, December 30, 2016 - 3:55:25 AM - Ufuk Sürmen Back To Top

 Hi Aaron. I get we have two options while connect remote sql servers or other domains with windows authantication.

But isn't windows authantication means thay the userwho logged in to windows has authantication to use sql server. So since I am not an authorized login or user in remote servers I mean i am not an windows user on their machine how could I can connect totheir servers. So everyine who ko ws my  domain name and instance name can connect to my machine. So what about security. It must be like i belive adding a user,  we must add other windows users or domains authorized to enter our server. Please help what is the point i missed?


Thursday, October 13, 2016 - 7:41:32 PM - Elliott Back To Top

Fantastic post!
Thanks Aaron!


Thursday, September 29, 2016 - 3:25:01 AM - Nishad Back To Top

Thanks very much for wonderful tip! 

 


Tuesday, July 19, 2016 - 3:12:20 AM - Ram Back To Top

Great tips! Thank you!

 


Tuesday, June 07, 2016 - 1:40:17 PM - Mike Back To Top

 

Thanks!  Works like a charm.


Monday, May 23, 2016 - 9:57:27 AM - Mike Back To Top

Adding the port in Credential Manager did the trick. You just saved me tons of time with this awesome tip!

 

 


Thursday, April 14, 2016 - 7:37:45 AM - Luca Back To Top

The runas tip is a stroke of genius! So thanks!


Monday, March 28, 2016 - 12:42:02 PM - Joel Back To Top

 I was not aware of the runas command. Pretty nifty, thanks!

 


Wednesday, March 02, 2016 - 12:58:43 PM - James Back To Top

Thank you so much. I wasn't aware of the credential manager. This was a HUGE help!


Friday, September 25, 2015 - 1:18:45 PM - Syed Back To Top

I appreciate your clear and easy direction. Continue to share more, it's really a great help. Thanks much!

 

 


Friday, November 14, 2014 - 2:15:16 PM - Aarti Back To Top

Fantastic solution and a life saver. Thanks!!


Friday, November 14, 2014 - 5:43:06 AM - Wojciech Wróblewski Back To Top

Thank's a lot! This article helped me However, I had to combine the credential manager step with runas shortcut.

 


Monday, October 27, 2014 - 11:26:49 PM - Brandon Back To Top

I've tried all sorts of combinations in Credential Manager, but never thought to include the port! THANK YOU!


Tuesday, October 07, 2014 - 5:18:10 PM - Vick Back To Top

Woks great, thank you.

One concern is do we have to change password of the Windows credential everytime it changes on the other domain? We have a rule to change it once every quarter.

Thanks!


Thursday, September 11, 2014 - 6:32:37 AM - jerem Back To Top

Thanks it helps me a lot!


Tuesday, August 05, 2014 - 10:56:28 AM - Gb Back To Top

Thank you! This is a great solution!


Monday, July 07, 2014 - 9:06:49 AM - Sanjay Monpara Back To Top

Hay Aaron , Its wonderful...

Thanks


Wednesday, June 11, 2014 - 4:25:02 PM - Aaron Bertrand Back To Top

@Julia, that works great if you are specifying a user in the same domain (or perhaps across domains with a domain trust). AFAIK it doesn't work across domains, or from a standalone Windows machine in its own workgroup...


Wednesday, June 11, 2014 - 3:04:21 PM - Julia Back To Top

I use much easier way: hold the Shift key and right-click on the Management Studio/BIDS icon, select Run as a different user and type your other domain credentials. You are there!


Wednesday, June 11, 2014 - 11:53:20 AM - Kimber Back To Top

Thank you! The credential manager instructions were exactly what I needed!


Tuesday, June 10, 2014 - 11:06:06 AM - M. Kokoy Back To Top

Great article.  Thank you.


Tuesday, June 10, 2014 - 8:48:53 AM - John G Back To Top

Great tip, Aaron. We were just having a discussion about how to connect non-domain machines via SSMS using domain credentialss, this takes the command-line solution to the next level with a desktop icon and stored creds.

Thanks for a very timely tip!

John G

 


Learn more about SQL Server tools