Troubleshooting SQLCMD Errors during Microsoft SQL Server (MSSQL) Environment Discovery (KBA1263)

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies

Major Release	All Sub Releases
6.0	6.0.0.0, 6.0.1.0, 6.0.1.1, 6.0.2.0, 6.0.2.1, 6.0.3.0, 6.0.3.1, 6.0.4.0, 6.0.4.1, 6.0.4.2, 6.0.5.0, 6.0.6.0, 6.0.6.1, 6.0.7.0
5.3	5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0, 5.3.8.1, 5.3.9.0
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.2.6.0, 5.2.6.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

Issue

During MSSQL Environment discovery of windows hosts, the job completes successfully but no MSSQL databases are discovered and a warning is thrown like this (or similar):

WARNING: Failed to query instance "MSSQLSERVER" on host "win12lssrc.windows.com".
Make sure that the SQL Server instance is listening to local TCP connections on port "1433". If the port has changed, refresh environment "win12lssrc". Also make sure that the Delphix Connector service is running on host "pvwin12t2.windows.com", and the OS user "Dx_OS_User" has the required privileges on the instance.

If all the suggested actions are checked and validated, the issue can be caused by environmental causes on the windows host running the Delphix Connector service. An example of this type of condition is where the SQLCMD.exe call is failing but you are not able to tell based on the message in the warning. You can test SQLCMD.exe from a Powershell console, running as the Delphix OS user, on the Windows host where Delphix Connector service is running.

An example of what can happen is when the SQLCMD.exe binary is unable to load its SQLCMD.rll resource file due to an upgrade of MSSQL server tools or windows update not completely removing an old SQLCMD (perhaps update was interrupted). Currently, in this scenario Delphix Engine is not returning the vendor message to the warning and suggested actions do not include validating the SQLCMD.exe binary, but this can be checked relatively easy. 

In the scenario where SQLCMD.rll is not accessible to the binary, you will see a message like this when SQLCMD.exe invocation is attempted, as described in the Troubleshooting section:

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

Troubleshooting 

How do you tell this problem is due to SQLCMD.exe?

• It is known the instances were discovered based on the warning message, so the Environment discovery job finds the MSSQL instances, but failing to discover databases.

• You can run the following test yourself, similar to how the Delphix Connector service running on the staging and/or target windows host environment uses Powershell to invoke SQLCMD.exe in order to connect to the MSSQL server instance and run SQL queries against the source instance to discover the database on all instances running on the source environment. If SQLCMD.exe is failing to run due to an environment issue (no access to SQLCMD.rll, for example), you can run a simple test on the DelphixConnector host:
  • Login to the Windows host where DelphixConnector is installed - this is the connector/proxy host you chose when discovering the source environment.
  • Run Powershell command prompt and invoke SQLCMD, no need to run with parameters.
  • If you see an error like this:

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

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

• Now you must investigate the issue in the environment preventing the loading of this resource for SQLCMD
  • Check the PATH to understand where SQLCMD is loading from. You can do this in Powershell by running $env:path
  • Typical path for the SQLCMD.exe utility is:
    • MSSQL 2008: C:\Program Files\Microsoft SQL Server\100\Tools\Binn\

    • MSSQL 2012: C:\Program Files\Microsoft SQL Server\110\Tools\Binn\

    • MSSQL 2014: C:\Program Files\Microsoft SQL Server\120\Tools\Binn\

  • Under the Binn directory is the Resources\1033 directory, containing various resources files for SQLCMD.exe. If SQLCMD.rll is missing from here, the error above occurs. This can happen if something goes wrong when installing updates or upgrade to MSSQL Server tools.

• If other failures occur with SQLCMD.exe, you should be able to determine the root cause based on the error. Of course, if MSSQL Server is not running you might see errors of the sort like:

PS C:\Program Files\Microsoft SQL Server\100\Tools\Binn> .\sqlcmd
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : 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 i
f SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

• • This can occur because by default, if SQLCMD does not specify a non-default MSSQL instance name (default is MSSQLSERVER), then you must provide the instance name in the form of -S Hostname\InstanceName:

PS C:\> sqlcmd -S WIN2012R2STD\SQL2014STD -E
1> select @@servername
2> go
------------------------------------------------------
--------
WIN2012R2STD\SQL2014STD

(1 rows affected)

The objective here is to find out whySQLCMD will not execute. Delphix has no way to determine why this issue occurs, and to test you must run as the Delphix OS user and invoke powershell. If for some reason you ca not login to the machine with Delphix Connector installed as the OS user, you can start a Powershell shell as another user:

PS C:\> Start-Process powershell.exe -Credential "Domain\Delphix_OS_User"
PS C:\>

 

Resolution 

The resolution is dependent on the error message. In the case of a missing SQLCMD.rll file or Resources\1033 directory, you need to work with your Windows administrator to determine the root cause. Sometimes it is simply a Windows path issue which can be corrected by placing the proper directory in the Windows System path:

• The SQLCMD.exe binary is installed in the above mentioned directories such as C:\Program Files\Microsoft SQL Server\100\Tools\Binn\ for MSSQL 2008R2 installation.
• Under Binn there should be the Resources\1033 directory, containing SQLCMD.rll among other resources.
  • If the Resources directory is missing, check the Windows Path to see if there is another Tools\Binn installation, and if it contains the proper resources, testSQLCMD.exe from this directory
  • If the SQLCMD.exe works, you can change the Windows System Path so this directory appears first in the Windows Path.
    • In this case, when Delphix Connector service invokes SQLCMD.exe via a Powershell script, the binary is found and properly executed.
    • Example Path when problem occurs:
      • C:\....;C:\Program Files\Microsoft SQL Server\100\Tools\Binn\;.....;C:\Program Files\Microsoft SQL Server\110\Tools\Binn\;....
      • The 110 directory has the working SQLCMD.exe
      • You will likely need assistance from your Windows Administrator to change the System path, but here are steps to do so:
        • Open the Server Manager utility on the Windows connector/proxy host
        • Click the Computer Name:

 

Click Environment Variables from the Advanced tab

Look for the Path variable under the System section, double click it, and the Edit System Variable window pops up, copy the contents, place in an editor of your choice, like notepad, and make the Path change, placing the 110 directory ahead of the 100 directory.

• Change: 
  • C:\....;C:\Program Files\Microsoft SQL Server\110\Tools\Binn\;.....;C:\Program Files\Microsoft SQL Server\100\Tools\Binn\;....
  • C:\....;C:\Program Files\Microsoft SQL Server\100\Tools\Binn\;.....;C:\Program Files\Microsoft SQL Server\110\Tools\Binn\;.... 
    to 
  • Copy the changed Path to the Path environment variable into the Edit System Variable field for Variable value for the Path and click OK. Now the System Path has been changed.
  • Invoke a new Powershell console and run the SQLCMD.exe test to verify the Path change. 

If this issue is related to something gone wrong with an installation, like MSSQL tools, enlist the assistance of your Windows Administrator to mitigate the problem.