Automatic MSSQLSvc SPN registration not working despite required AD permissions

Automatic SPN registration for SQL Server requires more than the AD permissions alone. Verify these prerequisites and checks:

1. Confirm Kerberos prerequisites
   • The client and SQL Server computer must be in the same Windows domain or in trusted domains.
   • An SPN must map to the Windows account that is actually running the SQL Server service.
2. Verify the SQL Server service account permissions on the SQL Server computer object in Active Directory
   • Validated write to service principal name
   • Read servicePrincipalName
   • Write servicePrincipalName If these rights are missing, SQL Server can fail to register the SPN automatically.
3. Check whether SQL Server logged SPN registration failure
   • If SPN registration fails during startup, SQL Server records the failure in the SQL Server error log and startup continues.
   • If SPN deregistration fails during shutdown, SQL Server records that in the SQL Server error log and shutdown continues. This is the primary log location explicitly documented for SPN registration failures.
4. Verify whether the connection is actually using Kerberos or falling back to NTLM Run:

      SELECT net_transport, auth_scheme
      FROM sys.dm_exec_connections
      WHERE session_id = @@SPID;
   

   Expected result:
   • auth_scheme = KERBEROS when Kerberos is working
   • auth_scheme = NTLM when SPN lookup fails or does not map correctly
5. Check for common SPN configuration problems Common causes documented by Microsoft include:
   • SPN does not exist
   • Duplicate SPNs
   • SPN registered under the incorrect account
   • Incorrect SPN registered
   • SPN maps to an incorrect domain account, virtual account, MSA, or built-in account These conditions can cause either NTLM fallback or authentication failure.
6. Validate the SPN format SQL Server is expected to register For TCP/IP:
   • MSSQLSvc/<FQDN>:<tcpport> For named instance connections that support Kerberos:
   • MSSQLSvc/<FQDN>:<instancename> For default instance:
   • MSSQLSvc/<FQDN> If the registered SPN does not match the actual connection type, instance, port, or service account, Kerberos will not be used correctly.
7. Use Microsoft-supported troubleshooting tools The recommended Microsoft-supported diagnostic approach is:
   • Use Microsoft Kerberos Configuration Manager for SQL Server to troubleshoot Kerberos-related connectivity issues.
   • If automatic registration still fails, use Setspn or Kerberos Configuration Manager to inspect, add, correct, or remove SPNs as needed.
8. If automatic registration still does not occur Microsoft states that manual intervention might be required when the service account lacks the permissions required to register or modify the SPN. The troubleshooting path is to identify why the startup account still cannot complete registration, then correct the account rights or SPN state.

For the specific questions:

1. Additional prerequisites/configurations to verify
   • Domain or trusted-domain membership
   • SQL Server service account is the account the SPN should map to
   • Correct SPN format for the instance and protocol
   • SQL Server error log entries during startup
   • Actual authentication method from sys.dm_exec_connections
2. Common reasons automatic registration fails after permissions are granted
   • Missing or incomplete servicePrincipalName rights
   • Duplicate SPNs
   • SPN exists under the wrong account
   • Incorrect SPN format or incorrect registered SPN
   • SPN lookup does not map to the SQL Server startup account
3. Recommended Microsoft-supported troubleshooting approach
   • Review the SQL Server error log for startup SPN registration failures
   • Verify the active authentication method with sys.dm_exec_connections
   • Use Microsoft Kerberos Configuration Manager for SQL Server
   • Use Setspn to inspect and correct missing, duplicate, or misregistered SPNs
4. Logs/events to check
   • The documented location is the SQL Server error log for SPN registration failure at startup and deregistration failure at shutdown.
   • No specific Windows Event Log entry is documented in the provided material.

References:

• Register a Service Principal Name for Kerberos connections
• "Cannot generate SSPI context" error when using Windows authentication to connect to SQL Server
• Service Principal Name (SPN) Support in Client Connections