-
Title
Cannot make SQL Server connection - Troubleshooting SQL Server Connection Errors -
Description
SQL Server connection errors can include the following:
"Monitored Server - SQL Server Connection Failure Cannot connect to SQL Server instance '<instance_name>' : Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. : The wait operation timed out [0xFFFFFFFE]"
"Monitored Server - SQL Server Connection Failure Message: "Cannot connect to SQL Server instance '<your_instance>' : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) [0x80131904]"
"Monitored Server - SQL Server Connection Failure Message: Cannot connect to SQL Server instance '<your_instance>' : A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)"
"Monitored Server - SQL Server Connection Failure: Connection does not exist"
"Login failed for user"
"Timeout expired"
"No such interface supported"
-
Cause
SQL Server connection cannot be established. -
Resolution
Check the SQL Server address and account authentication details have been entered correctly:
Open the Spotlight Cloud Diagnostic Server application, and use the Connection Properties window of the Connections icon to run the Spotlight checks:
SQL Server Address:
1. In Spotlight, confirm the address is a valid server name, server instance name or IP address. If aliases are used, then an incorrect alias on the client machine can cause the connections to fail. Check the alias name of the instance. For a Microsoft Cluster Server (MSCS) use the virtual name of the cluster.
2. Identify the default port and IP address of the SQL Server instance: Open SQL Server Configuration Manager | SQL Server Network Configuration | Protocols for {Instance Name} | TCP/IP option
3. In Spotlight, add the port number to the Address line of the connection:
server name, port number
Or
IP address, port numberAuthentication:
1. In Spotlight, uncheck/disable the ‘Use Windows Authentication (using Diagnostic Server credentials)’ option. Connect using a SQL Server authentication method account by entering the User and Password fields with an account of sysadmin server role.
Check SQL Server availability, requirements and firewalls:
Use external tools to test the connection:1. On the host of Spotlight Diagnostic Server, open SQL Server Management Studios. In the Connect to Server window, click Options button. From the Network Protocol drop down list choose ‘TCP/IP’ option. Test the connection using the same account used in Spotlight.
2. From Control Panel | Administrative Tools launch ODBC Data Sources (64-bit). Click Add and choose SQL Server Native Client. Enter SQL Server information and click Next. Enter account Login ID and password using the same account used in Spotlight. Click ‘Test Data Source’ to test the connection.
3. Use an external tool such as Telnet to test the connection across the network. Install Telnet using Control Panel | Programs and Features. From Command Prompt window and enter: Telnet <server name> <port number>
4. For named instances check the SQL Browser is already started on the SQL Server instance host. Check if the UDP port 1434 is not blocked by firewall.
See Troubleshoot Connecting to the SQL Server Database Engine and SQL Connectivity troubleshooting checklist for further troubleshooting steps.
Application Conflict Issues:
Spotlight on SQL Server Enterprise cannot be running in the same server as Spotlight Cloud
Check Windows Services.msc console and ensure Spotlight Diagnostic Server service is not running. If needed, install Spotlight Cloud on a different server