Resolve SQL Server error “Cannot generate SSPI context” in a Kerberos environment

Recently I came across following error when I tried to create an ODBC connection to one of my SQL Servers using TCP/IP.

The SQL server belongs to a SharePoint farm which is configured with Kerberos authentication. The same SQL server was initially configured with local system account and later reconfigured with a domain account (e.g trs\SQL_Admin).

Logs showed that there is an issue with SPN records

Following are the steps I followed to resolve the issue

• Check existing SPN records for the service account

setspn –l trs\sql_admin

It lists all SPN records registered. However it did not contain an entry for

mssqlsvc/TRS-AUS1-AS-01.trs.local:1433

• Add missing SPN record using following command

setspn -a mssqlsvc/TRS-AUS1-AS-01.trs.local:1433 trs\sql_admin

Unfortunately I received an error saying that there is a duplicate record. Let’s investigate that

• Execute following command to find out any SPN records for that particular server

setspn -l TRS-AUS1-AS-01

It had a record with same signature. May be it was created for my local service account when SQL server was initially configured

• We need to remove those SPN entries. To remove them use following commands

SetSpn -d MSSQLSvc/TRS-AUS1-AS-01.TRS.local:1433 TRS-AUS1-AS-01
SetSpn -d MSSQLSvc/TRS-AUS1-AS-01.TRS.local TRS-AUS1-AS-01

• Then we need to add new SPN entries with our service account

setspn -a mssqlsvc/TRS-AUS1-AS-01:1433 trs\sql_admin
setspn -a mssqlsvc/TRS-AUS1-AS-01.trs.local:1433 trs\sql_admin

• You can verify the delegation through Active Directory Users and Computers

That’s all we have to do. Now I can create an ODBC connection using TCP/IP