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=========

 [oledb]

; 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)  )