AlwaysOn可用性组创建失败(error 41131)

问题描述

在SQLServer2016上创建可用性组失败，错误显示如下：

1
2
3
4


Failed to bring availability group 'availability_group' online. The operation timed out.
Verify that the local Windows Server Failover Clustering (WSFC) node is online. 
Then verify that the availability group resource exists in the WSFC cluster. 
If the problem persists, you might need to drop the availability group and create it again.

原因

导出集群日志(PowerShell)

1
2


mkdir d:\trace
Get-ClusterLog -Destination d:\trace

其中错误信息如下

1
2


ODBC Error: [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The user does not have permission to perform this action. (297)
Failed to run diagnostics command. See previous log for error message

如果在SQL Server登录时丢失[NT AUTHORITY\SYSTEM]帐户，或者该帐户缺乏创建高可用性组所需的权限，就会发生此问题。

解决方案

创建NT AUTHORITY\SYSTEM账户

1
2
3
4


USE [master]
GO
CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO 

添加相关权限

1
2
3
4
5
6


GRANT ALTER ANY AVAILABILITY GROUP TO [NT AUTHORITY\SYSTEM]
GO
GRANT CONNECT SQL TO [NT AUTHORITY\SYSTEM]
GO
GRANT VIEW SERVER STATE TO [NT AUTHORITY\SYSTEM]
GO 

Tips:Cannot create a high-availability group in Microsoft SQL Server 2012