服务器环境如下:
戴尔T330服务器(服务器做了RAID)、windows server 2012 r2 datacenter、SQL server 2012、.net framework v4.0.30319
客户在使用的过程中多次反映系统有时无法登陆,经过跟踪发现数据库在运行一段时间后状态变成“恢复挂起”,重启SQL Server服务后恢复正常,但是运行几天后问题又重复出现,通过查看Windows系统日志未发现异常,查看SQL Server错误日志后,发现如下错误日志:
The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/WIN-G47ST85UM16:1433 ] for the SQL Server service. Windows return code: 0xffffffff, state: 63. 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/WIN-G47ST85UM16 ] for the SQL Server service. Windows return code: 0xffffffff, state: 63. 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.
错误: 18456,严重性: 14,状态: 38。
望告知小弟该问题产生的原因!感激不尽!
问题已解决!
报错写的很清楚,SPN注册失败导致无法使用Kerberos 验证。
你应该是修改了sql服务的启动账号,导致SPN 不一致,用该windows账号验证失败。
setspn -L servername 删除MSSQLSvc打头的SPN,setspn -D SPN_name servername
https://blogs.msdn.microsoft.com/apgcdsd/2011/09/26/kerberosntlm-sql-server/
https://support.microsoft.com/zh-cn/help/811889/how-to-troubleshoot-the-cannot-generate-sspi-context-error-message