Connect to SQL Servers in another domain using Windows Authentication

By:   |   Updated: 2023-10-04   |   Comments (48)   |   Related: > 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.

Solution

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 /noprofile /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:

command prompt

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:

connection dialog

But in reality - behind the scenes - you are impersonating the login you passed on the command line. You can confirm this by querying SUSER_SNAME(). And while it may not have always been the case, in modern versions of Management Studio, you can also confirm this by looking at the username next to the server you've connected to in Object Explorer:

evidence of impersonation

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:

create shortcut

And give the shortcut a label that will make it easy to identify:

name shortcut

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:

launching shortcut

(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:

credential manager

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:

add a windows credential

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 name (or user the wrong one). 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 or connecting from inside the domain or on the same network.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2023-10-04

Comments For This Article




Friday, October 11, 2024 - 7:05:46 PM - Binni Back To Top (92564)
Thanks a lot, worked like a charm :)

Monday, June 24, 2024 - 2:52:44 PM - adolpo Back To Top (92341)
Hello . I am newbie ... how can someone do it from a pc with domain to a pc that is out of domain?
thanks in advance

Wednesday, December 28, 2022 - 10:18:28 AM - Fred Back To Top (90786)
Thank you for your useful article.
I had a question:
In availability group, we can connect two nodes that are in the workgroup network. The communication method is Windows authentication and this communication is established through the FQDN and same SID of Windows logins in both nodes. So why can't we use Windows login in the same way to be remote to the engine server?

Friday, August 5, 2022 - 7:03:07 AM - Uladzislau Marhunou Back To Top (90349)
Thanks!

Sunday, April 24, 2022 - 10:02:22 AM - William Back To Top (90030)
Searched the whole Internet for two days, thank you Aaron! Using Credential Manager is painless and effort-saving!

Tuesday, February 1, 2022 - 7:40:06 AM - Jacques Back To Top (89719)
If you are trying to connect to a named instance then it's likely that it's not port 1433, so make sure to find out which port is used. Also, you don't need the instance name in the Credential Manager.

Thursday, September 9, 2021 - 2:21:37 PM - John L Back To Top (89221)
You know you have come across a great tip when it has glowing comments spanning across 7 years. I have been using the runas method for quite a while but I ran into an issue where I needed to connect to two databases, each in a different domain, in the same interface.
For that, the credential manager was exactly what I needed. Thank you!

Monday, April 26, 2021 - 5:09:49 PM - Richard E. Noecker Jr. Back To Top (88605)
For those having latency issues, we had issues when connecting to a SQL server in a domain that we were not in. For some it might take 3-5 seconds just to expand a node (ex. get list of tables), and the issues were even more pronounced for others. What resolved it for us was realizing that if we used a hostname (ex. sql.myserver.com) it was fast and it we used the IP it was slow. It must be doing a reverse DNS lookup on the IP and failing (slowly) for every single request. We originally used the IP since we were outside of the domain. If you can't resolve the hostname for this reason maybe add it to the windows hosts file as a workaround.

Monday, January 4, 2021 - 9:12:30 AM - TizzyFoe Back To Top (87995)
Great Tip, i have been using this for about 6 months. I see the same thing that reaver mentioned. Not just latency, it takes less then a second to run a query, but any SSMS action takes a very long time. For example in object explorer expanding the table list in a database will take about 30 seconds. Using the database drop down to change when database my query will run in takes about 30 seconds (though typing 'use myDatabase' is instant). Right clicking a stored procedure, it takes about 30 seconds to get my context menu to show up.

it also takes about 30 seconds to open a new query window. Presumably all these things must be triggering me to log in again and logging in with these methods takes about 30 seconds. Its Painful, but much less painful then alternatives. If anybody solves it, please let me know.

Wednesday, November 11, 2020 - 9:57:36 AM - TedG Back To Top (87779)
This is nice, but far from elegant. The problem with this method is that the App loses access to local domain and network resources. It's a clunky solution for medium-large enterprise. Office Apps such as Excel will launch in the context of any already running instance so you would have close all instances of Excel to open your RunAs version. If your enterprise uses profile redirection, users will not have access to those resources from the RunAs context and could potential "lose" files by saving them to the local (C: Drive) version of their profile rather than the redirected network instance.

With that said, there does not seem to be a better solution short of domain trust, so Thanks!

Wednesday, July 22, 2020 - 6:16:25 PM - David Back To Top (86177)

Thanks a lot! Very helpful


Tuesday, March 24, 2020 - 4:10:36 PM - jack Back To Top (85180)

Really helpful, thanks a lot ... I wish You a very nice day.


Wednesday, October 16, 2019 - 9:17:51 AM - Billy Back To Top (82799)

This is a really awesome instruction. It works for me.


Friday, May 24, 2019 - 7:42:25 AM - Jordan Back To Top (80191)

It worked perfectly! 

Thank you!


Tuesday, March 12, 2019 - 7:39:12 AM - Wil Dobson Back To Top (79265)

 Thank you for this tip! hours of googling on how to test a windows service account for SQL-server access didn't get me the answer I need. I appreciate this article!


Friday, June 29, 2018 - 11:24:17 AM - Jesse Back To Top (76459)

Thank you for this tip! It was a tremendous help to me a few minutes ago!


Tuesday, May 1, 2018 - 11:37:10 AM - reaver Back To Top (75832)

Hi Aaron, thanks for your quick response.

We were advised that using local domain controller for the remote domain can help, but have not implemented this to remedy the problems yet. I was curious if you had to deal with this problem in the past and if anything worked for you. Obviously the sql server is on the other end of out planet so it is difficult to beat the latency between locations, also DNS was reviewed and updated so all the hosts can resolve each other via forward and reverse zone (sql server, AD server, management laptop).

 

 


Tuesday, May 1, 2018 - 9:50:26 AM - Aaron Bertrand Back To Top (75830)

reaver, if you have slow latency, you have to pay for it somewhere. People typically have to make the hard choice between (a) waiting for SSMS to finally show results and (b) remote desktop and wait for mouse clicks to respond. No magic here except getting on a better network connection.


Tuesday, May 1, 2018 - 6:03:55 AM - reaver Back To Top (75828)

Hi, I am using the runas option with user for the remote domain, however this method is very slow for me. The remote network has sql server and domain controller on the same LAN so communication is very fast between them. However I am on different continent and the latency from my laptop to the remote domain is 300ms. Any action taken on the management studio on the remote databases takes 30s and more to see response.

Thanks

 


Tuesday, March 20, 2018 - 5:45:22 PM - Drew Back To Top (75487)

 Thank you so much for the tip. It worked great for me.

 

 


Monday, March 5, 2018 - 6:53:02 PM - Juan Munoz Back To Top (75350)

Hello,  I need to do this but to an SQL instance.  What would be the syntex for that?

 

This doesn't seem to work:

Internet or Name Address:  MYServer.CCC.IECC.COM\Instance1:1433

user Name: BC\Jblow

Password:Mypassword

 

Thank you in advance.

 

 

 


Tuesday, November 7, 2017 - 5:33:26 AM - Sebastian Back To Top (69344)

 

Hi, 

many thanks for your solution. Works like a charm!

Regards,

Sebastian


Friday, July 14, 2017 - 2:54:24 PM - Aishwariya Coimbatore Mohan Back To Top (59359)

This is the best solution ever. I have tried all the methods out there to make my laptop connect to the management studio, i am wired into company's network and wasnt able to connect. Running the ssms as domian account helped me. 

 


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

 

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

 


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

 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 (45766)

 Nice tip! Thank you!


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

 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 (43561)

Fantastic post!
Thanks Aaron!


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

Thanks very much for wonderful tip! 

 


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

Great tips! Thank you!

 


Tuesday, June 7, 2016 - 1:40:17 PM - Mike Back To Top (41629)

 

Thanks!  Works like a charm.


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

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 (41222)

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


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

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

 


Wednesday, March 2, 2016 - 12:58:43 PM - James Back To Top (40846)

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 (38754)

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 (35295)

Fantastic solution and a life saver. Thanks!!


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

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 (35087)

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


Tuesday, October 7, 2014 - 5:18:10 PM - Vick Back To Top (34872)

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 (34492)

Thanks it helps me a lot!


Tuesday, August 5, 2014 - 10:56:28 AM - Gb Back To Top (34008)

Thank you! This is a great solution!


Monday, July 7, 2014 - 9:06:49 AM - Sanjay Monpara Back To Top (32574)

Hay Aaron , Its wonderful...

Thanks


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

@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 (32197)

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 (32196)

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


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

Great article.  Thank you.


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

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

 















get free sql tips
agree to terms