troubleshooting Question

Which duplicate SQL spn should I delete?

Avatar of HogRing
HogRing asked on
Microsoft SQL Server
3 Comments2 Solutions316 ViewsLast Modified:
Server 2008 R2, SQL server 2010.  

The event viewer on my domain controller in the system log shows:
Event 11 the KDC encountered duplicate names while processing a Kerberos authentication request.  The duplicate name is MSSQLSvc/server1.contoso.local:1433 (of type DS_SERVICE-PRINCIPAL_NAME).  This may result in authentication failures or downgrades to NTLM.  In order to prevent this from occurring remove the duplicate entries for MSSQLSvc/server1.contoso.local:1433 in Active Directory.

SetSPN -X ran on the SQL server shows that there are 2 groups of duplicate SPNs:
MSSQLSvc/server1.contoso.local is registered on these accounts:
CN=Administrator, CN=Users, DC=contoso, DC=local
CN=Server1, OU=SQL Servers, DC=contoso, DC=local

MSSQLSvc/server1.contoso.local:1433 is registered on these accounts:
CN=Administrator,CN=Users,DC=contoso,DC=local
CN=Server1,OU+SQL Servers,DC=contoso,DC=local

found 2 groups of duplicate SPNs.

I gather that I should have one entry for the server account, plus an entry for the server account plus port 1433 like this:
MSSQLSvc/server1.contoso.local is registered on these accounts:
[
CN=Server1, OU=SQL Servers, DC=contoso, DC=local

MSSQLSvc/server1.contoso.local:1433 is registered on these accounts:
CN=Server1,OU+SQL Servers,DC=contoso,DC=local


or one entry for the administrator user account, plus an entry for the administrator user account plus port 1433 like this:
MSSQLSvc/server1.contoso.local is registered on these accounts:
CN=Administrator, CN=Users, DC=contoso, DC=local

MSSQLSvc/server1.contoso.local:1433 is registered on these accounts:
CN=Administrator,CN=Users,DC=contoso,DC=local

However, I should not have my existing entries that lists both the administrator account and the server account.  The problem is I don't know if I should delete the server account entry or the user account entry.  

In the SQL Server Configuration Manager (Local)>SQL Server Services>SQL Server (MSSQLSERVER) the Log On As column shows Adminstrator@contoso.local.  This makes me think that the SPN I should keep is the listing with the second line that reads CN=Administrator, CN=Users, DC=contoso, DC=local.  
However, there are other services in that detail pane [SQL Server Browser, SQL Server Reporting Services (MSSQLSERVER)] that show NT Authority\LOCALSERVICE and NT Authority\NetworkService respectively in the Log On As column.  I'm not sure if the network service and or local service entries in the Log On As column mean that I need to have the SPN entries for the server account that have the second line that reads "CN=Server1, OU=SQL Servers, DC=contoso, DC=local".

Which of the duplicate SPNs is the record that I need to keep and which do I need to delete?  I've seen posts where someone deleted the wrong one and all hell broke loose.
ASKER CERTIFIED SOLUTION
PadawanDBAOperational DBA

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros