Skip to main content
Delphix

KBA1751 How to Resolve "Failed to query instance" and "Login timeout expired" Errors During SQL Server dSource and VDB Operations

 

Applicable Delphix Versions

Major Release

All Sub Releases

5.2 5.2.2.0, 5.2.2.1, 5.2.3.0, 5.2.4.0, 5.2.5.0, 5.2.5.1

5.1

5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1, 5.1.9.0, 5.1.10.0

5.0

5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1 ,5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4

4.3

4.3.1.0, 4.3.2.0, 4.3.2.1, 4.3.3.0, 4.3.4.0, 4.3.4.1, 4.3.5.0

4.2

4.2.0.0, 4.2.0.3, 4.2.1.0, 4.2.1.1, 4.2.2.0, 4.2.2.1, 4.2.3.0, 4.2.4.0 , 4.2.5.0, 4.2.5.1

4.1

4.1.0.0, 4.1.2.0, 4.1.3.0, 4.1.3.1, 4.1.3.2, 4.1.4.0, 4.1.5.0, 4.1.6.0

4.0

4.0.0.0, 4.0.0.1, 4.0.1.0, 4.0.2.0, 4.0.3.0, 4.0.4.0, 4.0.5.0, 4.0.6.0, 4.0.6.1

3.2

3.2.0.0, 3.2.1.0, 3.2.2.0, 3.2.2.1, 3.2.3.0, 3.2.4.0, 3.2.4.1, 3.2.4.2, 3.2.5.0, 3.2.5.1, 3.2.6.0, 3.2.7.0, 3.2.7.1

Issue

When attempting to add an Environment, or when performing operations on a dSource or VDB, the Delphix Engine may report warnings or errors that indicate that it is unable to connect to the SQL Server instance.

These errors may be reported as:

WARNING: Failed to query instance "<INSTANCE NAME>" on host "<HOSTNAME>".

Make sure that the SQL Server instance is listening to local TCP connections on port "<PORT>". If the port has changed, refresh environment "<HOSTNAME>". Also make sure that the Delphix Connector service is running on host "<IP ADDRESS>", and the OS user "<DOMAIN\USER>" has the required privileges on the instance.

or:

Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : TCP Provider: No connection could be made because the target machine actively refused it.
.
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..
die : Error: attach_vdb failed
At C:\Program Files\Delphix\DelphixConnector\4206020e-3896-b844-e150-5a61861b4c
45-vdb-306\SCRIPT\AttachVDB.ps1:23 char:12
+         die <<<<  "$($args[0])"
    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorExcep
   tion
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorExceptio
   n,die

While they may occur during any operation, the errors are most likely to occur when an Environment is initially added.

Troubleshooting

There are several possible reasons that these errors may occur. Common causes include:

  • The SQL Server Instance is not running
  • The SQL Server Instance is not configured to allow TCP/IP connections
  • There are firewalls in place, preventing connections to SQL Server
  • The SQL Server configuration is causing execution of sqlcmd to fail

The Resolution section describes each of these issues, and provides recommended steps to resolve them.

The warning or error provided by the Delphix Engine may provide more information on which SQL Server Instance is involved in the failure. If the affected Environment has been added as a Cluster, the warning or error may also provide detail on which node of the cluster was being accessed when the failure occurred.

Before proceeding with troubleshooting, try connecting to the affected SQL Server instance using your normal SQL Server tools (e.g. SQL Server Management Studio). The success or failure of this connection may help you rule out some possible causes.

Resolution

The SQL Server Instance is not running

In some cases, SQL Server will not be responding to login attempts because the SQL Server Instance is not running, perhaps due to failure or scheduled maintenance.

This can be verified by opening the Services control panel on the affected Source or Target environment.

In the following example, the SQL Server Instance SQL2008 is not running.

2018-07-26_13-52-41.png

To resolve this issue: Start the affected SQL Server instance.

The SQL Server Instance is not configured to allow TCP/IP connections

Some editions of SQL Server ship with TCP/IP intentionally disabled, or TCP/IP may have been disabled for the SQL Server Instance because it was not previously required.

To confirm whether TCP/IP is correctly configured for a SQL Server Instance, connect to the affected Source or Target environment and open the Sql Server Configuration Manager. In the tree that appears, expand the SQL Server Network Configuration object and select Protocols for <Your Instance>.

In the following example, TCP/IP has been disabled for the SQL2008 instance.

2018-07-26_14-01-14.png

To resolve this issue: Right-click "TCP/IP', select Properties, and change Enabled to "Yes". You will need to restart the SQL Server Instance before the changes take effect.

There are firewalls in place, preventing connections to SQL Server

Once you have verified that the service is running and listening for TCP/IP connections, it is important to verify that your Windows Server and your network devices are allowing connections between the Delphix Engine and SQL Server.

Host-based firewalls can be checked using the Windows Firewall with Advanced Security application.

To ensure that the appropriate rules are in place with other network devices, it may be necessary to engage your Network or System administration teams.

A list of ports that are used by the Delphix Engine is available in our document Network Access Requirements for SQL Server. Please note that you may need to open additional ports for "Named Instances" of SQL Server or if SQL Server has been configured to run on a non-standard port.

To resolve this issue: Work with your System and Network administrators to verify that the appropriate access is in place.

The SQL Server configuration is causing execution of sqlcmd to fail

In some cases, a problem with the server's configuration prevents the sqlcmd application from loading its required libraries. This, in turn, will cause some operations initiated by the Delphix Engine to fail.

To verify whether this is an issue, launch PowerShell from the affected Source or Target host, and attempt to run sqlcmd. A "Failed to load resource" error indicates that this host may be misconfigured.

Windows PowerShell
Copyright (C) 2009 Microsoft Corporation. All rights reserved.

PS C:\> sqlcmd
Failed to load resource file SQLCMD.rll.

To resolve this issue, review our more detailed Knowledge Base article on this subject: KBA1263 Troubleshooting SQLCMD errors during Microsoft SQL Server (MSSQL) environment discovery.

External Links

Default SQL Server Network Protocol Configuration (Microsoft) describes which editions of SQL Server ship with TCP/IP Enabled by default.