Need help removing duplicate SPN

Discussion:

(too old to reply)

Gunnertac

2010-01-05 21:59:01 UTC

I am getting Event ID 11 errors indicating a duplicate SPN for MSSQL Service.
I used the TechNete script queryspn and it appears that instead of having two
computers with the same SPN I have one user and one computer with the same
registered SPN. The entries I see are:

CN=SQLSA,OU=Virtual,OU=Admins,OU=Accounts,OU=Bellevue,DC=MYDOMAIN,DC=LOCAL
Class: user
User Logon: SQLSA
-- MSSQLSvc/MYSERVER.MYDOMAIN.LOCAL:1433

CN=MYSERVER,OU=Non-Domain
Controllers,OU=Servers,OU=Computers,OU=Bellevue,DC=MYDOMAIN,DC=LOCAL
Class: computer
Computer DNS: MYSERVER.MYDOMAIN.LOCAL
-- MSSQLSvc/MYSERVER.MYDOMAIN.LOCAL:1433
-- HOST/MYSERVER
-- HOST/MYSERVER.MYDOMAIN.LOCAL

I have other SQL Servers that look identical to the computer entry that
don't have their MSSQL SPN duplicated in the User SQLSA so I'm guessing I
should delete the entry from the user using ADSIEDIT to clear the error. Is
that correct? I don't see two computers with that SPN.

While I'm here I have another question. We just upgraded a server from SQL
2005 STD to SQL 2008 ENT. Its MSSQL SPN entries are:

CN=MYSERVER2,OU=Non-Domain
Controllers,OU=Servers,OU=Computers,OU=Bellevue,DC=MYDOMAIN,DC=LOCAL
Class: computer
Computer DNS: MYSERVER2.MYDOMAIN.LOCAL
-- MSSQLSvc/MYSERVER2.MYDOMAIN.LOCAL
-- MSSQLSvc/MYSERVER2.MYDOMAIN.LOCAL:1433
-- HOST/MYSERVER2
-- HOST/MYSERVER2.MYDOMAIN.LOCAL

Is this server not creating a duplicate SPN error because one is appended
with :1443? I have other SQL servers and their entries all are appended with
:1443. They are all SQL 2005 boxes. Should I delete the appended SPN from AD
or should I leave it alone?

--
Tim

Jorge Silva

2010-01-05 23:52:11 UTC

Permalink

Hi
this problem occurs when two or more accounts have the same service
principal name (SPN) registered. This is not allowed and the KDC will
"scream" when this issue is detected. You need to detect where the
duplication exists (use the setspn tool), and remove the duplication that Is
not needed. One thing that you need to understand is why that SPN is need
and by which service/account is required. For instance, for SQL you may need
to set the SPN for a given account that is running under SQL service. If
you're sure that changing that SPN will not break other configurations (that
may be set incorrectly using the wrong SPN), you may delete both SPNs (the
one that is being used for the SQL account and for the Server Account), then
give both accounts (user account and computer account) the right to read and
write the ServicePrincipalName.

This procedure is explained in the following article:
http://support.microsoft.com/kb/811889
--
I hope that the information above helps you.
Have a Nice day.

Jorge Silva
MVP Directory Services

Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.

Post by Gunnertac
I am getting Event ID 11 errors indicating a duplicate SPN for MSSQL Service.
I used the TechNete script queryspn and it appears that instead of having two
computers with the same SPN I have one user and one computer with the same
CN=SQLSA,OU=Virtual,OU=Admins,OU=Accounts,OU=Bellevue,DC=MYDOMAIN,DC=LOCAL
Class: user
User Logon: SQLSA
-- MSSQLSvc/MYSERVER.MYDOMAIN.LOCAL:1433
CN=MYSERVER,OU=Non-Domain
Controllers,OU=Servers,OU=Computers,OU=Bellevue,DC=MYDOMAIN,DC=LOCAL
Class: computer
Computer DNS: MYSERVER.MYDOMAIN.LOCAL
-- MSSQLSvc/MYSERVER.MYDOMAIN.LOCAL:1433
-- HOST/MYSERVER
-- HOST/MYSERVER.MYDOMAIN.LOCAL
I have other SQL Servers that look identical to the computer entry that
don't have their MSSQL SPN duplicated in the User SQLSA so I'm guessing I
should delete the entry from the user using ADSIEDIT to clear the error. Is
that correct? I don't see two computers with that SPN.
While I'm here I have another question. We just upgraded a server from SQL
CN=MYSERVER2,OU=Non-Domain
Controllers,OU=Servers,OU=Computers,OU=Bellevue,DC=MYDOMAIN,DC=LOCAL
Class: computer
Computer DNS: MYSERVER2.MYDOMAIN.LOCAL
-- MSSQLSvc/MYSERVER2.MYDOMAIN.LOCAL
-- MSSQLSvc/MYSERVER2.MYDOMAIN.LOCAL:1433
-- HOST/MYSERVER2
-- HOST/MYSERVER2.MYDOMAIN.LOCAL
Is this server not creating a duplicate SPN error because one is appended
with :1443? I have other SQL servers and their entries all are appended with
:1443. They are all SQL 2005 boxes. Should I delete the appended SPN from AD
or should I leave it alone?
--
Tim

Paul Bergson [MVP-DS]

2010-01-06 13:29:17 UTC

Permalink

Just a bit of followup. Each sql instance should have its own service
account established for an spn.

Below is a quick how to

Kerberos on a sql server. You will need to setup a service principal name
(SPN) for a unique service account for the specific instance on the sql
server. Grant this account local admin privileges on this server.
Start by creating a service account
Grant this account local admin privileges on the sql server server.
Configure the service principal name (SPN):
setspn -A MSSQLSvc/<FQDN>:1433 <SQL_Service_Account>
Example: setspn -A MSSQLSvc/SQLServer01.MyDomain.com:1433 serviceSQLServer01

To verify open up ADSIEdit and find the service account user object and
inspect the attribute value, servicePrincipalName, it should contain the
value following the -A above.
Open up the services console within Control panel on the sql server and
modify every service associated with this sql instance, with the new user
account on the "Log On As". Once modified restart each of the services.
EX: SQL Server
SQL Server Agent
SQL Server Browser
SQL Server FullText Search
SQL Server Integration Services
SQL Server Reporting Services

Download the resource kit for the sql server and run kerbtray on the local
workstation to monitor and purge Kerberos tickets as you work on this
process. Kerberos is extremely picky, you had best make sure you do
everything as expected or it won't work!
Open up Active Directory Users and Computers (ADUC) and find the sql server
object, right click on it and select properties. Click on the Delegation
tab and select "Trust this computer for delegation to any service (Kerberos
only)" within the Account Options window.
Within ADUC search for the sql service account and open up its properties,
select the Delegation tab and select "Trust this user for delegation to any
service (Kerberos only). Select the Account tab and deselect "Account is
sensitive and cannot be delegated".

Check out the link below for detailed specifics:
http://msdn.microsoft.com/en-us/library/ms189585(SQL.90).aspx
--
Paul Bergson
MVP - Directory Services
MCTS, MCT, MCSE, MCSA, Security+, BS CSci
2008, 2003, 2000 (Early Achiever), NT4
Microsoft's Thrive IT Pro of the Month - June 2009

http://www.pbbergs.com

Please no e-mails, any questions should be posted in the NewsGroup This
posting is provided "AS IS" with no warranties, and confers no rights.

Post by Jorge Silva
Hi
this problem occurs when two or more accounts have the same service
principal name (SPN) registered. This is not allowed and the KDC will
"scream" when this issue is detected. You need to detect where the
duplication exists (use the setspn tool), and remove the duplication that
Is not needed. One thing that you need to understand is why that SPN is
need and by which service/account is required. For instance, for SQL you
may need to set the SPN for a given account that is running under SQL
service. If you're sure that changing that SPN will not break other
configurations (that may be set incorrectly using the wrong SPN), you may
delete both SPNs (the one that is being used for the SQL account and for
the Server Account), then give both accounts (user account and computer
account) the right to read and write the ServicePrincipalName.
http://support.microsoft.com/kb/811889
--
I hope that the information above helps you.
Have a Nice day.
Jorge Silva
MVP Directory Services
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.

Jorge de Almeida Pinto [MVP - DS]

2010-01-07 23:10:19 UTC

Permalink

an SPN should be registered on account only (user or computer)
--
Cheers,
(HOPEFULLY THIS INFORMATION HELPS YOU!)

# Jorge de Almeida Pinto # MVP Identity & Access - Directory Services #

BLOG (WEB-BASED)--> http://blogs.dirteam.com/blogs/jorge/default.aspx
BLOG (RSS-FEEDS)--> http://blogs.dirteam.com/blogs/jorge/rss.aspx
------------------------------------------------------------------------------------------
* This posting is provided "AS IS" with no warranties and confers no rights!
* Always test ANY suggestion in a test environment before implementing!
------------------------------------------------------------------------------------------
#################################################
#################################################
------------------------------------------------------------------------------------------

__________ Information from ESET Smart Security, version of virus signature database 4752 (20100107) __________

The message was checked by ESET Smart Security.

http://www.eset.com