Tools of the Trade: Part I – Universal Data Link Files

  • Article

The other day I was working on an issue with one of our customers.

 

I had been having him run several different tests using a few of the tools that we use on the SQL Developer team on a daily basis. After about the third or fourth diagnostic we ran, he asked me something along the lines of: “how are we supposed to know about all these different tools?”

I didn’t have a good answer. Although many of the tools we use are well known, unless a person spends their days troubleshooting, they are unlikely to be familiar with all of them. I was not aware of most of them before I began working for Microsoft’s Product Support Services, and in the cases where I was familiar with the tool, I did not always know how to get the most mileage out of it.

In this series of posts, we’re going to take a look at some of the tools we commonly use, how to use them, and the types of issues they can be used to resolve.

One of the first things we try to do when troubleshooting a database connectivity issue is to establish just how “broken” things are.

 

In many situations, database connectivity is failing only when used by a specific user, for a specific application, or after taking a specific series of steps. People often are not aware of which is true for them; there are lots of systems out there running only a single program that utilizes a data back-end and if that’s not working properly, how do we know if the failure is system wide or application specific?

Enter the Universal Data Link (UDL) file. UDL files offer a very simple way to test the connectivity of a given Ole DB provider to a data source. The very simple instructions for creating and configuring a UDL file can be found here.

Once you’ve created and opened the UDL, you should start out on the “Providers” tab, and select the provider appropriate for you data source (e.g. “Microsoft Ole DB Provider for SQL Server” or “SQL Native Client” for SQL Server databases). The Connection tab allows you to set provider-specific details for connecting, such as the name of the server, the authentication mechanism, and the initial catalog.

 

If necessary, you can use the All tab to set values for provider specific properties (for example, you can set the Failover_partner for SQL Native Client here to test a Database Mirroring scenario). Once that’s complete, you can click the “Test Connection” button and observe the results.

There are a lot of different deductions you can make based on the result of your test. If the connection is successful, you know at least you are able to connect and authenticate to the datasource from that machine.

 

At this point, you’ll want to take a look at the differences between what your application is doing, and what the datasource is doing. One possibility is that your application is using a different data provider (An ODBC driver? A .Net Data Provider? JDBC? A different Ole DB provider?). Another difference is that often your application runs under the context of a different user account with different permissions (very common for connections made from services such as IIS).

If the connection failed, you’re probably running into a more system-wide issue. You should get a specific error, which will guide your troubleshooting from here. If you see something along the lines of “SQL Server does not exist or access is denied” then you might be a good time to use some other tools to diagnose possible network connectivity issues, like SQL Profiler to audit failed logins, or Netmon to take a look at what traffic is being sent across the wire (there will be posts to come discussing some of these tools).

 

Other errors will suggest different courses of action, for example checking out possible local permissions issues (Filemon, Regmon, ProcessMonitor). The good news is, now you’ve got a quick and easy way to reproduce your issue, without the additional complexity of an entire application.

UDLs are also very useful for constructing Ole DB connection strings. A UDL file is essentially a text file that holds a connection string. If you configure the UDL using the dialog to connect to your data source, you can then open the .udl file using Notepad and copy the connection string for use in your applications.

So, that’s an overview of how we use UDL files. Just about any time you have a database connectivity issue, your best first step is to see if you can connect using a UDL file. This will allow you to better define your problem, which really helps in deciding what your next troubleshooting steps should be. In future articles, we’ll look at some of the tools that you might use to further define your problem, and get closer to a solution.

 

Posted By : Justin Trobec