Part I of this series helped us in understanding how windows authentication works. Though the process looks smooth but failure can happen at any stage during the process. Here I am explaining few commonly seen scenarios which causes SSPI authentication failure, and their resolutions.
Part-2: Why this authentication fails and how to troubleshoot these issues.
Scenario #1: Client cannot resolve FQDN to SQL Server to build proper SPN due to DNS resolution.
Client forms fully qualified domain name (FQDN) by using DNS name of target SQL Server. If it fails, or only returns the NetBios name, then client cannot properly form the FQDN. FQDN is needed to form a proper SPN (Service Principal Name) for Kerberos which is then passed to InitializeSecurityContext on the client side.
The key factor for this is how DNS is configured on these servers. You can verify this on client and server by using ping command on command prompt. On the client computer run ping command
> ping –a IP address
>Ping –a sqlserver1
Or you can try nslookp for hostname and ipaddress.
You need input FQDN and IP address at least twice in nslookup as you may see different results if there are more than one entry for the IP or FQDN.
- If you find any mismatch between servers’s IP and FQDN, that could be the cause of your connectivity issue.
- There could be one more reason if you someone has manually created entry on entry in hosts file (c:\WINDOWS\system32\drivers\etc\hosts) on the client machine and forgot to remove it.
Scenario #2: SPN Found in AD, but Not Configured Properly
SPN is associated with either a domain account or a machine account. When SQL Server service starts under LocalSystem account, it automatically creates an SPN for machine account but when SQL Server starts under a domain account, it tries to create the SPN for service account but this usually fails in both the cases because the domain account and the local account do not have the right to set their own SPNs. In that case you will have to create the SPNs manually.
If SPN found in AD, authentication will always go for Kerberos only it won’t “fall back” to NTLM. If SPN is found in AD, but is not configured for same account that was used to start SQL Server in this case InitializeSecurityContext return SEC_E_WRONG_PRINCIPAL (The target principal name is incorrect). This is most common cause of “Cannot generate SSPI context” error
To resolve this error you need to find the existing SPN and delete the miss configured SPN and recreate new one this can be done by using setspn.exe from command line with following Switches.
-R = reset HOST ServicePrincipalName
Usage: setspn -R computername
-A = add arbitrary SPN
Usage: setspn -A SPN computername
-D = delete arbitrary SPN
Usage: setspn -D SPN computername
-L = list registered SPNs
Usage: setspn [-L] computername
Once you find the SPN you can use command setspn –D <SPN> <Account> to delete the duplicate SPN as mentioned in the kb
There is one special scenario which I would like to bring to your notice.
When SQL Server runs under LocalSystem it creates SPN on machine name instead of service account now suppose due to some reason SQL Server crashed and you now decide to run the SQL Server from service account at that time when you try to login to SQL Server it may also fail with error “Cannot generate SSPI context“
When SQL server ran under Local System, it had successfully registered the Service Principal Name (SPN) for the service. The SPN is kept in the Active Directory and should be de-registered when the server is shutdown. Since this SQL Server got crashed, SQL server failed to de-register the SPN. When the client connects to the server using TCP, it can find the SPN in the Active Directory and Kerberos will be used to perform the security delegation. However, it finds the SPN configuration is not correct, and Kerberos will fail.
To resolve this issue also we can use the same approach of using setspn.exe and delete and recreate correct SPN.
Scenario #3: SPN Not Found, But We Need Kerberos (e.g. double hope)
Client uses SPN to identify the target SQL Server in AD. Active Directory uses SPN to securely identify a network resource to allow mutual authentication but if client creates invalid SPN, SPN will not be found in Active Directory, Kerberos will not be used, NTLM will be used with SSPI. If we needed Kerberos for double hop SSPI will end up passing over user token NT Authority\Anonymous Login on the other side this is one cause of error “Login failed for user ‘NT Authority\Anonymous Logon‘”
Kerberos Double Hop and Issues
Kerberos Double Hop is method used by most of the web based application in which client’s Kerberos authentication credentials are maintained over two or more connections. By using double hope you retain user’s credentials and act on behalf of the user in further connections to other servers.
Steps to explain how double hop works are as follows:
- Client connects to application by providing windows credentials and domain controller returns a Kerberos token to the client.
- Client uses this token to request a service ticket to connect to Middle Tier Server (e.g. IIS).
- Client connects to IIS and provides both token and service ticket.
- IIS uses the clients details (Token and service ticket) so IIS can connect to SQL Server.
- IIS connects to SQL Server using the client’s credentials.
To work this successful there are few setting needs to be enabled, below diagram shows how the double hope works and what configurations are useful. If you want to know more about how double hop works in detail you can go through this excellent blog.
If double hop is not configured in AD, and you try to double hop a user token, you end up with the ‘Login failed for user ‘NT Authority\Anonymous Logon‘ token on the other side.
To resolve this issue you need to make sure following 2 settings are configured properly.
1- User account you want to delegate must not have “Account is sensitive and cannot be delegated” AD property set.
2- Machine that is performing hop must have “Trust Computer For Delegation” set.
Scenario #4: User Account not found on target server
SSPI may work perfectly, and transfer over correct user token to SQL Server, but the target machine cannot find the account.
SQL Server calls LookupAccountSID API to get account domain and user name given the user token’s SID If this fails, we get “Login failed for user ‘null’“
Scenario #5: User Is Member of Too Many Groups
If user is part of multiple groups this can cause Kerberos SSPI toker to be bigger and SQL Server driver could not anticipate such a big token. In such cases you will get error “Communication link failure“
Refer to this kb for resolution.
Hope you like this series!!
———————- Error message when you use a Windows Server 2003-based domain controller to join a Windows XP-based client computer to a domain: “Not enough storage is available to complete this operation”
You may not be able to connect to a SQL Server that is running on a Windows Server 2003 computer by using Windows authentication
“Cannot generate SSPI context” error message, when connect to local SQL Server outside domain
“Cannot Generate SSPI Context” error message, more comments for SQL Server
“Understanding Kerberos Double Hop”
Get “Cannot generate SSPI context” error
SQL Server 2008 connectivity issue : cannot generate SSPI context