MSSQL AAG Replica secondary showing disconnected error

 Error:

You will see the secondary replica is disconnected error as:

Solution:

In this case go to SQL server and check the ERROR log and see the exact error:

Error Log location:

C:\Program Files\Microsoft SQL Server\<MSSQL15.MSSQLSERVER>\MSSQL\Log

I am able to see the error as :

There is no user and permisions.

On Node2 from SQL query run:

GO

CREATE LOGIN [RAN\SQL-AG-VM-Node1$] FROM WINDOWS

GO

GRANT CONNECT ON ENDPOINT::hadr_endpoint TO [RAN\SQL-AG-VM-Node1$];

GO

On Node2 run SQL query

GO

CREATE LOGIN [RAN\SQL-AG-VM-Node2$] FROM WINDOWS

GO

GRANT CONNECT ON ENDPOINT::hadr_endpoint TO [RAN\SQL-AG-VM-Node2$];

GO

Now try a failover and you will see both in CONNECTED