I had a new SSIS package that was ready to deploy to production. It was pulling data from Oracle and this was the first package being placed into production that was using Oracle as a source. Arrangements had been made to have the Oracle OLE DB drivers installed on the production SQL Server as the performance was determined to be better that the OLE DB driver Microsoft provided at the time. On the first run of the package in production, the Oracle connection failed validation with a very useless error message, Connection failed validation. Why? The production server did not have any client tools to test connectivity as the system administrator had merely installed the drivers. As this was financial institution and the server was locked down, the SQL Server service account was setup with minimal rights. I sat down with the production DBA to troubleshoot the issue. Fortunately we were able to login interactively using the SQL Server service account. We created a UDL file to test whether a connection could be made to the production Oracle server. The first thing we checked was the Provider tab which verified that the Oracle OLE DB driver was indeed installed. We then attempted to create a connection and an error was raised. The error showed that the SQL Server service account did not have rights to the directory where the Oracle driver was installed and could not read the tnsnames.ora file. We contacted the system administrator to grant the directory rights to the SQL server service account and the connection was able to be made on the next test. The SSIS package ran successfully as well.
Let’s take a closer look at UDL files.
UDL files are useful to:
- troubleshoot connectivity issues.
- prove that a computer can connect to a database server.
- determine if the OLE DB driver you need is installed on the machine.
- generate a connection string.
A UDL file can be created two different ways. If you have the “Hide extensions for known file types” option turned off in explorer, the UDL file can be created directly from the Desktop. Right click on the Destop and select New->Text Document. Rename the new file test.udl. Windows will pop the warning below. Click Yes.
If you haven’t seen it before, here is the “Hide extensions for known file types” option.
The second way is to open Notepad and save an empty file to the Desktop named test.udl. The file icon should look like this on new versions of Windows.
Here is the older version of the icon that would appear on Windows XP/2003.
Double click the newly created icon and the Data Link Properties dialog will be displayed.
The Connection tab is ready to create a connection to a SQL Server by default. In order to create a connection using a different driver, click on the Provider tab and select the driver.
Now when the Connection tab is selected, notice how the UI has changed to provide the appropriate options for the new driver.
The Advanced tab allows additional connection properties to be set.
The All tab shows all of the properties that can be set for the chosen provider. There are properties on this tab that may not be in the documentation provide by the vendor.
Now that we have seen all this fancy UI magic the question is “What is a UDL file”? It is just a test file with a connection string. Windows launches the UI based on the file extension.
Let’s create a UDL using the SQL Server OLE DB driver with windows authentication, pointing to the AdventureWorks database and with an application name of “Test UDL”. Here are the results.
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=AdventureWorks;Data Source=.SQL2005;Application Name=Test UDL
The UDL been around for a while, is available on any windows box and is a quick light weight way to troubleshoot your connections.