SSMS can't retrieve status of SQL Server and SQL Agent

[Hayorti]

I duplicated this dockerfile so that I could install SQL Server 2017 on a Windows Server 2019 base, and operationally everything seems fine, but I'm having a (potentially) minor problem with SSMS. I can connect to the container, browse the databases, and create and run SQL Agent jobs, but there's a blue question mark on both the SQL Server and SQL Agent icons. Reading up, it seems like this might have something to do with Windows Firewall or WMI not being accessible remotely, but I've hit a wall.

The Windows Defender Firewall service itself is off by default, and that's what most of the WMI troubleshooting actually hinges on, so I can't think of anything else to do.

Has anyone run into this before or have any thoughts as to what might be the cause/resolution?

[forkazmoden]

Hi,

Based on my test, in order to display the status of the service, the server must have WMI installed and the user must have rights to remotely access the WMI object. Windows Management Instrumentation (WMI) is the infrastructure for management data and operations on Windows-based operating systems. If you are using a LINUX container, there is no WMI in it. But I also cannot find any way to configure this permission in a Windows container.

To return information about the SQL Server and SQL Server Agent services, you can use the sys.dm_server_services DMV. For mssql-server service in Linux, you can use sudo systemctl status mssql-server. Please refer to Troubleshoot SQL Server on Linux.

select servicename,status_desc from sys.dm_server_services

servicename                               status_desc
SQL Server (MSSQLSERVER)	          Running
SQL Server Agent (MSSQLSERVER)	          Stopped

[Hayorti]

Yes, I'm aware of the WMI requirement, but that knowledge hasn't brought me any closer to a solution. Both MSSQL services are running, as is the WMI service, and the latter is definitely returning data, but because I'm using SQL authentication, it's not like I can grant the SSMS login user remote access to WMI.

[banerjeeamit]

We are looking into how we can make the status show up in SSMS and other tools that we have which depend on components that depend on Windows components. For now, we recommend that you use systemctl commands to query the service status.

[Hayorti]

Thanks for the info, @amitmsft. To clarify, though, I'm running it in a Windows Server 2019 container, not a Linux one. Any idea when you might have a solution, or where it would be posted once you do?

[banerjeeamit]

Thanks for the clarification. We are looking into this scenario. We will post on our SQL Server team blog once we have concluded on the official support story.

[Chesky7]

Having the same issue on a clustered SQL server, one node doesn't have the blue question mark and the other does, both servers have wmi services on and firewall off.