Troubleshooting MS SQL Monitor Addition Problems
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 connect successfully with these tools, it indicates that the SQL Server is up and running correctly.
2. Check if the hostname or IP address is resolving correctly from the Applications Manager machine.
Error message : The connection to the host givenhost, named instance givenhost failed.Error:'java.net.SocketTimeoutException' / The connection to the given host, named instance authType failed.Error:'java.net.UnknownHostException'.
Open Command Prompt and execute the following commands:
- 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.
- ping <hostname_or_ip> - This command is used to test the connectivity between your computer and a specified host or IP address.
If 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:
telnet <hostname_or_ip> <port> - This command is used to check network connectivity to a specific port on a host.
If the connection is successful, you'll 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.
Error message : The server <instance> is not configured to listen with TCP/IP.
Solution: Ensure that TCP/IP is enabled for the SQL Server.
Follow the steps below to ensure that TCP/IP is enabled for the SQL Server:
- In the SQL Server Configuration console, click on SQL Server Network Configuration.
- Click on Protocols for MSSQLSERVER/Protocols for SQLSERVER2016 (based on the required instance).
- Under the Protocol Name column, ensure that the status of the TCP/IP is 'Enabled'.
- If the status is set to 'Disabled', double-click on TCP/IP and switch the status from Disabled to Enabled.
- 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
Error message : Network error IOException: Could not create socket
- If the SQL Server is on a different network, ensure there are no network or VPN issues affecting connectivity.
- Verify if the SQL Server has multiple NICs, with one configured to use DHCP and another with a static IP.
- 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 "SQL Named Instance" adding Issue
Error Message :
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' .
Unable to get information from SQL Server.
Solution: How to add Named Instance in Applications Manager KB
7. Check "Force Encryption" Option
Error message: I/O Error: DB server closed connection
Solution: If the "Force Encryption' option is "Yes" for SQL Server, select "Force Encryption" option in the add monitor page and try adding it.
Steps to check "Force Encryption option" in SQL Server
- Follow the steps below to ensure that Force Encryption is enabled for the SQL Server:
- In the SQL Server Configuration console, click on SQL Server Network Configuration.
- Right Click on Protocols for MSSQLSERVER/Protocols for SQLSERVER2016 (based on the required instance), choose Properties
- In the "Flags Tab", check if the "Force Encryption' option is "Yes" , then in Applications Manager, select "Force Encryption" option in add monitor page.
Issue Still Persisting
- Please share the following information along with the common details:
- Screenshot from SQL Server to confirm Force Encryption is enabled.
8. Connectivity Issue
Error message:
- Login timed out
- The TCP/IP connection to the host <hostname>, port 1433 has failed. Error: "Connection timed out: no further information.. Verify th 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 network connectivity issue.
If SQL Server is not reachable within the login timeout (default is 60 seconds), we may receive a login timeout error. This could happen due to the server being unreachable because of network issues, 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 .
Issue Still Persisting
- Please share the following information along with the common details:
- Execute the below query on the appmanager query tool and share the result
- 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
- Health and Availability History of the issue reported MS SQL monitor
- Share the results of point 2 and 3
- Report from the MS SQL Self-Help Tool (refer to the relevant KB for assistance).
9. Check Permission has been provided for the user:
- Error message 1 : The server principal 'username' is not able to access the database 'master' under the current security context.ClientConnectionld:
- Cause : The issue occurs because the SQL Server login does not have sufficient permissions to access the master database, usually because the CONNECT permission has not been granted.
- Solution :
- Execute the below query on the SQL Server by replacing the username with the monitoring use:
- GRANT CONNECT TO username; (OR)
- In SQL Management Studio for user Choose Properties → Securables → Click Add ( under Securables ) → Choose 'All objects of the Types...' → Choose Servers → Choose Grant for 'Connect SQL' permission.
- Error Message 2 : VIEW SERVER PERFORMANCE STATE permission was denied on object'server,database'master
- 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:
- Execute the below query on the SQL Server by replacing the username with the monitoring user
- GRANT VIEW SERVER STATE TO username;
- GRANT VIEW SERVER PERFORMANCE STATE TO username; (OR)
- In SQL Management Studio for user Choose Properties → Securables → Click Add ( under Securables ) → Choose 'All objects of the Types...' → Choose Servers → Choose Grant for 'View server state ' permission.
- In SQL Management Studio for user Choose Properties → Securables → Click Add ( under Securables ) → Choose 'All objects of the Types...' → Choose Servers → Choose Grant for 'View server performance state ' permission.
- Issue Still Persisting
- Please share the following information along with the common details:
- Screenshot of the "Test Credential" result.
- Report from the MS SQL Self-Help Tool (refer to the relevant KB for assistance).
- Screenshot of the below page by connecting it using SQL Server Management Studio
10. SQL Server supported TLS version mismatch to the Appmanager
Error message: The server selected protocol version TLS10 is not accepted by client preferences [TLS13. TLS12]
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 :
- Navigate to AppManager_Home>\working\jre\lib\security\java.security
- Search "jdk.tls.disabledAlgorithms" and remove TLSv1, TLSv1.1
- Save the changes
- Restart Applications Manager.
Share the below details along with the screenshots/reports mentioned on each step based on the error message Cx receiving:
- Monitor Information page screenshot.
- Edit Monitor page screenshot along with the test credential result
- SQL Server version
- Health and Availability History report
- MS SQL Self-Help Tool report based on the error message.
New to ADSelfService Plus?
Related Articles
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 ...Real User Monitor (RUM) - Troubleshooting
If the monitor has not polled data for a long time, follow the below steps for troubleshooting. Step 1: Check the RUM Agent configuration Real User Monitor requires the RUM Agent to be installed and mapped to the Applications Manager. Refer this help ...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 ...How is Backup Age calculated for MS SQL DB monitor?
Backup Age displays the number of hours passed since the backup operation was completed. In simple terms, it is the difference between the current time and the time at which backup was performed. Backup Age (hours) = Current Time - Backup Time By ...