May 31, 2017 at 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/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
May 31, 2017 at 1:03 pm
hurricaneDBA - Wednesday, May 31, 2017 5:22 AMDear 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 DomainNow 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
May 31, 2017 at 2:04 pm
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
May 31, 2017 at 2:22 pm
hurricaneDBA - Wednesday, May 31, 2017 2:04 PMHi 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
May 31, 2017 at 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
May 31, 2017 at 3:47 pm
hurricaneDBA - Wednesday, May 31, 2017 3:10 PMHi 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
June 1, 2017 at 5:27 am
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
January 19, 2018 at 12:59 pm
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.
February 1, 2018 at 7:08 am
You can also use this tool from Microsoft
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply