We help IT Professionals succeed at work.
Get Started

Which duplicate SQL spn should I delete?

HogRing
HogRing asked
on
296 Views
Last Modified: 2016-04-25
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.
Comment
Watch Question
Operational DBA
Commented:
This problem has been solved!
Unlock 2 Answers and 3 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant

An Experts Exchange subscription includes unlimited access to online courses.

Get Started
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE