How to Test SQL Server Connectivity Using a UDL File

SUMMARY

It may become useful to test connectivity to a local or remote SQL Server to verify the connection, or credentials in use. This document was designed to explain the process of testing the SQL connection using a common, and non-software specific method.

APPLIES TO

• Microsoft SQL Server

PROCEDURE

1. The first thing to confirm is that you can see/edit file extensions. Open an instance of Windows File Explorer, click the “View” tab > Options > “Change Folder and Search Options”

2. When the “Folder Options” window opens, select the “View” tab. In the “Advanced settings” section take note of the “Hide extensions for known file types” option.  If it is checked, then un-check it, select “Apply” and then “OK.” If it is already unchecked, then simply select the “Cancel” button.

3. On a blank area of the desktop, right-click, hover over “New”, and select the “Text Document” Option

4. The new document will automatically be in rename mode. Name the document “test.udl” and then hit the “Enter” key on your keyboard.  You will get a prompt asking you to confirm the change to the file’s “type”. Click on the “Yes” button to proceed.

5. You can confirm that the change was successful by simply viewing the file icon. It should have changed to look like the following image.

6. Double-click on the new file. When it opens, select the “Provider” tab.
7. In the “Select the data you want to connect to:” list, select the “Microsoft OLE DB Provider for SQL Server” option and click “Next”.

8. You should now be back in the “Connection” tab. Enter the node name for the system with SQL Server that you want to try to connect to, or select it from the drop-down list (this can be local or remote). Then enter valid user credentials or choose Windows Integrated, and finally select the “Test Connection” button.
   NOTE: It is not necessary to select a specific database (item 3 in the “Connection” tab). Simply enter data for items 1 & 2, and then skip directly to the “Test Connection” button.

9. If the connection was successful, then you should receive a confirmation pop-up similar to the following: