AppInsight for SQL credential permissions

I am confused about the permissions needed for AppInsight for SQL in SAM. The credential I assigned to the template works for both "Auto Detect" and "Windows credential", but fails when I choose the "SQL Server credential" option. So, why do the majority of the components have a green status? They are mostly custom performance counter types, but regardless, if the credential fails as a SQL Server cred, shouldn't all components be in unknown (gray) status?

Help me understand this please. Thank you.

  • When you authenticate against SQL you can either use a Windows/ActiveDirectory account OR a SQL account(account built into SQL). That's how SQL works. 

    When you choose "auto detect" Orion tries both methods to see if any of them works. So you probably have a proper windows account as it works with auto and windows. 

    Why does some values turn grey? Can be for several reasons. Can be due to lack of permissions but most likely not. Can be that you are monitoring an availability group and some databases are not readable on this node. But it can also be that some performance counters just don't work on that SQL server, they got corrupt. Happens sometimes. You can try with a normal "Performance monitor" on the SQL server and see if the counters work with that to start with. If not there are guides online to rebuild them.

    Hope above helps you some.