Simple ways to test SQL connectivity

While installing Configuration Manager 2012 R2, you may have a problem connecting to the remote SQL server.  Here are some ways you can use to test remote SQL connectivity from your Primary SCCM server:

  1. Telnet to the SQL server’s listening port on 1433.  This approach will require a telnet client.  To install the native telnet client on Windows 7/8.1, run the command "OptionalFeatures.exe" and then place a checkmark next to “Telnet Client” and click on OK.
    To use telnet.exe to connect to the SQL server’s port 1433, open a command prompt and type "telnet.exe <sqlserverhostname_or_ip> 1433".  If connection fails, troubleshoot your SQL server accordingly.  A successful connection will appear as a blank screen:
  2. Create an ODBC System DSN to see if connection is successful.  To add a “System DSN” open the ODBC Data Source Administrator by running the following command "C:\Windows\System32\odbcad32.exe" (64-bit) or "C:\Windows\SysWOW64\odbcad32.exe" (32-bit).  Then click on the “System DSN” tab and click on “Add…”.
    Select “SQL Server” and click on Finish.
    In the “Create a New Data Source to SQL Server” window, name the data source anything you want as we won’t be saving this connection – it’s just a connectivity test.  In the “Server” field enter your SQL server’s hostname or IP.
    Leave the next screen on defaults and continue.
    Again, leave the next screen on defaults and continue.
    The next screen you can also leave on defaults and click on Finish.
    In the configuration summary window, click on “Test Data Source…”.
    If test was successfull, you will see the below message.
  3. Last but not least and probably the less intrusive way of testing SQL connectivity is by use of Universal Data Link files (.UDL).  This method does not require the installation of a telnet client or has as many steps as the “System DSN” method and support for this method is built into every windows version.  You can delete the file afterwards to “clean-up” the system.  Right-click anywhere on your desktop and select “New”, then “Text Document”.
    Name the text document anything but make sure to change the “.TXT” to “.UDL”.  Then double-click on the resulting file.
    Double-click on the .UDL file and type the hostname or IP of your SQL server, then select a user account with permission to the SQL server and select a database.  Then click on “Test Connection”.
    If connectivity test was successful, you will receive the below message:

This isn’t an exhaustive guide to testing SQL connectivity.  Your mileage may vary depending on what the root cause of your connectivity issue, but these steps will certaintly start you off in the right direction.

Leave a Reply