107

I am trying to connect to a remote SQL Server on a VPN in a different domain. When I enter the Server name on the SQL Server and choose Additional Connection Parameters to add some extra stuff needed by my school:

Integrated Security=SSPI; User ID=DOMAIN\username; Password=Password

I get the following error:

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

MDCCL
  • 8,520
  • 3
  • 30
  • 61
stergosz
  • 1,195
  • 2
  • 9
  • 7

2 Answers2

148

There is another way, which I now use in preference to the runas /netonly method.

You can add the credentials to your profile in Windows using the Credential Manager found in the Windows control panel.

  1. Open Credential Manager

  2. Click "Add A Windows Credential"

  3. Populate the "internet or network address" field with the name and port number of the SQL instance you wish to store credentials for.

    Example: UniServer:1433 (1433 is the default port, you may need a different port, especially if you are connecting to a named instance)

  4. Populate the "User Name" (don't forget to include the domain e.g. MYDOMAIN\MYUSER)

  5. Populate the "Password"

  6. Click OK

If you have the server name, port and login details correct, you should now be able to use Windows Authentication from most client tools, SSMS, Excel, whatever. They will all use the stored credentials.

Tip: Sometimes you need to use the FQN for the server when adding the credentials. e.g. UniServer.UniDomain.org:1433, it all depends on your network specifics.

Here is a quick demo of the method : http://youtu.be/WiVBPsqB9b4

It is a screen grab of me attempting (and failing) to connect to a SQL Server running in a VM from my desktop, then adding the required credentials and trying again - successfully.

Tip: use the "cmdkey /add" command to script creating and updating stored credentials.

Community
  • 1
Mister Magoo
  • 3,543
  • 1
  • 14
  • 20
  • Interesting! It seems to depend on what your client machine thinks the remote machine is called rather than what it might actually be called. I've put an invented entry ("verysillytest.mwardm") in my host file for the IP address (as those of us accessing from outside of domains are wont to do) and set up the credential in the name "verysillytest.mwardm:1433". I can then connect happily (from a little app called Query Express) using either the hostname or the IP address. – mwardm Oct 28 '16 at 09:04
  • @mwardm yes, that's why I recommend using ping or nslookup as it will tell you the name exactly as you need to use it, including upper / lower case specifics. – Mister Magoo Oct 28 '16 at 09:28
  • Ah, well "ping -a" didn't give me anything and nslookup didn't (immediately) work because I wasn't using the domain's DNS servers. Don't worry, I actually prefer the flexibility of just having to know the IP address (and the login) and being able to make up my own name! – mwardm Oct 28 '16 at 13:49
  • 2
    Awesome, it works! I just wonder - is it supposed to work like that or is it a bug or a design fail on the side of SQL Server? Microsoft or people from Microsoft advertise everywhere that you have to have a domain connected machine, otherwise there is no way to connect to SQL Server which is set up to accept only the domain credentials. – David Ferenczy Rogožan Nov 16 '16 at 18:03
  • In my case a port was necessary. – rotman Jan 24 '17 at 14:20
  • Mine needed an FQDN, host name and port is not enough. Thanks. – Victor Zakharov Aug 28 '18 at 20:42
  • 3
    Just a minor thing to be aware of. You don't put the instance name in the Credential Manager. Initially I tried to add it like server\SQL2019,[port]. This didn't work. It just needs [servername]:[port] – user1751825 Jun 18 '20 at 14:25
  • Really nice, it does work, but in my case (W10 on client, SSMS17, W2008R2 on server and SQL2008, two domains with a one-way TCP connection) apparently SSMS tried to first connect with native credentials (source account), probably lags on reverse verification or something server-side (TBD) and only then tries to send correct credentials and finally gets authenticated, then each SQL query repeats the cycle as the connection gets dropped all the time. Probably a something to report to Microsoft, but... just in case someone else also has such weird local conditions. – Vesper May 24 '21 at 13:18
  • 1
    On my machine, SSMS returns "Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication. (Microsoft SQL Server, Error: 18452)". This error doesn't appear when using \netonly – Stevoisiak Aug 17 '21 at 16:39
  • @Stevoisiak The only time I have seen that is when you got something wrong with the configuration of the credentials in Credential Manager, so it didn't actually use the one you wanted. – Mister Magoo Aug 18 '21 at 23:20
103

You are attempting to pass Windows credentials in plain text from the connection string of an application. This simply isn't how Windows authentication works, and largely defeats the purpose.

You also can't just create the same username with the same password in your own domain, and expect that to magically work. Domain name is still part of the validation - your machine either has to be part of the domain, or the domain your machine is in must be trusted by the school's domain.

The only workaround I know of is for SSMS (and it works for other apps too, like Plan Explorer and SentryOne), and that's the runas /netonly trick described in this answer. This fools Windows into launching SSMS as the login you specify, rather than your own (this isn't something you can set in the Connection properties dialog of SSMS, it's how you need to launch SSMS from the command line or a shortcut):

runas /netonly /user:domain\username "C:\path_to\ssms.exe"

This will prompt you for your password in the remote domain. It will look like it is using your local Windows credentials, but it is not.

This should work with any application, including Visual Studio.

So your options are:

  • have the university allow you to join your machine to the domain
  • have the university add your domain as a trusted domain
  • have a jump box inside the VPN that allows you to RDP and use tools connecting directly to the SQL Server machine
  • use SQL authentication
  • use the runas /netonly trick with SSMS or Visual Studio
Aaron Bertrand
  • 180,303
  • 28
  • 400
  • 614
  • See https://serverfault.com/q/784616/58957 if you are tired of typing the password every time you run runas /netonly or need to use it from a batch script. – Nickolay Jan 23 '20 at 22:13
  • This however does NOT work if the source system is Win10 (maybe 8.1 or 7 would apply too), the target system is Windows Server 2003R2 (have to run such legacy) with SQL Server 2005 as target application, and possibly the fact that source system is in a domain also played a role. The problem is that WS2003 reports successful login, while SQL server reports untrusted connection. Some legacy issue I admit, but should others come by this answer, expect problems with legacy OSes and software that relies on some internal credential transfer. – Vesper Jun 08 '21 at 08:36
  • Thanks, the runas option worked for me on my nondomain workstation – Taavi Sep 01 '21 at 10:55
  • I was having the same issue so I opened Visual Studio with run as different domain user & kept connection string with Integrated Scurity = True so it took correct credentials & execute the program successfully. But that program was writing a file & as a result it created a user with Username.DomainName where it wrote the output file, but then I didn't have access to that folder. – Harshil Jul 06 '22 at 12:39