Testing SQL Server connectivity using SQLCMD.EXE

Testing SQL Server connectivity using SQLCMD.EXE

Our admin guide recommends running tests before installation: "We recommend testing remote database SQL server connectivity before beginning an installation." The steps below detail one way to accomplish some connectivity tests.

Using SQLCMD you can use it to test connectivity to a SQL Server instance, to test that you can connect to the host, it is reachable, TCP/IP connectivity is enabled and that you have at least the right needed to connect to the sql server instance. This simple test will not however determine if you have the necessary priviledges needed to connect to a particular named database, create databases or schema or read, insert or modify data.

These steps assume that Windows Authentication will be used to connect to the SQL Server instance. If SQL Server Authentication instead will be used and not Windows Authentication then the steps are slightly different, there is an additional -P parameter needed. See the SQLCMD.exe help for more information.

Steps to test for connectivity

1. Login as the user that will be used for the service account for GMC on the machine where the prospective GMC install would be performed. IMPORTANT: Please make sure you run the test on the server you wish to install GMC on or the Server that already has GMC installed.
2. If SQLCMD is not installed, install it. Use these instructions to do it manually: Manually Installing Microsoft SQL Server 2005 Command Line Query Utility (SQLCMD)
3. Open a new command window. [Start] -> "Run..." -> Open: cmd.exe
4. In the command window run the a sqlcmd to test the connectivity:

To connect using port number follow the general syntax:

sqlcmd.exe -S tcp:<computer name>,<port number> -Q "select getdate()"

Example of successfully connecting to a SQL Server instance running on a computer named "april" that allows connections to the instance on the static port of 1433.

C:\Documents and Settings\mjang>sqlcmd.exe -S tcp:april,1433 -Q "select getdate()"

-----------------------
2009-08-26 00:08:27.847

(1 rows affected)

To connect using a named instance follow the general syntax:

sqlcmd.exe -S tcp:<computer name>\<instance name> -Q "select getdate()"

Example of connecting to a SQL Server instance named "GMC" running on a computer named "mjang-opt745".

C:\Documents and Settings\mjang>sqlcmd.exe -S tcp:mjang-opt745\GMC -Q "select getdate()"

-----------------------
2009-08-26 00:09:54.550

(1 rows affected)

Troubleshooting

This is the error you might see if the SQL Server instance doesn't have TCP/IP enabled for remote connections:

C:\Documents and Settings\mjang>sqlcmd.exe -S tcp:mjang-opt745\GMC -Q "select getdate()"
HResult 0x274D, Level 16, State 1
TCP Provider: No connection could be made because the target machine actively re
fused it.

Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

If you see this error try to run the command without the "tcp:" prefix in front of the computer name; if that is successful then you know that the sql server intance has "named pipes" enabled for remote connections but not "TCP/IP". Have the settings for remote connections on the SQL Server instance changed to enable "TCP/IP" connections as well as "named pipes" using the surface area configuration tool that is installed with the sql server instance.
Example of not specifying forced use of TCP/IP which allows SQLCMD to use named pipes to connect.

C:\Documents and Settings\mjang>sqlcmd.exe -S mjang-opt745\GMC -Q "select getdate()"

-----------------------
2009-08-26 00:09:54.550

(1 rows affected)

GMC requires TCP/IP for its database connectivity to SQL Server because it uses the Microsoft JDBC driver which only supports TCP/IP database connections.

This is the error you might see if the SQL Server instance service isn't running, is fire-walled or an invalid or non-existant computer name was used. (not a very informative error unfortunately)

C:\Documents and Settings\mjang>sqlcmd.exe -S tcp:mjang-opt745\GMC -Q "select getdate()"
HResult 0xFFFFFFFF, Level 16, State 1
SQL Network Interfaces: Server doesn't support requested protocol [xFFFFFFFF].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.