ComputersSQL

Three lesser known ways to test SQL connectivity

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

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

sqlcon02

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

sqlcon03

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

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

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

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

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

sqlcon10

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!

Subscribe to VirtualizationHowto via Email 🔔

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Brandon Lee

Brandon Lee is the Senior Writer, Engineer and owner at Virtualizationhowto.com and has over two decades of experience in Information Technology. Having worked for numerous Fortune 500 companies as well as in various industries, Brandon has extensive experience in various IT segments and is a strong advocate for open source technologies. Brandon holds many industry certifications, loves the outdoors and spending time with family.

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.