0

I am getting below error while trying to connect SQL from remote server. Local login is working fine. Can anyone help on this. Thanks in advance.

"Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: 10.186.12.10]"

My environment :

Client - already test AD/DNS domain joined

Remote SQL Server - already prod AD/DNS domain joined

I have running inside test AD/DNS server isolated environment. No relationship between test AD/DNS server and prod AD/DNS server. Test domain name : contoso.com Otherwise , PROD domain name : contoso.com

Test AD/DNS Server : 10.190.10.1 Prod AD/DNS Server : 10.150.10.1

Do I have to join prod AD/DNS client machine as well ? I mean , is there any workaround solution ?

Thanks in advance,

Last Update:

Solved my issue.

3
  • Where / when are you receiving this error?... When you try to login from SSMS or is the error being thrown elsewhere like a linked server connection?
    – J.D.
    Feb 16 at 14:20
  • sorry , no linked connection I have been using SSMS also , I am conneccting from client machine to remote sql server.
    – Cell-o
    Feb 16 at 14:31
  • You need to create a trust relationship between the two ADs for this to work. You don't need to move the machines between domains. Feb 16 at 22:03
0

If you're just using SSMS then you can either launch it using the remote domain credentials via CMD, updating the path of your SSMS install

c:\windows\system32\runas /netonly /user:Doamin\ExampleUser "C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe" 

Or you can enter the credentials in Windows Credential Manager (Can be done through UI or CMD)

cmdkey /add:"Server:1433" /user:"Domain\ExampleUser" /pass:"P45sW0rD!" 
0

I believe the issue is the client machine's domain isn't trusted by the remote server when logging in via Windows Authentication in SSMS. You should still be able to login with your AD credentials from your local SSMS client by switching the Authentication mode from Windows Authentication to Azure Active Directory - Password.

If you run into the subsequent error "The certificate chain was issued by an authority that is not trusted" then you can tell SSMS to trust it manually by clicking the "Options" button on the Login window. Then under the "Connection Properties" tab, check off the "Trust server certificate" checkbox. Then click the "Connect" button.

Screenshot Example

7
  • Login failure has nothing to do with SSL/TLS certificates (for which you would get a connection/handshake failure), and here it has everything to do with a lack of trust between the two ADs Feb 16 at 22:01
  • @Charlieface Disagree on this one, I've ran into this error before and the solution I just posted works. I can replicate it right now by connecting to my work VPN, and trying to connect to the remote SQL server instance in SSMS locally on my machine. I receive the exact same error message as OP and checking off "Trust server certificate" fixes it and allows me to connect. (Not very intuitive error though, I'll agree.)
    – J.D.
    Feb 16 at 22:05
  • But that is a separate problem. Here, there is no way it would ever work even if using offical CA certificates and a proper hostname, because the Kerberos ticket is not trusted. Feb 16 at 22:07
  • @Charlieface Idk what else I can say, I get the same error, the above solution works for me. I'll update my answer with proof when I get a chance.
    – J.D.
    Feb 16 at 22:52
  • @Charlieface Sorry I mispoke, I just tested it and have updated my answer. When I've encountered this issue, for me, it's always been a two part problem. I've updated my answer with what works for me in this case, without having to setup a trust relationship between the two domains.
    – J.D.
    Feb 16 at 23:14
-2

When the client tries to connect, it will pass its existing Kerberos ticket, the one created from it's Domain Controller on Test AD when the user logged in.

SQL Server will pass the ticket to it's own DC, which will fail it. Since the domain of Production has no trust relationship with Test domain, there is no way for the DC to trust what this ticket represents.

To get this to work without moving machines between domains, you need to setup a trust relationship between the two AD domains. Then, the Production DC can verify the ticket with the other DC.

There are many ways to create trusts. Importantly, they can be one-way, so you don't have to have both domains trusting each other. You can also setup a Forest Trust, which can be easier to manage.

The two DCs need to be able to talk to each other, so you may need to open ports and setup DNS records.

I suggest you speak to your Sysadmin or whoever runs your AD domain, if any.

Not the answer you're looking for? Browse other questions tagged or ask your own question.