Basics first : "UDL Test"

When an application failed with a database/datasource connectivity problem, the first thing to check is if the box really cannot connect to the database/datasource using a “generic” way. The simplest technique is “UDL Test” using the OLE DB providers installed on the box . For example, if the application is failing to connect to a SQL Server 2000 or 2005, jsut follow the steps below :

1) Create a new empty text file like “test.txt” (For example, right mouse click on an empty place on your desktop, select “New” and “Text Document”)

2) Rename the file as “test.udl

  As soon as you renamed the file, text file icon  should change to a UDL icon

3) Double click on test.udl file and you’ll receive a window titled “Data Link Properties

You’ll get all the installed OLE DB Providers on the box when you’ve switched to the Provider tab. If the OLE DB Provider for the database/datasource you’re interested in is in the list, select the OLE DB Provider and click on “Next“and you’ll switch to “Connection” tab and will be ready to play with the OLE DB Provider. The rest depends on the OLE DB Provider you selected.

For example if you selected “Microsoft OLE DB Provider for SQL” , you’ll  see a window like that :

You’ll need the name of the SQL Server (or instance name) you’re trying to connect to the textbox in the 1st part. “Use a specific user name and password” is selected by default in the 2nd part and this means “SQL Authentication”. You’ll need to enter a SQL Server username here (P.S. : It’s a common mistake to type a Windows/Domain user like “mydomain\myuser“. This WON’T work! You can only type a SQL Server Username like “sa” etc. which is called as SQL Server user). If you select “Use Windows NT Integrated security” radio button, then the Username and Password textboxes will be disabled and the credential that you logged on to the machine will be used while connecting to SQL Server. Be sure that your SQL Server is in “mixed” mode (Please refer to SQL Server Books Online for the details )

You can either click on “Test Connection” directly to test the connection or click on the combobox to enumerate the available databases/catalogs on the server.

If you click on “Test Connection“, you should receive “Test connection succeeded” message if you can connect to the SQL Server :

If you click on “OK” and click “OK” again, the information that you entered will be saved into the test.udl file. The UDL file is a simple text file and the magic line is the last line which is the “Connection string” Here’s an axample for a SQL Server OLE DB Provider scenario when “Use Windows NT Integrated security” selected :

=============TEST.UDL file BEGIN=========


; Everything after this line is an OLE DB initstring

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=SERVERNAME

=============TEST.UDL file END===========

Having the connection string from a UDL file

  1) Will prevent you to make typos in the connection string (You’re going to be sure that the connection string is right and working, cause you’ve got “Test connection succeeded” 😉 )

  2) Will help you to “generate” the connection strings which are using different types of OLE DB Provider that you’re not familiar with their syntax. The parameter/value pairs are specific to the OLE DB Provider    manufacturer. Of course, all OLE DB connection strings should have Provider=SOMEABBREVIATONFORTHEOLEDBPROVIDER pair (I can assure you that you’ll never see that long “abbreviation” for an OLE DB Provider :o)  )

Comments (5)

  1. Luisfer says:

    How would it be if a want to test a conection to an SQL Server  but the instance is in other port

  2. Faruk Celik says:


    Let's say your instance is INST2 and running on a machine named "SQLSRV1". You candirectyl type "SQLSRV1INST2" into 1st box (without quotes).

    If this fails, likely that your client machine cannot reach to the SQL Browser service (listening on UDP 1434) of your SQLSRV1 machine. Check if SQL Server Browser service is running on your SQLSRV1 first.

    There's another way also. Let's say you don't know the instance name and your DBA gave the TCP Port of the INST2 instance. Then you can type the below syntax into the 1st box :


    where "nnnn" is the TCP Port of your INST2.

    But as I said, if your SQLSRV1's "SQL Server Browser Service" is running and your client machine can reach to it, you should not need to care about the TCP Port your INST2 instance is listening on.

    Sometimes, going with "tcp:SQLSRV1,nnnn" syntax is not good. Because, by default the named instances are listening on random TCP port "nnnnn" when their SQL Server Engine service started.

    But if your DBA is already fixed the INST1 to listen on a constant TCP Port "nnnn" then, you can go with it.

    Hope that helps

  3. Tim says:


  4. Yashwant says:

    Thank You for nice explanation of UDL test. 🙂