SQL SERVER – Login Failed. The Login is From an Untrusted Domain and Cannot be Used with Windows Authentication

In SQL Server, there are error few messages which can be caused due to multiple reasons. One of the famous example is “Login Failed for User” error message. If you have seen this earlier, you would know that this message can come due to incorrect user name, incorrect password, incorrect database and many more other reasons. Whenever I see login failed, I look at SQL Server ERRORLOG to see the exact cause.

Here is one of the error where I was not clear about the cause.

SQL SERVER - Login Failed. The Login is From an Untrusted Domain and Cannot be Used with Windows Authentication login-untrusted-01-800x157

Here were the messages in ERRORLOG.

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: 169.111.227.120] SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. The logon attempt failed [CLIENT: 169.111.227.120]

This was coming from the application server which was same domain. Based on my search on the internet, there is some kind of loopback, check taking place which causes trusted connections via the loopback adapter to fail.

WORKAROUND/SOLUTION

Loopback check can be removed by adding a registry entry as follows:

  • Edit the registry using regedit. (Start –> Run > Regedit )
  • Navigate to: HKLM\System\CurrentControlSet\Control\LSA
  • Add a DWORD value called “DisableLoopbackCheck”
  • Set this value to 1

If above doesn’t solve the issue, we need to create SPNs for SQL Service account. While searching for SETSPN.exe command I came across this nice tool which can help a lot of pain like syntax error etc. is called as “Microsoft® Kerberos Configuration Manager for SQL Server®” which can be downloaded from https://www.microsoft.com/en-us/download/details.aspx?id=39046

The best piece about this tool is that it can help in finding missing SPN and provide script to run or fix it directly, if you have permission. Basically, it can

  • Gather information on OS and Microsoft SQL Server instances installed on a server.
  • Report on all SPN and delegation configurations on the server.
  • Identify potential problems in SPNs and delegations.
  • Fix potential SPN problems.

Have you used this tool earlier?

Reference: Pinal Dave (http://blog.SQLAuthority.com)

, , , ,
Previous Post
SQL SERVER – OS Error 13 – The Data is Invalid OS Error 87 – The Parameter is Incorrect – Various Errors due to Faulty Media or Hardware
Next Post
SQL SERVER – Msg 3035, Level 16 – Cannot Perform a Differential Backup for Database “SQLAuthority”, Because a Current Database Backup Does not Exist

Related Posts

26 Comments. Leave new

  • mohanchhapadia
    July 10, 2017 3:09 pm

    Hi Pinal,

    This Workaround/Solution has to be implemented on the Application Server or the SQL Server?

    Many thanks,
    Mohan

    Reply
  • Adele Davison
    July 21, 2017 7:45 pm

    Hello – just wanted to say thanks for this. Setting the registry key fixed the issue for me. Best wishes, Adele

    Reply
  • it worked thanks

    Reply
  • shankar kulkarni
    January 25, 2018 9:02 am

    This Workaround/Solution has to be implemented on the Application Server or the SQL Server?

    Reply
    • 1. SPNs are for the SQL server in domain controller..
      2. Registry change was done when IIS was running on the same machine. You can try both and let us know.

      Reply
  • I have followed the regEdit, but not worked. I have installed Kerberos Configuration Manager, but not sure what configuration I have to do in SQL Server. Please advise.

    Reply
  • Not a very clear post. Several people have asked the same question which is…. the registry changes you’re suggesting, are they made on the SQL Server machine or on the client connecting to this server?

    Reply
  • This helped me fix an issue where I was using Entity Framework and was trying to use Windows Authentication on the same server as my SQL server instance.

    Thanks!

    Reply
  • The exact error message referenced in your article suddenly showed up in my environment, I discovered it was due to the “Netlogon” service being disabled after a MS Windows Server 2016 update. Changing the service to “Automatic” and restarting the service were the steps I took to resolve this issue. I hope this additional information could be useful for anyone who encounters this same error message.

    Reply
  • in which Key(HKEY..) we will see HKLM folder?

    Reply
  • Awesome..Thanks for the workaround

    Reply
  • Thanks a lot for the work around. It worked for me.

    Reply
  • it has fixed my issue

    Reply
  • You, sir are a legend!

    Reply
  • its working thanks for your support

    Reply
  • What if the connection is going to a different domain where we have a one way trust
    Would this registry hack still work or they have to be on the same domain?
    Or I would have to do the SPN

    Reply
  • Joe Piontkowski
    January 16, 2020 4:42 am

    I had this problem this week. I read dozens of solutions and none of them worked. So here is a new answer that I haven’t seen anywhere else on the internet.

    NTLM authentication is used by local user accounts (non-domain accts). If you use a local user acct to connect to SQL, SQL will use NTLM to authenticate back to the originating server.

    For security reasons, many organizations have required that only NTLMv2 is used, never NTLM.

    SO…if the request is coming from a server that is using NTLMv1 and the SQL server is only accepting NTLMv2, you’ll get this error (untrusted domain). The solution is to make sure your devices are all using NTLMv2 authentication.

    HKLM\SYSTEM\CurrentControlSet\Control\Lsa\LMCompatibiltyLevel DWORD 1

    Level 0 – Send LM response and NTLM response; never use NTLMv2 session
    security
    Level 1 – Use NTLMv2 session security if negotiated
    Level 2 – Send NTLM authenication only
    Level 3 – Send NTLMv2 authentication only
    Level 4 – DC refuses LM authentication
    Level 5 – DC refuses LM and NTLM authentication (accepts only NTLMv2)

    Reply
  • Here was…

    HKEY_LOCAL_MACHINE instead HKLM…

    Nice!

    Reply
  • Ok more than few have asked where the registry change needs to be made on APP or SQL? is it a secret that no one answered?

    Reply
  • In our case one of the guys had ran some sharepoint powershell command and left the shell as is while he changed the password for his account later. That caused the logon failures for the open sharepoint shell. Once the person closed the shell and logged off the server the errors went away. Had the logon failures flood the sql logs every few seconds until the session was closed for that user.

    Reply
  • thanks so much. Saved us hours of work.

    Reply

Leave a Reply

Menu