Solved

Removing duplicate SPNs (Service Name Providers)

Posted on 2010-08-24
13
19,887 Views
Last Modified: 2012-05-10
I currently get errors in the system log on my Windows 2008 R2 domain controller daily, such as:
Event ID: 11
Source Name: KDC
The KDC encountered duplicate names while processing a Kerberos authentication request. The duplicate name is MSSQLSvc/IKSDB01.iks.bz:1139 (of type DS_SERVICE_PRINCIPAL_NAME). This may result in authentication failures or downgrades to NTLM. In order to prevent this from occuring remove the duplicate entries for MSSQLSvc/IKSDB01.iks.bz:1139 in Active Directory.

I googled this and found an article that makes the fix seem fairly straighforward:
http://technet.microsoft.com/en-us/library/cc733945(WS.10).aspx

Here is my output when I run: setspn -X
Checking domain DC=iks,DC=bz
Processing entry 2
MSSQLSvc/iksdb01.iks.bz:3064 is registered on these accounts:
        CN=IKSDB01,CN=Computers,DC=iks,DC=bz
        CN=Administrator,CN=Users,DC=iks,DC=bz

MSSQLSvc/iksdb01.iks.bz:3964 is registered on these accounts:
        CN=IKSDB01,CN=Computers,DC=iks,DC=bz
        CN=Administrator,CN=Users,DC=iks,DC=bz

MSSQLSvc/iksdb01.iks.bz:1139 is registered on these accounts:
        CN=IKSDB01,CN=Computers,DC=iks,DC=bz
        CN=Administrator,CN=Users,DC=iks,DC=bz

MSSQLSvc/iksdb01.iks.bz:4929 is registered on these accounts:
        CN=IKSDB01,CN=Computers,DC=iks,DC=bz
        CN=Administrator,CN=Users,DC=iks,DC=bz

MSSQLSvc/2000sql01.iks.bz:1407 is registered on these accounts:
        CN=2000SQL01,CN=Computers,DC=iks,DC=bz
        CN=Administrator,CN=Users,DC=iks,DC=bz

MSSQLSvc/SHAREPOINT.iks.bz:1433 is registered on these accounts:
        CN=SHAREPOINT,CN=Computers,DC=iks,DC=bz
        CN=Administrator,CN=Users,DC=iks,DC=bz

found 6 groups of duplicate SPNs.


The article then gives instructions using setspn -D<SPN> <computer_name> to delete these.

Some questions:
#1. Given the output of my duplicate SPNs, what would be command that I type to erase one of these.
#2.  How do I know which duplicate to erase?
#3.  If in doing this, I screw something up, is there a way to undo?

Thanks,
Jamie
0
Comment
Question by:jamorlando
  • 6
  • 5
13 Comments
 
LVL 10

Expert Comment

by:dhruvarajp
ID: 33512456
here you go
http://technet.microsoft.com/en-us/library/cc786325(WS.10).aspx

you can add delete using instructions in this article

Thank you
0
 
LVL 57

Accepted Solution

by:
Mike Kline earned 500 total points
ID: 33512636
So the output there gives you the computer name and dupliate SPNs.  Use the procedures Marcin described here to find the one you need to delete
http://social.technet.microsoft.com/Forums/en-US/winserverDS/thread/ba6a67c2-ee45-4dcc-9ce4-fb6ebceb1c2a/
Thanks
Mike
0
 

Author Comment

by:jamorlando
ID: 33513277
For simplicity, lets use MSSQLSvc/iksdb01.iks.bz:1139 as an example.
I opened up AdsiEdit and was able to find this SPN under the ServicePrincipleName section of:
DC=iks,DC=bz -> CN=Computers -> CN=IKSDB01
DC=iks,DC=bz -> CN=Users -> CN=Administrator

IKSDB01 is one of our old servers running MS SQL SERVER 2000.
I have no problem with deleting one of the SPNs, and now know how to.  Thank you!

I am still unsure as to which one to delete...
In the article he says:
Locate the westpex computer and find out what's the account in which secuirty context SQL Server service executes. If this is Administrator's account, delete the second one - otherwise (if this is a local System or Network Service account, delete the first one).

Where would I find this in SQL Server?  Or would I locate this in services.msc?
From services.msc, if I look at the "Log In As" field under the various database instances, some of them have different values ... some say "IKS\Administrator" (our domain admin login), and some say "Local System".  Is this what I'm looking for?

How will I be able to tell what MSSQLSvc/iksdb01.iks.bz:1139 corresponds with in this list.
Thanks!
0
 
LVL 57

Expert Comment

by:Mike Kline
ID: 33513538
some say "IKS\Administrator" (our domain admin login), and some say "Local System".  Is this what I'm looking for?
Yes so administrator is what is used.
Take a look at part 2 of this DS team series
http://blogs.technet.com/b/askds/archive/2008/06/09/kerberos-authentication-problems-service-principal-name-spn-issues-part-2.aspx
about halfway down they use  queryspn.vbs and go through the process
Thanks
Mike

0
 
LVL 57

Expert Comment

by:Mike Kline
ID: 33513573
Can't believe I forgot to mention Joes great entry  http://blog.joeware.net/2008/07/17/1407/
0
 

Author Comment

by:jamorlando
ID: 33513674
@MKLine71, thank you!  I think I need to explain it more ... I've attached a screenshot of the SQL Server section of services.msc ... you can see all the different database instances.  Some are running under IKS\Administrator and some under Local System.

How am I to know which database instance corresponds to, for example: MSSQLSvc/iksdb01.iks.bz:1139 ?  Any SQL Server experts on here? :)

SQLServer2000.jpg
0
 

Author Comment

by:jamorlando
ID: 33514465
Ok, probably a pretty clunky way of doing this but I figured out a way to do this:

Using MSSQLSvc/iksdb01.iks.bz:1139 as an example:
From the command line, I run:
netstat -ano | findstr 1139
It returns: TCP    0.0.0.0:1139           0.0.0.0:0              LISTENING       1592

(1592 is the PID process ID?)

From the registry, I navigate to:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server

The folders under here are all my database instances .. then I would navigate to the MSSQLServer folder underneath, and look for the "uptime_pid" key which is 1592.

So now I know that MSSQLSvc/iksdb01.iks.bz:1139 corresponds to my MSSQL$SQL2000SMARTSEAL service.

0
 

Author Comment

by:jamorlando
ID: 33514507
One last question .... if for SOME reason, I delete the WRONG SPN, how do I recover from this?

I assume that I could just change the authentication from within SQL Server, or just unjoin the server from the domain and rejoin it.  Some reassurance would be great before I start deleting stuff with ADSIEdit :)
0
 
LVL 57

Assisted Solution

by:Mike Kline
Mike Kline earned 500 total points
ID: 33514996
You can register the SPN again

http://support.microsoft.com/kb/909801/en-us

*** from the article

You can use a command that is similar to the following to register an SPN for an instance:
SetSPN –A MSSQLSvc/.:1433

Note If an SPN already exists, you must delete the SPN before you can reregister it. You may have to do this if the account mapping has changed. To deleted an existing SPN, you can use the SetSPN.exe tool together with the -D switch.
0
 

Author Comment

by:jamorlando
ID: 33515043
Awesome ... all my questions have been answered and I feel like I actually comprehend this now. THANKS!
0
 
LVL 57

Expert Comment

by:Mike Kline
ID: 33515090
thanks...if you don't mind respond and let us know how things went when you are done.
0
 

Author Comment

by:jamorlando
ID: 33515867
I tried it for the first one in my list ... deleted the duplicate SPN, and am successfully able to get to the database.  I'll leave it like that for a couple days just to make sure nobody's affective and then I'll knock out all the other ones, going with Sharepoint LAST because that's our most important server here :)
0

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Find out how to use Active Directory data for email signature management in Microsoft Exchange and Office 365.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now