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.
- Test connection succeeded: self-explanatory.
- 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.)
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.