Which duplicate SQL spn should I delete?

HogRing
HogRing used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Operational DBA
Commented:
You need to have the SPN registered for <server>.<fqdn> and <server>.<fqdn>:1433 for the account under whose context SQL Server DB engine is running (SSRS would need SPNs too if you modified the config file for Kerberos authentication, but neither here nor there).  You'll probably want to make sure that you check the delegation settings for the service account as well.  You can use the following to double check your work: https://www.microsoft.com/en-us/download/details.aspx?id=39046. (or do the work for you if you're feeling really squirrelly)

Author

Commented:
I went to services.msc and looked at "SQL Server (MSSQLSERVER)", the Log On As column lists administrator@contoso.local.  Is that how you check the account under whose context SQL Server DB engine is running?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
Is that how you check the account under whose context SQL Server DB engine is running?
Yes, it is. You should register the SPN with the account that's running the SQL Server service. All the rest of entries can be deleted.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial