38

The SQL Server instance is accessible and seems to be fine.

Microsoft SQL Server 2016 (SP1-CU2) (KB4013106) - 13.0.4422.0 (X64)
Mar 6 2017 14:18:16 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor)

But what does the white question mark mean?

these icons dont go away when I refresh. I am sysadmin inside sql server and outside I am administrator on that box.

enter image description here

enter image description here

Another thing I noticed, you can see on the picture below. These are 2 different Management Studio sessions.

On the top one, I am logged in as myself, DBA and sysadmin, on the second one I use Management Studio with run as a different user and I use a domain account that I use for the replication, which is not sysadmin.

The second one has the blue icon in this and other servers as well, whilst mine is the normal green one.

enter image description here

Improve this question
1
  • I have a few other servers, but only this one has this feature. nothing changes when I either refresh or hit F5. the service is started. I tend to use windows authentication, but this server in particular allows SQL Auth connections too.
    – Marcello Miorelli
    Jun 19, 2017 at 16:38

4 Answers 4

Reset to default
30

Enabling these Windows Firewall rules did the trick for me

  • Windows Management Instrumentation (WMI-In)
  • Windows Management Instrumentation (DCOM-In)

These two rules are predefined and you can enable them by right clicking and enabling these rules in Windows Firewall advanced settings

I Also opened port 135 in the windows firewall, but I don't know if this was really neccessary.

I now confirmed it for Windows Server 2016 and Windows Server 2019 and SQL Server 2017

Improve this answer
2
  • 1
    It wont work on server 2019 with sql 2016
    – Root Loop
    May 5, 2019 at 1:43
  • 1
    This has worked for me previously, but not now. Also I noticed that DCOM-In is set up as port 135
    – Dave Pile
    Feb 6, 2020 at 9:59
27

I believe the blue question mark indicates that SSMS does not know if the service is running or not, by way of a WMI call. I'm quite certain this icon replaced the blank or white circle icon of prior SSMS versions.

After some digging, it looks like you could run into one or more issues causing this behavior, as follows:

  1. See if TCP port 135 to the server is open or not on your firewall? If it's not, opening it up may resolve this icon, but understand that there are also some special considerations to be aware of when enabling this port. Normally, this isn't a big deal if the server is not exposed to the perimeter of your network, but if your firewall rules are very restrictive or this server sits in a DMZ, this may be something you have to live with.
  2. Your user may not have proper permissions to view the service state remotely. To enable those permissions, follow the instructions here.
  3. WMI services are not running on the server hosting the database engine. Start the WMI services via services.msc, etc.
Improve this answer
1
  • #2 is what was happening in our situation. User was running SSMS as a non-privileged Windows account and connecting to MSSQL using the 'sa' account. Since their non-privileged Windows account didn't have WMI permissions, the blue icon appeared. If they relaunched SSMS as their privileged account (with access to query WMI), it appears correctly as a green play icon.
    – flakshack
    Mar 14 at 20:13
3

Root cause: User Groups doesn't seem to exist on the current server. Thus, sql server error log shows "Login failed for user 'strsDataAndQueues'. Reason: Failed to open the explicitly specified database 'StrsData'"

Solution: Add-on Administrator groups which does not exist on the current server

Steps:

  1. Open Computer Management on both the servers i.e. server A and server B

  2. Click on System Tools, scroll down to Local Users and Groups, Click on Groups

  3. Right click Administrators, and choose Properties

  4. Compare Administrators group between server A and server B

  5. Add-ons Administrator group which is not available in server B

Improve this answer
1

I've also found this issue can be caused by using an FQDN when connecting to the SQL server in SSMS. So if you've tried everything above and its still not working try...

Adding an entry for the SQL server to your hosts file (C:\Windows\System32\drivers\etc\hosts) with the IP address and just the server name, not FQDN. Then try connecting to the SQL server again in SSMS using just the server name and it works as expected.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.