The target principal name is incorrect. Cannot generate SSPI context.

It is a good practice to use a domain or service account to run the SQL service.

However, once you do the right thing and change the SQL Service account, you may start getting the following error message when attempting to connect to the sql server:

“The target principal name is incorrect.  Cannot generate SSPI context.”

The explanation, as given by Microsoft in this article

If you run the SQL Server service under the LocalSystem account, the SPN is automatically registered and Kerberos authentication interacts successfully with the computer that is running SQL Server. However, if you run the SQL Server service under a domain account or under a local account, the attempt to create the SPN will fail in most cases because the domain account and the local account do not have the right to set their own SPNs. When the SPN creation is not successful, this means that no SPN is set up for the computer that is running SQL Server. If you test by using a domain administrator account as the SQL Server service account, the SPN is successfully created because the domain administrator-level credentials that you must have to create an SPN are present.

There are 4 ways to fix the problem:

• Revert to using the Network Service or Local System account (NOT RECOMMENDED)
• Assign the domain account to the Domain Admins group (NOT IDEAL – due to the elevated permissions)
• Fix the problem by giving the domain account just the appropriate permissions in Active Directory. Permissions required are
  • ServicePrincipalName: Read
  • ServicePrincipalName: Write
• Create SPNs for the service account manually

Below, I’ll show how to give correct permissions and how to create SPNs manually.

Giving permissions to create SPNs to domain account

First, it is good practice to verify that the problem is actually due to permission issues. Log in to the server where you SQL Instance is running. Go to the error logs and look for the last time that the SQL service was restarted. You should find an error message similar to this:

Date                    05/12/2017 8:49:23 AM
Log                       SQL Server (Archive #1 – 05/12/2017 8:49:23 AM)
Source                Server
Message
The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/servername.domainname.net:1433 ] for the SQL Server service. Windows return code: 0x2098, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

Now we are sure this is SPN related problem. Let’s give permissions to register SPNs to SQL domain account.

Log in to the server running your Active Directory service and execute the following steps:

• Run Adsiedit.msc
• In the ADSI Edit snap-in, expand Domain [YourDomainName], expand DC= RootDomainName, expand CN=Users, right-click CN= [YourAccountName, and then click Properties.
• In the CN= AccountName Properties dialog box, click the Security tab.
• On the Security tab, click Advanced.
• In the Advanced Security Settings dialog box, select one (any) of “SELF”‘s row
• Click Edit, Open Permission Entry dialog box.
• Make sure Principal is “SELF”, Type is “Allow” and “Applied to” is “This Object Only”, in Properties section, select the properties below:
  • Read servicePrincipalName
  • Write servicePrincipalName

Click OK to apply all changes and exit the ADSI Edit snap-in.

Now restart the SQL Service(s) that use the account in question.

You can verify that the SPN has been registered successfully upon the restart by going to the SQL Server logs. You should now see an entry similar to this:

Date 05/12/2017 9:24:15 AM
Log SQL Server (Current – 05/12/2017 9:24:15 AM )
Source Server
Message
The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/servername.domainname.net:1433 ] for the SQL Server service.

Connections to SQL Server should now succeed!

How to create SPNs manually

You can create SPNs manually using command line setspn tool. Run command prompt under account that has permissions to create SPN (Domain Admins and above) and type:

to register SPNs for both hostname and FQDN for SQL domain account.

You can also use Microsoft Kerberos Configuration Manager for SQL Server tool to analyze your environment and fix SPN related issues if any.

To Install:

1. Download the 32bit or 64bit version of the Kerberos Configuration Manager installer that matches your computer’s OS architecture.
2. Click Open to start the installation immediately or click Save to save the installation .msi file to disk and install it later.
3. Accept the license term of this tool.
4. Click Next to complete the installation.

To Launch the Tool:

1. After the installation is complete successfully, double click the KerberosConfigMgr.exe  to launch the application.

To Generate SPN List from Command Line:

1. Go to command line.
2. Switch to the folder where KerberosConfigMgr.exe is.
3. Type KerberosConfigMgr.exe -q -l
4. For more command line option, type KerberosConfigMgr.exe -h

To generate SPNs from a GUI:

1.  Double click on KerberosConfigMgr.exe
2. Enter credentials that have permissions to register SPNs
3. Wait for the tool to analyze your environment
4. Apply necessary fixes

To Save a Server’s Kerberos Configuration Information:

1. Connect to the target windows server.
2. Click on Save button on the toolbar
3. Specify the location where you want the file to be saved at.  It can be on a local drive or network share.
4. The file will be saved as .XML format.

To View a Server’s Kerberos Configuration Information from Saved File:

1. Click on the Load button on the toolbar.
2. Open the XML file generated by Kerberos Configuration Manager.

To Generate a Script to Fix SPN from Command Line:

1. Click on the Generate button for the SPN entry.
2. The generated script can be used by a user who has privilege to fix the SPN on the server.

To See the Log Files for this Tool:

1. By default, one log file is generated in the user’s application data folder.

To Get Help:
Option 1: Hover the mouse cursor over the command for tooltip.
Option 2:  Run KerberosConfigMgr.exe –h from command line
Option 3: Click the Help button in the toolbar.

Setspn tool examples

It will register SPN “HOST/daserver1” and “HOST/{DNS of daserver1}”

It will register SPN “http/daserver” for computer “daserver1” if no such SPN exists in the domain

It will delete SPN “http/daserver” for computer “daserver1”

It will register SPN “http/daserver” for computer “daserver1” if no such SPN exists in the forest

It will register SPN “http/daserver” for user account “dauser”
if no such SPN exists in the domain

It will report all duplicate registration of SPNs in this domain and bar

It will find all SPNs of the form */daserver registered in the forest to which bar belongs