Dancing with the SSPI Devil

"Cannot Generate SSPI Context"

It's the DEVIL! Your app can't authenticate to SQL Server because... Authentication, Active Directory, Kerberos and SPNs... and Delegation ...and DNS ...and too many answers on Google!

The answer is in there somewhere, but where??

SSPI

Let's start at the (relative) beginning...

SSPI - Security Support Provider Interface

It's the common front to several Security Support Providers (SSPs)

• NTLM
• Kerberos
• CredSSP
• TLS/SSL
• And Others

You're probably reading this because you're trying to open a connection from an application to a SQL server, you're application is using Integrated Security and it's not working.

In this case, Windows will try to connect with the Kerberos Authentication Provider first.

You can bypass Kerberos, but why? Let's get Kerberos working.

How Kerberos Works

• Your SQL Client looks up the SQL Server's FQDN with forward and reverse DNS lookups
• Your SQL Client builds an SPN (Service Principal Name) from this detail
  • MSSQLSvc/<FQDN>:<Port>
  • MSSQLSvc/MySQLServer.MyNetwork.com:1433
• The SPN is passed to Active Directory, asking for a matching AD principal object (Computer Account or User Account)
• If AD matches the SPN to a principal object it issues an authentication token and passes that back to the client
• The client passes the token to the SQL server who double checks with AD that it is valid
• If all that works, you have a connection and you can login

Check DNS

Still not Working?

Check AD

Once your SQL Client has resolved a valid FQDN and built an SPN (Service Principal Name) string, it is sent to AD to find a match.

Matching SPNs are stored in the Active Directory object of the login used in the SQL Server service.

• User Object - Where the SQL Server service uses a Domain Account
• Computer Object - Where the SQL Server service uses 'Local System'

When SQL Server starts, it tries to write it's own SPNs into AD. There's a good chance your SQL Server Service account doesn't have Domain Admin priviledges (I hope it doesn't) so this will fail and your SPNs will be missing by default.

You can manually check if the correct SPNs exist with the following PowerShell command:

Get-ADComputer MySQLServer -Properties ServicePrincipalNames | Select -ExpandProperty ServicePrincipalNames

You should get the following back:

• MSSQLSvc/MySQLServer.MyNetwork.com:1433

Where the line starts 'MSSQLSvc' and 'MySQLServer.MyNetwork.com' exactly matches the server name you got back from 'ping -a'.

You can safely ignore any records that don't start 'MSSQLSvc', your SQL Client will too.

If you have no 'MSSQLSvc' SPNs returned from your Get-ADComputer command, or they are incorrect, you can alter the recorded SPNs with the following PowerShell commands:

Set-ADComputer -ServicePrincipalNames @{Add='MSSQLSvc/MySQLServer.MyNetwork.com:1433'}

Set-ADComputer -ServicePrincipalNames @{Remove='MSSQLSvc/SQLServer1:80'}

Valid SPN Formats

Add SPNs to AD Objects Automatically

If you're scared of adding and removing SPNs manually in AD, you can run the Microsoft 'Kerberos Configuration Manager' Tool to automate the process.

You have to run it on your SQL server, as Administrator.

If it fails to launch, check that you don't have any unresolvable users in your local 'Administrators' group.

• i.e. they appear as a SID rather than a username

Static Ports

For a TCP connection, your SQL Client will include either of the following in the SPN it sends to AD:

• The TCP Port discovered from the SQL Browser service
• Port 1433 for a Default Instance

You will need to fix the TCP ports your SQL Named Instances use, so the SPNs registered in AD remain valid.

Time

The Kerberos protocol uses timestamps in its communications to secure itself from replay attacks.

By default, a Windows Domain will have a policy set to allow a maximum deviation of 5 minutes. If your client, SQL or AD are more than 5 minutes out, Kerberos authentication will fail.

If you're seeing intermittent Kerberos Authentication failure, check the time on each of your Active Directory Domain Controllers.

Check if anyone has changed or overridden your Default Domain Policy here:

• Computer Configuration\Windows Settings\Security Settings\Account Policies\Kerberos Policy\Maximum tolerance for computer clock synchronization

Check Your Connections

The final piece of the puzzle is to check you can now connect with Kerberos Authentication.

Make your connection and login with your original Connection String. Check in SQL to see if your connection successfully negotiated:

• SELECT s.login_name, c.auth_scheme, s.host_name
  FROM sys.dm_exec_connections c
  JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id;

All done

I hope that helps!