Tag Archives: UDL

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.
    OptionalFeatures.exe
    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:
    telnet1
  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…”.
    odbc1
    Select “SQL Server” and click on Finish.
    odbc2
    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.
    odbc3
    Leave the next screen on defaults and continue.
    odbc4
    Again, leave the next screen on defaults and continue.
    odbc5
    The next screen you can also leave on defaults and click on Finish.
    odbc6
    In the configuration summary window, click on “Test Data Source…”.
    odbc7
    If test was successfull, you will see the below message.
    odbc8
  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”.
    udl1
    Name the text document anything but make sure to change the “.TXT” to “.UDL”.  Then double-click on the resulting file.
    udl2udl3
    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”.
    udl4
    If connectivity test was successful, you will receive the below message:
    udl5

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.

Advertisements