我注意到，在最近更新 Windows 2019 后，我们唯一运行 Active Directory 的服务器，几个相关的 SQL Server 服务不再启动。
在研究了每个错误之后，我能够让它们运行，只剩下 SQL Server Agent 需要排除故障。我可以手动启动它，它会运行 10 秒钟，然后关闭。
经过研究，我尝试过的方法：
- 服务 > SQL SERVER AGENT > 登录，我将其更改为本地系统帐户并勾选允许服务与桌面交互
- 服务> SQL SERVER 代理>常规，我将启动类型更改为自动（延迟启动）
- 服务> SQL SERVER AGENT>恢复>第一次失败，我设置为重新启动服务
- 服务> SQL SERVER AGENT>恢复>第二次故障，我设置为重新启动服务
SQLAGENT.OUT 的内容
2023-06-15 09:25:53 - ? [508] Logging SQL Server Agent messages in file 'E:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT'.
2023-06-15 09:25:53 - ? [000] Event Global\sqlserverRecComplete opened
2023-06-15 09:25:53 - ? [100] Microsoft SQLServerAgent version 15.0.2101.7 (X64 unicode retail build) : Process ID 8028
2023-06-15 09:25:53 - ? [495] The SQL Server Agent startup service account is XXX\SA200$.
2023-06-15 09:25:53 - ! [150] SQL Server does not accept the connection (error: 776). Waiting for Sql Server to allow connections. Operation attempted was: Verify Connection On Start.
2023-06-15 09:25:54 - ! [150] SQL Server does not accept the connection (error: 776). Waiting for Sql Server to allow connections. Operation attempted was: Verify Connection On Start.
2023-06-15 09:25:55 - ! [150] SQL Server does not accept the connection (error: 776). Waiting for Sql Server to allow connections. Operation attempted was: Verify Connection On Start.
2023-06-15 09:25:56 - ! [150] SQL Server does not accept the connection (error: 776). Waiting for Sql Server to allow connections. Operation attempted was: Verify Connection On Start.
2023-06-15 09:25:57 - ! [150] SQL Server does not accept the connection (error: 776). Waiting for Sql Server to allow connections. Operation attempted was: Verify Connection On Start.
2023-06-15 09:25:58 - ! [150] SQL Server does not accept the connection (error: 776). Waiting for Sql Server to allow connections. Operation attempted was: Verify Connection On Start.
2023-06-15 09:25:59 - ! [150] SQL Server does not accept the connection (error: 776). Waiting for Sql Server to allow connections. Operation attempted was: Verify Connection On Start.
2023-06-15 09:26:00 - ! [150] SQL Server does not accept the connection (error: 776). Waiting for Sql Server to allow connections. Operation attempted was: Verify Connection On Start.
2023-06-15 09:26:01 - ! [150] SQL Server does not accept the connection (error: 776). Waiting for Sql Server to allow connections. Operation attempted was: Verify Connection On Start.
2023-06-15 09:26:02 - ! [150] SQL Server does not accept the connection (error: 776). Waiting for Sql Server to allow connections. Operation attempted was: Verify Connection On Start.
2023-06-15 09:26:03 - ! [150] SQL Server does not accept the connection (error: 776). Waiting for Sql Server to allow connections. Operation attempted was: Verify Connection On Start.
2023-06-15 09:26:04 - ! [150] SQL Server does not accept the connection (error: 776). Waiting for Sql Server to allow connections. Operation attempted was: Verify Connection On Start.
2023-06-15 09:26:05 - ! [150] SQL Server does not accept the connection (error: 776). Waiting for Sql Server to allow connections. Operation attempted was: Verify Connection On Start.
2023-06-15 09:26:06 - ! [150] SQL Server does not accept the connection (error: 776). Waiting for Sql Server to allow connections. Operation attempted was: Verify Connection On Start.
2023-06-15 09:26:07 - ! [150] SQL Server does not accept the connection (error: 776). Waiting for Sql Server to allow connections. Operation attempted was: Verify Connection On Start.
2023-06-15 09:26:07 - ! [000] Unable to connect to server '(local)'; SQLServerAgent cannot start
2023-06-15 09:26:07 - ! [298] SQLServer Error: 776, SQL Server Network Interfaces: The token supplied to the function is invalid [SQLSTATE HY000]
2023-06-15 09:26:07 - ! [298] SQLServer Error: 776, Cannot generate SSPI context [SQLSTATE HY000]
2023-06-15 09:26:07 - ! [382] Logon to server '(local)' failed (DisableAgentXPs)
2023-06-15 09:26:07 - ? [098] SQLServerAgent terminated (normally)
任何帮助将不胜感激
附加信息
至于可能影响此行为的原因，前一天，我们家的网络电话线路发生了灾难性故障。最快的解决方案是将现有的家用路由器换成使用 SIM 卡/4G 连接的新路由器。新路由器的 IP 地址与之前的路由器相同，其 DHCP 服务器的设置范围也相同。
这让我陷入了一个新的困境，解释了 SSPI > SPN 错误是如何发生的
SQLCHECK 的结果...
Suggested SPN Exists Status
-------------------------------- ------ -----------------------------------------------------------------------------------------
MSSQLSvc/SA200.ad.redacted.net:1433 False SPN is on the wrong account: Administrator, CN=Administrator,CN=Users,DC=ad,DC=redacted,DC=net
MSSQLSvc/SA200:1433 False SPN does not exist.
MSSQLSvc/SA200.ad.redacted.net False SPN is on the wrong account: Administrator, CN=Administrator,CN=Users,DC=ad,DC=redacted,DC=net
MSSQLSvc/SA200 False SPN does not exist.
我怀疑这个 SPN 问题可能是因为我最初使用了错误的解决方案来修复 SQLSERVER 服务 - 我更改了以本地服务身份登录，我认为这可能会导致上述 SPN 问题。我所需要做的就是选择延迟启动。今晚我将尝试将 SQLSERVER 服务重新设置为以 NT AUTHORITY/Network Service 身份登录，然后重新运行 SQLCHECK，看看它是否能解决 SPN 问题
更新日期：2023 年 7 月 22 日
使用 SETSPN -s 添加了 2 个缺失的 SPN...
SETSPN -s MSSQLSvc/SA200 (计算机名称)
SETSPN -s MSSQLSvc/SA200:1433 (计算机名称)
我似乎无法删除并重新添加另外 2 个 SPN...
MSSQLSvc/SA200.ad.redacted.net
MSSQLSvc/SA200.ad.redacted.net:1433
我可以使用 SETSPN -d 删除它们，但当我尝试重新添加时，它会告诉我检测到重复项
答案1
在 SQL Server 管理员中，我们注意到 SQLServer Agent 的服务正在使用本地用户帐户登录 - 我们将其更改为正确的 NT Service\SQLServerAgent，问题得到解决
关于 SQLCheck 详述的 SPN 错误，我们通过使用 SETSPN -d 首先删除每个错误来更正这些错误，注意使用正确的帐户名（否则它们不会被删除）。然后我们使用 -s 选项为每个 SPN 创建新的 SPN。重新运行 SQLCheck 未显示任何错误