Three lesser known ways to test SQL connectivity

0

In troubleshooting a SQL connectivity issue recently, I tried multiple tests in connecting to SQL to help troubleshoot the issue. In this post, let’s take a look at three lesser known ways to test SQL connectivity including ODBC, a UDL file, as well as the sqlcmd utility. These are lesser known ways than simply using SQL Server Management Studio, but each can prove beneficial when testing and troubleshooting.

Three ways to test SQL connectivity

One of the first lesser known ways to test connectivity is by using an ODBC connection.  The ODBC connection utility is built into Windows and can be launched by typing in odbcad32 at a run or search menu.  Click the System DNS tab and then select Add.

sqlcon01 Three lesser known ways to test SQL connectivity

Enter your Data source name and then the server you want to connect to.

sqlcon02 Three lesser known ways to test SQL connectivity

Choose which type of authentication you wan to use – either Windows authentication which uses the currently signed on user, or SQL server authentication.

sqlcon03 Three lesser known ways to test SQL connectivity

You can next through the following couple of screens.  Finally select to Test Data Source.  You should see a “TEST COMPLETED SUCCESSFULLY” message displayed.

sqlcon06 Three lesser known ways to test SQL connectivity

SQLCMD utility

The SQLCMD utility is also lesser known.  This is installed with SQL Server Management Studio and is a command line way to both connect and execute SQL statements.  Simply open a command prompt and execute the sqlcmd /? to see the usage of the utility.  As you can see, a few common parameters are the -S server, the -U login id and -P password.  However, there are quite a few other parameters that can be used with the utility.

If you don’t specify the -U and -P for user and password, then Windows authentication is assumed and used.  A typical connection test might look like this:

sqlcmd -S TESTSQLSERVER -U sa -P sapassword

sqlcon07 Three lesser known ways to test SQL connectivity

UDL File

A UDL or Universal Data Link file can be used to easily test connectivity.  Simply create a new file and rename it with a .udl extension.  Then double click the file.

sqlcon08 Three lesser known ways to test SQL connectivity

Once you double click, the Data Link Properties box opens.  Here we can populate our connection information including the server nameuser name, and password.  If you select the Use Windows NT Integrated security it will assume credentials of the logged in user.  Once populated you can click the Test Connection button to initiate the connectivity test.

sqlcon09 Three lesser known ways to test SQL connectivity

When you click the Test Connection button, you should see the “Test connection succeeded” as the result.

sqlcon10 Three lesser known ways to test SQL connectivity

Thoughts

Hopefully, the Three lesser known ways to test SQL connectivity will shed light on additional ways to test connectivity to a SQL server either for verification or troubleshooting purposes.  When troubleshooting a connectivity issue, the more tools and angles of testing, the better!  Happy troubleshooting!