I have a two nodes SQL failover cluster instance setup. When I try to connect to SQL FCI in SSMS using sa on ACTIVE node, it shows the blue icon but if I access the instance from PASSIVE node, it displayed a green icon perfectly. I have simulated a test lab environment according to my production and it behave the same as production. The configuration and scenarios are as below:
SQL FCI: SQL 2019 Eval
OS Version: W2019 Standard
iSCSI shared disks attached to both nodes
TESTLAB\SQLADMIN1 (Domain admin account that used to perform the installation and configuration) - Local administrator of both nodes and also the domain administrator
TESTLAB\SQLSERVICE (Domain user account that being used as service account as well as SQL logins) - Local administrator of both nodes
sa account enabled
No Firewall in between and Windows Defender Firewall has been turned off on both nodes
Scenario 1 (Working fine):
Login to ACTIVE/PASSIVE node with domain administrator account (TESTLAB\SQLADMIN1) and launch SSMS, tried with Windows authentication and SQL authentication (using sa account). Both account displayed green icon on SQL server and SQL Agent.
Scenario 2 (Question mark appear):
Login to ACTIVE node with domain user account (TESTLAB\SQLSERVICE) and launch SSMS, tried with Windows authentication and SQL authentication (using sa account). Both accounts displayed blue question mark on SQL server and SQL agent. If I login to ACTIVE node using Domain admin account, no issue occur.
Scenario 3 (Working fine):
Login to PASSIVE node with domain user account (TESTLAB\SQLSERVICE) and admin account (TESTLAB\SQLADMIN) and launch SSMS. Both Windows authentication and SQL authentication used, green icon appeared on SQL server and SQL agent.
My issue is similar to below thread but there's no resolution:
I have manually added my SQL service account to WMI management to allow remote enabled but still having the same issue.