SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Connection Failed:: The target principal name is incorrect. Cannot generate SSPI context


Connection Failed:: The target principal name is incorrect. Cannot generate SSPI context

Author
Message
hurricaneDBA
hurricaneDBA
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3498 Visits: 602
Dear MVPs
I migrated a database from SQL Server 2008 running on windows 2008 R2 to a new SQL Server 2012 running on Windows 2012
At first everything was working fine and when we tried to connect from SSMS on a client machine we got the error:

Connection Failed:: The target principal name is incorrect.Cannot generate SSPI context

The error in the SQL Server logs was:

The SQL Server Network Interface library could not registerthe Service Principal Name (SPN) [ MSSQLSvc/ServerName .myDomain.co:1433 ] for theSQL Server service. Windows return code: 0x21c7, state: 15. Failure to registera SPN might cause integrated authentication to use NTLM instead of Kerberos.This is an informational message. Further action is only required if Kerberosauthentication is required by authentication policies and if the SPN has notbeen manually registered.



Now i did lots of research and people mentioned checking for Duplicate SPNs and creating a UDL file and checking also and finally what worked for me was below.
1. Choose the domain account user you want to use using Configuration manager and start all services you want to utilize using this account
2. Grant them AUTH-LogAs-Service and AUTH-LogAs-Batch
3. remove the machine from the Domain and add it again to the Domain

Now i tried changing the service account to another domain account and the problem returned so i recommend you keep that original user unless you really have to change it then modify the user and then rejoin to the domain then it should work fine

Have a great day
KY

Sue_H
Sue_H
SSC Guru
SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)

Group: General Forum Members
Points: 66865 Visits: 13912
hurricaneDBA - Wednesday, May 31, 2017 5:22 AM
Dear MVPs
I migrated a database from SQL Server 2008 running on windows 2008 R2 to a new SQL Server 2012 running on Windows 2012
At first everything was working fine and when we tried to connect from SSMS on a client machine we got the error:

Connection Failed:: The target principal name is incorrect.Cannot generate SSPI context

The error in the SQL Server logs was:

The SQL Server Network Interface library could not registerthe Service Principal Name (SPN) [ MSSQLSvc/SDOADP06.dolphin.co:1433 ] for theSQL Server service. Windows return code: 0x21c7, state: 15. Failure to registera SPN might cause integrated authentication to use NTLM instead of Kerberos.This is an informational message. Further action is only required if Kerberosauthentication is required by authentication policies and if the SPN has notbeen manually registered.



Now i did lots of research and people mentioned checking for Duplicate SPNs and creating a UDL file and checking also and finally what worked for me was below.
1. Choose the domain account user you want to use using Configuration manager and start all services you want to utilize using this account
2. Grant them AUTH-LogAs-Service and AUTH-LogAs-Batch
3. remove the machine from the Domain and add it again to the Domain

Now i tried changing the service account to another domain account and the problem returned so i recommend you keep that original user unless you really have to change it then modify the user and then rejoin to the domain then it should work fine

Have a great day
KY


You don't need to remove and add machines to and from the domain when changing the service account. The error you got usually is for duplicate SPNs - and often you need to clean up the duplicates for the machine itself not just the service account. And then verify the permissions so that the service can read, create SPNs - Read servicePrincipalName and Write servicePrincipalName
If you use configuration manager to change account, the permissions are handled and you won't give permissions that aren't needed. In addition, Windows rights should be granted to the per-service SID, not the domain account. You can find those documented here in the Windows Privileges and Rights section:
Configure Windows Service Accounts and Permissions

Sue





hurricaneDBA
hurricaneDBA
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3498 Visits: 602
Hi Sue
Thanks for the reply
We checked for duplicates and didn't find the server name anywhere on AD
Am I supposed to search for duplicates somewhere else?

Kal
Sue_H
Sue_H
SSC Guru
SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)

Group: General Forum Members
Points: 66865 Visits: 13912
hurricaneDBA - Wednesday, May 31, 2017 2:04 PM
Hi Sue Thanks for the replyWe checked for duplicates and didn't find the server name anywhere on AD Am I supposed to search for duplicates somewhere else?Kal


Did you use the setspn utility? In the case of your original error: setspn -l SDOADP06
I can't tell what you may have changed since that error but that is typically the first thing to run.
I just saw this article - much of it applies to your situation. Try walking through the steps listed -
Changing service account for MS SQL Server 2012 and the following issues

Sue



hurricaneDBA
hurricaneDBA
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3498 Visits: 602
Hi Sue
Yes I tried that but setsnp doesn't have the option -l anymore
I'll send a snapshot of the options tomorrow from the office
I ran setsnp -X which is supposed to list all duplicates and the server name didn't appear in the list

Kal
Sue_H
Sue_H
SSC Guru
SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)

Group: General Forum Members
Points: 66865 Visits: 13912
hurricaneDBA - Wednesday, May 31, 2017 3:10 PM
Hi Sue Yes I tried that but setsnp doesn't have the option -l anymore I'll send a snapshot of the options tomorrow from the office I ran setsnp -X which is supposed to list all duplicates and the server name didn't appear in the list Kal


According to Microsoft docs for your Windows version, it is still an option:
Setspn

This article has additional information on the duplicate check and different things to check:
SPN and UPN uniqueness

Sue



hurricaneDBA
hurricaneDBA
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3498 Visits: 602
Hi Sue
It seems i found the cause
I ran setspn but what worked was uppercase L as lowercase L didnt work
When i run the command SETSPN - L ServerName
I got the following:

MSSQLSvc/ServerName .mydomain.co:1433
MSSQLSvc/ServerName .mydomain.co
WSMAN/ServerName .mydomain.co
WSMAN/ServerName
TERMSRV/ServerName .mydomain.co
TERMSRV/ServerName
RestrictedKrbHost/ServerName
HOST/ServerName
RestrictedKrbHost/ServerName .mydomain.co
HOST/ServerName .mydomain.co

I ran it on 2 other database servers and the difference was below:
CmRcService/ServerName
CmRcService/ServerName .mydomain.co

Hence i removed the lines below and replaced them as follows:

C:\>setspn -DMSSQLSvc/ServerName .mydomain.co:1433 ServerName

C:\>setspn -DMSSQLSvc/ServerName .mydomain.co ServerName

Then we added the SPNs using thecommand below

C:\>setspn -ACmRcService/ServerName .mydomain.co ServerName

C:\>setspn -ACmRcService/ServerName ServerName

This worked for me and i am monitoring the server
I think this issue resulted because when installing SQL server i didnt specify the service account per service / feature being installed.
As usually i modify the service account during the installation and with those installations i didnt have this issue

Thanks again for the help Sue
have a lovely week
Kal


scribesjeff
scribesjeff
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 127
I ran into this issue on SQL 2016 when we installed a QA box and took the default NT service\MSSQLServer account to start the SQL server service. Then we tried to change the account to a domain account after the install. We could only connect through IP from remote desktops in SSMS.. Changing the account SQL ran under back to the NT account fixed the problem so we started looking at SPNs and noticed the QA server had duplicate entries as stated above. Removing the duplicates fixed the issue.
leon.booij
leon.booij
Right there with Babe
Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)

Group: General Forum Members
Points: 758 Visits: 591
You can also use this tool from Microsoft

Microsoft® Kerberos Configuration Manager for SQL Server®


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search