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
For all who have asked, we made the change on the Sql Server and that fixed our issue.
Make sure you’re not connecting to alias.
Hier ist another solution, which has helped me:
Check your remote domain credentials (Login User and Domain):
cmd>> whoami
cmd>> net config workstation
If they are not equal you have 2 options:
1) Launch SSMS with RUNAS using the remote domain credentials via CMD:
runas /netonly /user:DOMAIN\USER “C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe”
2) Enter the credentials in Windows Credential Manager
This can be done via CMD or UI (Control Panel\User Accounts\Credential Manager\Windows Credentials)
cmdkey /add:”Server:1433″ /user:”DOMAIN\USER” /pass:”PASSWORD!”
I used the:
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
And works very good!!!… Thanks!!!
i did create a DWORD as said here but on the Client machine. It is working fine. But is it safe?
It’s work for me!
Thanks!