SQL Server SSPI Contects Error.

Hewlett-packard Ms windows server 2003 r...
January 10, 2011 at 13:59:24
Specs: Windows Vista, Quad Core / 2Gig
Our SQL server has recently start getting a SSPI Connects Error. I looked into our error logs and we are getting the following...

There are multiple accounts with name MSSQLSvc/svr020.MAINDOMAIN.local:1433 of type DS_SERVICE_PRINCIPAL_NAME.

For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/even...

I do method 2 which is to use the following a ommand...

ldifde -f check_SPN.txt -t 3268 -d "" -l servicePrincipalName -r "(servicePrincipalName=MSSQLSvc/svr020.MAINDOMAIN.local:1433)" -p subtree

and I get the following...

dn: CN=SVR020,OU=Domain Controllers,DC=MAINDOMAIN,DC=local
changetype: add

servicePrincipalName: MSSQLSvc/svr020.MAINDOMAIN.local:1433
servicePrincipalName: VProRecovery Backup Exec System Recovery Agent 7.0/svr020.MAINDOMAIN.local
servicePrincipalName: ldap/77117311-482e-4c15-b9b2-f914ddc8913d._msdcs.MAINDOMAIN.local
servicePrincipalName: ldap/svr020.AZDFBLS.local/MAINDOMAIN
servicePrincipalName: ldap/SVR020
servicePrincipalName: ldap/svr020.MAINDOMAIN.local
servicePrincipalName: ldap/svr020.MAINDOMAIN.local/MAINDOMAIN.local
servicePrincipalName: HOST/svr020.MAINDOMAIN.local/MAINDOMAIN
servicePrincipalName: HOST/svr020.MAINDOMAIN.local/MAINDOMAIN.local
servicePrincipalName: HOST/SVR020
servicePrincipalName: HOST/svr020.MAINDOMAIN.local
servicePrincipalName: E3514235-4B06-11D1-AB04-00C04FC2DCD2/77117311-482e-4c15-b9b2-f914ddc8913d/MAINDOMAIN.local
servicePrincipalName: Dfsr-12F9A27C-BF97-4787-9364-D31B6C55EB04/svr020.MAINDOMAIN.local
servicePrincipalName: NtFrs-88f5d2bd-b646-11d2-a6d3-00c04fc9b232/svr020.MAINDOMAIN.local
servicePrincipalName: GC/svr020.MAINDOMAIN.local/MAINDOMAIN.local

I am not seeing the duplicate. Is there something I am missing or am I executing the command wrong?



See More: SQL Server SSPI Contects Error.

Report •

#1
January 14, 2011 at 07:15:09
Some times I have noticed that on computers having this problem that they can not do a Reverse Lookup. If I do a...

PING -a 10.100.100.7

It will not resolve it to the server name but if I do a normal ping to the server FQDN it resolves and IP every time. When I reboot it fixes the problem and it seems to fix the problem with the revers lookup as well. Has any one run into this before. I am not sure if the revers lookup is the problem but it is all I have to go on.


Report •

#2
January 18, 2011 at 11:10:33
After doing some research I have found that the SPN can and will get created under the Administrator CN. If it is a duplicate it will cause this error. So I ran the following command and got the following result:
ldifde -r (servicePrincipalName=MSSQLSvc/svr020*) -v -f somefile.txt


dn: CN=Administrator,CN=Users,DC=MYDOMAIN,DC=local
changetype: add
objectClass: top
objectClass: person
objectClass: organizationalPerson
objectClass: user
cn: Administrator
description: Built-in account for administering the computer/domain
distinguishedName: CN=Administrator,CN=Users,DC=MYDOMAIN,DC=local
instanceType: 4
whenCreated: 20070707183753.0Z
whenChanged: 20110108033736.0Z
uSNCreated: 8194

dn: CN=SVR020,OU=Domain Controllers,DC=MYDOMAIN,DC=local
changetype: add
objectClass: top
objectClass: person
objectClass: organizationalPerson
objectClass: user
objectClass: computer
cn: SVR020
description: Phoenix Server (Backup DC)
distinguishedName: CN=SVR020,OU=Domain Controllers,DC=MYDOMAIN,DC=local
instanceType: 4
whenCreated: 20071014173627.0Z
whenChanged: 20110116130401.0Z
displayName: SVR020$
uSNCreated: 172703
uSNChanged: 3395354
name: SVR020

This shows that there are duplicates one under a User CN and another under a Computer CN. So I ran the following command so that I can Identify the the SPNs under the User "Administrator":

setspn -l Administrator

Registered ServicePrincipalNames for CN=Administrator,CN=Users,DC=MYDOMAIN,DC=local:
    MSSQLSvc/svr010.MYDOMAIN.local:1795
    MSSQLSvc/svr020.MYDOMAIN.local:1433
    {14E52635-0A95-4a5c-BDB1-E0D0C703B6C8}/SVR030
    {14E52635-0A95-4a5c-BDB1-E0D0C703B6C8}/svr030.MYDOMAIN.local

So from what I have read, I want to delete the Administrator SPN. I think the command will be:


setspn -d MSQLSvc/svr020.MYDOMAIN.local:1433 Administrator

My question is, what else will I need to do?
Will the system automatically recreate the SPN or do I need to add it manually?
Also, am I using the right command?


Report •

#3
January 19, 2011 at 06:44:10
Ok the...

setspn -d MSQLSvc/svr020.MYDOMAIN.local:1433 Administrator

has gotten rid of the error that keeps popping up in the System Log.

I don't know if it has fixed the SSPI Context error or not. I will check in with the database users to see if they are still getting them.


Report •
Related Solutions


Ask Question