Issue:- While configuring AlwaysOn in Azure Environment, sometime users get below error.
Problem:-
Disconnecting
connection from NODE2...
Connecting
to NODE2...
Disconnecting
connection from NODE2...
Connecting
to NODE1...
Msg
41131, Level 16, State 0, Line 70
Failed
to bring availability group 'agadven' 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.
Msg
41152, Level 16, State 2, Line 70
Failed
to create availability group 'agadven'. The operation encountered SQL
Server error 41131 and has been rolled back. Check the SQL Server error
log for more details. When the cause of the error has been resolved,
retry CREATE AVAILABILITY GROUP command.
Disconnecting
connection from NODE1...
Connecting
to NODE2...
Msg
41044, Level 16, State 1, Line 82
Availability
group name to ID map entry for availability group 'agadven' cannot be found in
the Windows Server Failover Clustering (WSFC) store. The availability
group name may be incorrect, or the availability group may not exist in this
Windows Server Failover Cluster. Verify the availability group exists and
that the availability group name is correct and then retry the operation.
Msg
41158, Level 16, State 3, Line 82
Failed
to join local availability replica to availability group 'agadven'. The
operation encountered SQL Server error 41044 and has been rolled back.
Check the SQL Server error log for more details. When the cause of the
error has been resolved, retry the ALTER AVAILABILITY GROUP JOIN command.
Disconnecting connection from
NODE2...
Resolution:-
To resolve this issue, use one of the following methods.
Method 1: Use manual steps
if [NT AUTHORITY\SYSTEM] is not present in SQL server Logins Folder
Create a logon in SQL Server for the [NT AUTHORITY\SYSTEM] account on each SQL Server computer that hosts a replica in your availability group.
Grant the [NT AUTHORITY\SYSTEM] account the following server-level permissions:
Alter Any Availability Group
Connect SQL
View server state
Note Make sure that no other permissions are granted to the account.
Method 2: Use script
To create the [NT AUTHORITY\SYSTEM] account, run the following in a query window:
USE [master]
GO
CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
To grant the permissions to the [NT AUTHORITY\SYSTEM] account, run the following in a query window:
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]
After doing this, my issue resolved
Thanks for reading.
