To allow SQL Server to auto-generate the Service Principal Names (SPNs) needed, you can provide the proper permissions to the SQL Service Account. The steps:

• Grant Permissions:
  • Make sure the SQL Service Account has the necessary permissions to register SPNs. This can be done by adding the account to the Active Directory group that has the Write service Principal Name permission.
• Manually Add SPNs (if auto-generation does not work):
  • Use setspn -S rather than -A: -S checks for duplicate SPNs before adding, and duplicates break Kerberos.
  • You can manually add the missing SPNs using the set spn command. Here is an example command to add an SPN:
  • setspn -S MSSQLSvc/server:1433 Service account
• Reconnect to SQL Server:
  • After adding the SPNs, try reconnecting to SQL Server with your client application.
• Remove Misplaced SPNs (if necessary):
  • If there are misplaced SPNs, you can remove them using the setspn command. Here is an example command to remove an SPN:
  • setspn -D MSSQLSvc/server:1433 Service account
• Clear Kerberos Ticket Cache:
  • On the client machine, either log off and log back in or clear the Kerberos ticket cache by running the following command:
  • klist purge

These steps should help you make sure the SQL Service Account can auto-generate the SPNs needed for Kerberos authentication

You could provide the proper permissions to the SQL Service Account to allow SQL to auto generate the SPNs needed.

If auto generate does not work

Solution: Missing SPNs

\1. Run the following command to add the missing SPN:

Command syntax: setspn -S <SPN> <Account>

setspn -S MSSQLSvc/server:1433 Service account

\2. Try reconnecting to SQL Server with your client application.

Alternatively, you could provide the proper permissions to the SQL Service Account to allow SQL to auto generate the SPNs needed.

Note: A Missing SPN may not result in a connectivity failure but will prevent the application from using Kerberos authentication.

Solution: Misplaced SPNs

\1. Run the following command to remove the misplaced SPN:

Command syntax: setspn -D <SPN> <Account>

setspn -D MSSQLSvc/server:1433 Service account

\2. On the client machine, either logoff and log back in or clear the Kerberos ticket cache by running the following command:

klist purge

\3. Try reconnecting to SQL Server with your client application.

Note: If you wish to use Kerberos from your application you need to configure the correct SPN using the resolution from scenario 1 above.

Your SQL Administrator must be aware of it, you can ask your SQL Administrator to run the commands with appropriate rights

setspn -S MSSQLSvc/server:1433 Service account

From my SQL Server DBA handbook.