Timeless and Handy: The Microsoft Data Link File

Recently I had the need to test connectivity to a newly installed SQL Server instance. My go-to tool for that task is a Microsoft Data Link File. It's really easy to create and configure one. To start, create a new text file in Windows Explorer, change the file extension from ".txt" to ".udl", and open the file. You're off and running in a short time. (MSDN has more details if you need a thorough walk-through.) Oddly, I don't encounter many IT pros that use them any more. Most of the people I've worked with rely on Ping and/or Telnet.

Testing A Connection

There are a few common outcomes when testing a connection with a Data Link. There may be others, but these are the ones I'm familiar with.

1. Test connection succeeded: self-explanatory.
2. SQL Server does not exist or access denied: This could be a number of things. One of these is commonly the culprit:
• The Server\Instance name is incorrect.
• The SQL Server host is not running.
• The SQL Server service on the host is stopped (or paused).
• For a named instance, the SQL Browser service on the host is stopped.
• Requisite ports are blocked by a firewall (Telnet is useful for testing this).
• Other general network issues. (I know, that's a little vague. But I'm far from a networking expert. I'll just leave it at that so I don't embarrass myself.)
3. Login failed for user '<Login Name>': this could be a couple of things. For SQL authentication: the password is wrong, the login is misspelled, or the login doesn't exist in SQL Server. For Windows authentication, it means the login doesn't exist in SQL Server.
4. The account is disabled: self-explanatory.

Connection Strings Made Easy

In addition to testing connectivity to SQL Server, a Data Link file creates a connection string, which might be of use elsewhere. After a connection test is successful, open the Data Link file in a text editor. It will look something like this:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=SqlServerHostName\SQLExpress

Back in my dev days, I used to do this for ADO connection strings in VB6 all the time. For .Net developers, I suppose it's not as useful anymore--the SqlConnectionStringBuilder class is really convenient. But for .Net applications, I do see full connection strings in web.config and app.config files from time to time. Plus, you can use a Data Link file to build connection strings for other OLE DB providers.

I've liked Data Link files for a long time. They're lightweight, easy to use, and effective. I suspect they will be in my tool box for years to come.