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.
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)
32 Comments. Leave new
Hi Pinal,
This Workaround/Solution has to be implemented on the Application Server or the SQL Server?
Many thanks,
Mohan
Hello – just wanted to say thanks for this. Setting the registry key fixed the issue for me. Best wishes, Adele
Hello Adele/Pinal
The registry changes are to be made on Application Server or the SQL Server?
it worked thanks
Thanks for confirmation.
This Workaround/Solution has to be implemented on the Application Server or the SQL Server?
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.
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.
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?
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!
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.
in which Key(HKEY..) we will see HKLM folder?
Awesome..Thanks for the workaround
Thanks a lot for the work around. It worked for me.
it has fixed my issue
You, sir are a legend!
its working thanks for your support
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
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)
Here was…
HKEY_LOCAL_MACHINE instead HKLM…
Nice!
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?
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.
thanks so much. Saved us hours of work.