Connection errors adding or connecting to a SQL Server agent (4301363)

Users may experience connection errors when attempting to add or connect to SQL Server agents in Foglight. These errors prevent successful monitoring and data collection from the SQL Server instance. 

Network error IO Exception: Connection refused

[SQL SERVER Error Code = 0. SQL State : 08S01]

or

Failed to establish a SQL-Server JDBC connection protocol to Host [MYHOSTNAME\DEFAULT_INSTANCE], user account [MYDOMAIN\MYUSER] . Reason : [[Foglight][SQLServer JDBC Driver]Error establishing socket to host and port: MYHOSTNAME:2033. Reason: Connection timed out: connect- Profile:MSSQLProfile{host='MYHOSTNAME', instance= 'DEFAULT_INSTANCE', username='MYDOMAIN\MYUSER', authType= 'WINDOWS_CUSTOM', port= '2033' useNTLMv2= 'true', socketTimeout= '900', secureConnection= 'OFF' }]. . [SQL-Server Error Code = 0. SQL state : 08001]

Cause 1:
Incorrect or insufficient SQL Server credentials used during agent setup.

Cause 2:
Network connectivity issues between the Foglight Agent Manager (FglAM) and the SQL Server host.

Cause 3:
SQL Server instance is offline or not reachable.

Cause 4:
Required ports for SQL Server communication are blocked by firewalls.

Cause 5:
Agent Manager service is not running or experiencing stability issues.

Cause 6:
Agent adapter for SQL Server is disabled in Foglight.

Cause 7:
Insufficient permissions on the SQL Server for the agent login account.

Cause 8:
Incorrect SQL Server instance name or hostname specified.

Resolution 1:
Verify and use correct SQL Server credentials with sufficient permissions to connect and collect monitoring data.

Resolution 2:
Check network connectivity between the FglAM server and the SQL Server host. Verify DNS resolution and test connectivity (e.g., ping, telnet).

Resolution 3:
Ensure the SQL Server instance is online and accepting connections.

Resolution 4:
Confirm that firewall rules allow traffic on SQL Server ports (default TCP 1433) between FglAM and SQL Server.

Resolution 5:
Verify that the Foglight Agent Manager service is running on the server and restart it if necessary.

Resolution 6:
In Foglight console, navigate to Administration > Agents > Agent Adapters and confirm that the SQL Server agent adapter is enabled.

Resolution 7:
Confirm the SQL Server login account has the necessary permissions, such as VIEW SERVER STATE and required roles.

Resolution 8:
Double-check the SQL Server instance name and hostname specified during agent creation for accuracy.

Resolution 7

1. Open the Configuration Manager on the remove SQL Server host
   Start | Microsoft SQL Server | Configuration Tools | SQL Server Configuration Manager
2. From the left hand tree select SQL Server Network Configuration | Protocol for {INSTANCENAME} | TCP/IP, right click and enable this.
3. Double click the TCP/IP entry and click on the “IP Addresses” Tab
4. Enter TCP Port value (for example 1433 is the default port) then click OK
5. From the left hand tree, choose SQL Server Services | SQL Server ({INSTANCENAME}), right click and select restart.

Resolution 8

Enable the SQL Server Browser Service to discovery SQL instances that use dynamic ports.