While monitoring the SQL Server connection the SQL Server Connection Failure alarm will clear automatically for an error that is due to the service being down or network problems and the error condition goes away.
Check the SQL Server address and account authentication details have been entered correctly
Use the Spotlight Cloud Diagnostic Server Application to run these checks. Find out more how to Manage SQL Server Connections using the Spotlight Cloud Diagnostic Server Application.
SQL Server address
Check the address is a valid server name, server instance name or IP address.
For a Microsoft Cluster Server (MSCS) use the virtual name of the cluster.
If the SQL Server instance is hosted on a Windows Server and UDP port 1434 is closed then the port number must be included in the SQL Server address.
The connection will fail if the account permissions are insufficient to allow Spotlight Cloud to collect the data it needs.
When Use Diagnostic Server credentials is selected then the Windows Server account running the Spotlight Cloud Diagnostic Server (as a Windows service) is used to connect to the SQL Server instance. Ensure this account is trusted by the SQL Server instance.
When filling in the User and Password fields, ensure the database user has sufficient account permissions to retrieve performance data from the SQL Server instance and host by WMI. Typically the account will be a member of the sysadmin server role. It could be a SQL Server login (such as ‘sa’). Alternatively an account can be configured with the necessary privileges as documented by Grant account permissions.
Verify the SQL Server host is in a domain. Spotlight Cloud cannot monitor a SQL Server database when the Windows Server host is in a workgroup.
If the SQL Server instance is hosted within Microsoft Cluster Server (MSCS) then ensure the Windows Server host is selected as Cluster (monitor active node). Spotlight Cloud uses the current host node name to select the operating system connection. Therefore each Windows node in the cluster must be monitored by Spotlight Cloud. Verify each Windows node is in the list of Windows server connections.
SQL Server availability
Verify the SQL Server instance is available and accessible over the network. Use a tool other than Spotlight Cloud such as Microsoft’s SQL Server Management Studio or sqlcmd to connect to the SQL Server instance. This will help you determine if the issue is with Spotlight Cloud’s ability to connect to the SQL Server instance or with any any/every tool’s ability to connect to the SQL Server instance.
SQL Server Connection Requirements
Verify the SQL Server fulfills the requirements of Spotlight Cloud.
SQL Server Firewall Connectivity
Ensure ports are open on the SQL Server instance as outlined in the Microsoft KBase article that describes SQL Server firewall connectivity issues: http://msdn.microsoft.com/en-us/library/cc646023.aspx.
Microsoft Data Access Components (MDAC)
If Spotlight Cloud cannot connect to the SQL Server instance you may need to upgrade Microsoft Data Access Components (MDAC) on the Spotlight Cloud Diagnostic Server host. More information is available from the Microsoft Download Center (search for MDAC).
Connection to the SQL Server host (Windows Server)
The Spotlight Cloud Diagnostic Server uses WMI queries to retrieve performance counter information from the Windows server host. Verify WMI is working and returns data properly. See Troubleshooting WMI.
While monitoring the SQL Server connection, if WMI queries fail then a Windows Connection Failure alarm is raised on the third successive failed attempt, not on every failed attempt.
Verify TCP port 135 is open on the Windows Server host.