2

I'm trying to configure a SQL Server 2014 (12.0.5000.0) instance to use a regular user account in AD and Kerberos auth. During installation the default accounts (local system ones) were used, and the SPNs MSSQLSvc/<FQDN>:[<port> | <instancename>] were both created on the server's computer account.

I reconfigured all the SQL Server services to use the user account. When I manually restart the services via Configuration Manager the SPNs are correctly recreated on the user account and Kerberos auth works (checking via SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid ; from a domain joined client).

I then manually removed the SPNs on the computer account to avoid duplicate SPNs.

The problem is when the server restarts, the SPNs are not recreated (error follows), Kerberos auth does not work on the server, and I end up with NTLM auth instead.

SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.

The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/<FQDN>:<instancename> ] for the SQL Server service. Windows return code: 0xffffffff, state: 43. 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.

The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/<FQDN>:<port> ] for the SQL Server service. Windows return code: 0xffffffff, state: 43. 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.

Both the service (user) account, and the server (computer) account have been granted explicit permission to read and write SPNs on themselves and each other.

If I manually restart the services via Configuration Manager once the machine is booted and I'm logged in, the SPNs do get recreated (log message follows) and Kerberos does work.

SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.

The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/<FQDN>:<instancename> ] for the SQL Server service.

The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/<FQDN>:<port> ] for the SQL Server service.

What I can't work out is

  • what state: 43 means in the SQL Server ERRORLOG. Presumably it's not referring to Idaho.
  • why the SPNs do get recreated on a restart when using Configuration Manager, but do not get recreated on a server restart
  • why on a server restart the SPNs (which still exist) don't get used [if I trigger the restart in Configuration Manager they get recreated, but in a server shutdown, they aren't deleted, therefore they still exist on server startup]

I notice there is an SQLWriter service which is still running as Local System but according to SQL Writer Service, this is correct.

The FFL is 2008, and the DFL is 2008 R2.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.