Connect to SQL Server with Windows Authentication in a different domain

Question

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.

Answer 1

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

It is possible that will work with Visual Studio as well, but I really don't know.

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
• try the runas /netonly trick with Visual Studio
• just use the runas /netonly trick with SSMS

Answer 2

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.