Sample Header Ad - 728x90

SQL Server SPNs not being created, state: 43

2 votes
0 answers
2252 views
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/:[ | ] 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/: ] 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/: ] 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/: ] for the SQL Server service. > > The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/: ] 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.
Asked by Samuel Harmer (121 rep)
Feb 27, 2018, 12:56 PM
Last activity: Jun 7, 2018, 12:24 PM