Check the SQL Server address and account authentication details have been entered correctly

Use the SQL Server connection details dialog in the Spotlight Cloud Diagnostic Server Application to run these checks.

SQL Server address

Check the address is a valid server name, server instance name or IP address. If aliases are used then an incorrect alias on the Spotlight Cloud Diagnostic Server host can cause connections to fail. Check the alias name of the instance. For a Microsoft Cluster Server (MSCS) use the virtual name of the cluster.

Check the default ports used by the SQL Server instance.

Default instances typically use port 1433. Some installations use a non-standard port (other than 1433) for running SQL instances.

Named instances can be configured to use dynamic ports or a static port. The underlying network libraries query the SQL Browser Service running on your SQL Server machine via UDP port 1434.

Try to identify the default port and IP address of the SQL Server instance. To do this, open the SQL Server Configuration Manager | SQL Server Network Configuration | Protocols for {Instance Name} | TCP/IP Properties option

Try adding the port number to the Address line in the SQL Server connection details dialog in one of the following formats. Prefix the port number with the server name or IP address.
server name, port number
IP address, port number

Authentication

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.

If you are experiencing connectivity issues it is often easier and more straightforward to fill in the User and Password fields with a SQL Server login (such as ‘sa’) than Use Diagnostic Server credentials.

Connection

If the SQL Server instance is hosted within Microsoft Cluster Server (MSCS) then in the dialog to manage SQL Server connections:

  • Ensure the Windows Server host is selected as Cluster (monitor active node).
  • Verify each Windows node is in the list of Windows Server connections. 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.

Connection to the SQL Server instance

Verify the SQL Server instance fulfills the requirements of Spotlight Cloud.

For named instances the Microsoft SQL Browser Service must be installed on the SQL Server host. Check the SQL Browser Service has started on the SQL Server host. Make sure UDP port 1434 is not blocked by firewall.

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.

Try an external tool such as Telnet to test the connection across the network. Install Telnet using Control Panel | Programs and Features. Open a Command Prompt window and enter:
Telnet <server name> <port number>

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.

From the computer hosting the Spotlight Cloud Diagnostic Application, open SQL Server Management Studio. In the Connect to Server window, click the Options button. From the Network Protocol drop down list select the “TCP/IP” option. Test the connection using the same account used in Spotlight Cloud.

From the computer hosting the Spotlight Cloud Diagnostic Server Application, open the Control Panel | Administrative Tools, launch ODBC Data Sources (64-bit). Click Add and select the SQL Server Native Client. Enter SQL Server information and click Next. Enter account Login ID and password using the same account used in Spotlight Cloud. Click “Test Data Source” to test the connection.

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.

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.

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).

Additional connectivity references

Contact Support

Connect with our team of technical experts