Troubleshooting issues while adding an MS SQL Monitor

Troubleshooting issues while adding an MS SQL Monitor

1. Ensure that the SQL Server is running and accessible.

Try connecting to the SQL Server instance via SQL Server Management Studio or DBVisualizer using the same credentials. This step helps diagnose connection issues, ensuring that both the server is reachable and the authentication details are correct. If you can successfully connect to these tools, it indicates that SQL Server is up and running correctly.

2. Check if the hostname or IP address is resolving correctly from the Applications Manager machine.

Warning Error message: The connection to the host givenhost, named instance givenhost, failed.Error: ' java.net.SocketTimeoutException' / The connection to the given host, named instance, and authType failed.Error: ' java.net.UnknownHostException'.
Open Command Prompt and execute the following commands:
  1. nslookup <hostname_or_ip> - This command returns the domain name or IP address, indicating whether the Domain Name System (DNS) can resolve the hostname to an address and vice versa.
  2. ping <hostname_or_ip> - This command is used to test the connectivity between your computer and a specified host or IP address.
If the nslookup and ping commands fail to resolve, ask the user to resolve the DNS or network connectivity issue. After resolving these issues, ask them to try adding the MS SQL monitor in Applications Manager with the correct details.

3. Check if the port can be accessed via telnet from the Applications Manager machine.

Open Command Prompt and execute the following command:
Quote
telnet <hostname_or_ip> <port>
This command verifies network connectivity to a specific port on a host. 
Notes
Note: If the connection is successful, you will see a blank screen or a prompt indicating the port is open. If unsuccessful, you'll receive an error message, indicating the port is closed or inaccessible.

4. Check the SQL Server Configuration Manager.

WarningError message: The server <instance> is not configured to listen with TCP/IP. 

Idea
Solution: Ensure that TCP/IP is enabled for SQL Server.
Follow the steps below to ensure that TCP/IP is enabled for SQL Server:
  1. In the SQL Server Configuration console, click on SQL Server Network Configuration.
  2. Click on Protocols for MSSQLSERVER/Protocols for SQLSERVER2016 (based on the required instance).
  3. In the Protocol Name column, ensure the TCP/IP status is 'Enabled'.
  4. If the status is set to 'Disabled', double-click on TCP/IP and switch the status from Disabled to Enabled.
  5. Restart the SQL Server instance.
After enabling TCP/IP, ask them to try adding the MS SQL monitor in Applications Manager with the correct details.

5. Review potential network issues

Warning
Error message: Network error IOException: Could not create socket
Follow the steps below:
  1. If the SQL Server is on a different network, ensure there are no network or VPN issues affecting connectivity.
  2. Verify if the SQL Server has multiple NICs, with one configured to use DHCP and another with a static IP.
  3. Confirm whether Applications Manager resolves the hostname to the IP address assigned by DHCP (Dynamic Host Configuration Protocol), which may change with DHCP lease renewal or server reboots. This could lead to encountering the error 'Network error IOException: Could not create socket'.

6. Troubleshoot the "SQL Named Instance" adding Issue

Warning

Error Message:

  1. The connection to the host xxxxx, named instance yyyy, failed. Error: ' java.net.SocketTimeoutException' / The connection to the host xxxxx, named instance authTypeEXPRESS failed.Error: ' java.net.UnknownHostException'.

  2. Unable to get information from SQL Server.

7. Check "Force Encryption" Option

WarningError message: I/O Error: DB server closed connection

Idea

Solution:  If the "Force Encryption' option is "Yes"  for SQL Server,  select the "Force Encryption" option in the add monitor page and try adding it.

Follow the steps below to check the "Force Encryption" option in SQL Server:

  1. Follow the steps below to ensure that Force Encryption is enabled for the SQL Server:
  2. In the SQL Server Configuration console, click on SQL Server Network Configuration.
  3. Right-click on Protocols for MSSQLSERVER/Protocols for SQLSERVER2016 (based on the required instance), choose Properties.
  4. In the "Flags Tab", check if the "Force Encryption' option is "Yes", then in Applications Manager, select the "Force Encryption" option in the add monitor page.

Notes
If the issue persists
Please share the following information along with the common details:
  1. Screenshot from SQL Server to confirm Force Encryption is enabled.
  2. Report from the MS SQL Self-Help Tool (refer to the relevant KB for assistance).

8. Connectivity Issue

Warning
Error message:
  1. Login timed out
  2. The TCP/IP connection to the host <hostname>, port 1433, has failed. Error: "Connection timed out: no further information.. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port.  Make sure that TCP connections to the port are not blocked by a firewall.
Solution 1: Resolve the network connectivity issue.
  • If SQL Server is unreachable within the login timeout period (default: 60 seconds), we may receive a login timeout error. This could happen due to the server being unreachable because of network issues, an incorrect hostname, firewall restrictions, or the server being down.
  • Please check the above details to resolve the issue

Solution 2: Switch Driver to MS JDBC

  • If the JTDS JDBC driver is used to connect to SQL Server, it may sometimes cause threads to become blocked, leading to a "Login timed out" error in the SQL Server monitor. Switching to the Microsoft JDBC driver can help resolve this issue.
  • MS SQL > Edit Monitor page > Driver for SQL Server Connection > Choose "Microsoft JDBC Driver" > Test Credential > If test Credential Passed, then Update.
Notes
If the issue persists
Please share the following information along with the common details:
  1. Execute the query below on the Applications Manager query tool and share the result
    1. select mo.RESOURCEID, VERSION, SERVERNAME, DISPLAYNAME, rs.*,r.USERNAME, er.ERROR_MESSAGE from AM_MSSQLDETAILS ms inner join AM_RESOURCECONFIG_EXT rs on ms.RESOURCEID=rs.RESOURCEID inner join AM_ManagedObject mo on ms.RESOURCEID=mo.RESOURCEID inner join AM_RESOURCECONFIG r on r.RESOURCEID=rs.RESOURCEID inner join AM_MONITOR_ERRORS er on er.RESOURCEID=rs.RESOURCEID
  2. Health and Availability History of the issue reported: MS SQL, monitor
  3. Share the results of points 2 and 3
  4. Report from the MS SQL Self-Help Tool (refer to the relevant KB for assistance).

9. Check that permission has been provided for the user: 

Make sure all the permissions have been provided as mentioned on the prerequisites page.
Warning
Error message 1: The server principal 'username'  is not able to access the database 'master' under the current security context. ClientConnectionld.
Info
Cause: The issue occurs when the SQL Server login lacks sufficient permissions to access the master database, typically because the CONNECT permission has not been granted.
Solution:
  • Execute the below query on the SQL Server by replacing the username with the monitoring user:
    • GRANT CONNECT TO username;  (OR)
    • In SQL Management Studio for the user, choose Properties → Securables → Click Add ( under Securables ) → Choose 'All objects of the Types...' → Choose Servers → Choose Grant for 'Connect SQL' permission.
WarningError Message 2:  VIEW SERVER PERFORMANCE STATE permission was denied on object'server,database'master
Info
Cause: The user lacks the necessary permissions to view server performance metrics in the master database. This typically occurs when accessing DMV (Dynamic Management Views) without VIEW SERVER STATE privileges.
Solution:
  1. Execute the query below on the SQL Server by replacing the username with the monitoring user.
    1. GRANT VIEW SERVER STATE TO username;
    2. GRANT VIEW SERVER PERFORMANCE STATE TO username;  (OR)
    3. In SQL Management Studio for the user, choose Properties → Securables → Click Add ( under Securables ) → Choose 'All objects of the Types...' → Choose Servers → Choose Grant for 'View server state ' permission.
    4. In SQL Management Studio for the user, choose Properties → Securables → Click Add ( under Securables ) → Choose 'All objects of the Types...' → Choose Servers → Choose Grant for 'View server performance state ' permission.
Notes
If the issue persists 
Please share the following information along with the common details:
  1. Screenshot of the "Test Credential" result.
  2. Report from the MS SQL Self-Help Tool (refer to the relevant KB for assistance).
  3. Screenshot of the page below by connecting to it using SQL Server Management Studio
10. SQL Server supported TLS version mismatch in the Applications Manager
Warning
Error message: The server selected protocol version TLS10 is not accepted by client preferences [TLS13. TLS12]
Info
Cause: In the latest version of AppManager, support for TLS 1.0 and TLS 1.1 protocols has been disabled, which is causing connectivity issues. However, the SQL Server is still configured to use one of these older TLS versions. To resolve this, we need to update the AppManager configuration to allow connections using lower versions of TLS.  
Solution:
  1. Navigate to AppManager_Home>\working\jre\lib\security\java.security
  2. Search "jdk.tls.disabledAlgorithms" and remove TLSv1, TLSv1.1
  3. Save the changes
  4. Restart Applications Manager.
Notes
Share the details below, along with the screenshots/reports mentioned on each step, based on the error message:
  1. Monitor Information page screenshot.
  2. Edit the Monitor page screenshot along with the test credential result
  3. SQL Server version
  4. Health and Availability History Report
  5. MS SQL Self-Help Tool report based on the error message



          New to M365 Manager Plus?
          New to M365 Manager Plus?
            New to RecoveryManager Plus?
            New to RecoveryManager Plus?
              New to Exchange Reporter Plus?
              New to Exchange Reporter Plus?
                New to SharePoint Manager Plus?
                New to SharePoint Manager Plus?
                  See all integrations
                  New to ADManager Plus?
                  See all integrations
                  New to ADManager Plus?

                    New to ADSelfService Plus?

                    Start your free trial
                    Start your free trial

                      • Related Articles

                      • Real User Monitor (RUM) - Troubleshooting guide

                        If your Real User Monitor has not collected data for an extended period, follow the steps below to troubleshoot the issue. Step 1: Verify the RUM Agent configuration Real User Monitoring requires the RUM Agent to be installed and mapped to ...

                      • DNS Monitor - Troubleshooting

                        Common DNS Monitor Errors and Troubleshooting Guide 1. Host Not Found Description: The DNS server was unable to locate the requested lookup address. Possible Causes: This may happen if the hostname is incorrect, the domain does not exist, or there is ...

                      • Mail Server Monitor - Troubleshooting

                        Common Mail Server Monitor Errors and Troubleshooting Guide 1. Unknown Host Error Description: This error occurs when the mail client cannot resolve the hostname of the mail server to an IP address. The issue typically arises from DNS resolution ...

                      • Troubleshooting URL Monitor

                        When configuring a URL monitor in Applications Manager, you might encounter several errors related to accessibility, configuration, or server-side issues. Below are common errors along with step-by-step troubleshooting instructions to help you ...

                      • Troubleshooting MS SQL login failure

                        Login Failed. The login is from an untrusted domain and cannot be used with Windows authentication. Follow the steps given below the resolve this error. 1. Verify Credentials Ensure that the username and password of the SQL account (domain\username) ...
                      Related Products