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.
Enter your Data source name and then the server you want to connect to.
Choose which type of authentication you wan to use – either Windows authentication which uses the currently signed on user, or SQL server authentication.
You can next through the following couple of screens. Finally select to Test Data Source. You should see a “TEST COMPLETED SUCCESSFULLY” message displayed.
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
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.
Once you double click, the Data Link Properties box opens. Here we can populate our connection information including the server name, user 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.
When you click the Test Connection button, you should see the “Test connection succeeded” as the result.
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!
