Credential alarms fire once every hour for each SQL Server agent, service account may get locked out (4302796)

Credential Management Event alarms fire hourly from SQL Server database agents.

Service account locked out by Foglight even if password is updated

Service account locked out for Foglight very frequently. No credential has been found with an incorrect password.

SQL Server agents are configured to use Error list collection query method: FALLBACK.

The SQL Server agent, from version 5.7.5.1 onwards, is now using Windows Remote Shell command to get the DBSS_Error_Log_List, DBSS_SQL_Server_Error_Log, and DBSS_SQL_Agent_Error_Log collections. Windows Remote Shell Command DCOM connection is a different type of connection that also uses DCOM (WMI)/WinRM apart from the usual WindowsInfoConnection that a DBSS agent makes to get the OS Collections. 

The failed Credential Alarms occur when the DBSS agent cannot make the Remote Shell command and this is a valid alarm (when the 4 WinRM + 1 DCOM(WMI) connection attempts fail). WindowsInfoConnection may very well work but the Remote Shell command may fail, triggering the failed credential alarms.

SQL Server agents may be configured to use FALLBACK Error list collection query method, this attempts to make an OS connection using DCOM (WMI) to collect Error Log data, and then follows with a JDBC connection.

When the sufficient permissions have not been applied, credential alarms from each SQL Server agents may fire hourly.
 

RESOLUTION 1

Changing the Error log collection setting for one or more SQL Server agents using the Edit Properties panel 

1. Navigate to Administration | Agents | Agent Status
2. Select the checkboxes for one or more SQL Server agents that fire credentials alarms once per hour
3. Click the Edit Properties button
4. Click the link Modify the private properties for this agent
5. Change the pulldown for Error list collection query method from FALLBACK to JDBC
6. Click the Save button in the bottom right corner to save the changes
    

Changing the Error log collection setting for all SQL Server agents using the Edit Properties in the UI

1. Navigate to Administration | Agents | Agent Status
2. Select the checkboxes for one or more SQL Server agents that fire credentials alarms once per hour
3. Click the Edit Properties button
4. Click the link Modify the properties for all DB_SQL_Server agents
5. Change the pulldown for Error list collection query method from FALLBACK to JDBC
6. Click the Save button in the bottom right corner to save the changes

Changing the default Error log collection setting for all future SQL Server agents 

1. Navigate to Administration | Agents | Agent Properties
2. Expand the Namespace | Type for DB_SQL_Server
3. Click DB_SQL_Server
4. Change the pulldown for Error list collection query method from FALLBACK to JDBC
5. Click the Save button in the bottom right corner to save the changes

Use a groovy script to change the current error log setting for all SQL Server agents 

Run the following groovy script to change the Error Log collection for all SQL Server agents from FALLBACK to JDBC. Knowledgebase article 4309796 details how to run a groovy script in the Script Console.

srvConfig             = server["ConfigService"];
srvAgent             = server["AgentService"];

namespace = "DB_SQL_Server"
agentType = "DB_SQL_Server"

def updateASP(primaryASP) {
     primaryASP.setValueByString("ErrorLogListQueryMode" , "JDBC");
     srvConfig.saveConfig(primaryASP);
}

primaryASP = srvConfig.getAgentTypePrimaryAsp(namespace, agentType);
updateASP(primaryASP);
def allAgents = srvAgent.findByAdapterAndType("FglAM", "DB_SQL_Server");
allAgents.each {agent ->
     primaryASP = srvConfig.getAgentInstancePrimaryAsp(namespace, agentType, agent.getId());
     updateASP(primaryASP);
}

return true;

Use a groovy script to list the Error Log connection type setting for all SQL Server agents. Knowledgebase article 4309796 details how to run a groovy script in the Script Console.

def out = new StringBuilder();
def agentService = server.get("AgentService"); def configService = server.get("ConfigService"); def agents = agentService.findByAdapterAndType("FglAM", "DB_SQL_Server"); for (agent in agents) {
   def primaryASP = configService.getAgentInstancePrimaryAsp(agent.getAgentNamespace(), agent.getTypeId(), agent.getId());
   if (primaryASP) {
      out.append(String.format("%s, %s: ErrorLogListQueryMode=%s\n", agent.getRemoteClientId(), agent.getName(), primaryASP.getString("ErrorLogListQueryMode")));
   }
}

return out.toString();

Windows registry changes are often necessary on the monitored Windows server for WMI connections from Linux-based FglAMs to be successful. WinRM is the preferred connection mechanism for Windows server monitoring.

STATUS

This issue has been logged as FOG-5937 and will be reviewed by Product Management for an enhancement in a future release of the SQL Server cartridge.
 

RESOLUTION 2

Disable Error List Collections

1. Clone the Collection Details list and uncheck the "Collection Enable" checkbox in the rows for the following collections from the cloned list.
   DBSS_Error_Log_List
   DBSS_SQL_Server_Error_Log
   DBSS_SQL_Agent_Error_Log collections
2. Navigate to the Agent Status page (Administration| Agents | Agent Status).
3. Select one of the SQL Server agents and then select "Edit | Edit Properties".
4. Select "Modify the private properties for this agent".
5. Click the "Clone" button next to the Collection Details list under the Collection Details section, and name the cloned list (test_no_error_log may be an appropriate name).
6. Edit the Cloned list, find the rows for the following collections, uncheck the "Collection Enable" box, and then save the list.
   DBSS_Error_Log_List
   DBSS_SQL_Server_Error_Log
   DBSS_SQL_Agent_Error_Log collections
7. Save the changes to the SQL Server agent's properties.
8. Change the Collection Details list for each of the SQL Server agents to the cloned list created in step 1 and save the properties.
9. Deactivate all of the SQL Server Agents.
10. Unlock the Service account.
11. Activate the SQL Server agents.
12. Wait to see if the service account gets locked.

Additional CRs: FGL-19666, SSDB-14323, SSDB-14324, SSDB-14325

Idea/Enhancement FGSS-I-32 was logged to ask that the "Error list collection Query Method" property value be set to JDBC by default.