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

27 Comments. Leave new

  • For all who have asked, we made the change on the Sql Server and that fixed our issue.

    Reply

Leave a Reply

Menu