The easy way to test SQL connectivity

While I can’t say I have to do it all that often, there are occasional times when I need to test connectivity from a machine back to SQL.  The question then comes up, how to easily get that done?  Installing SQL tools is the most obvious, but not always handy and I really don’t want to be installing SQL tools on every machine I do this from.  The easy answer is to use a UDL file.

How do I use a UDL file?

While you can use a UDL file to test various connectivity scenarios, as an System Center Configuration Manager admin I’m mostly concerned with connections back to the SQL database so I set it up on the provider tab to use the “Microsoft OLE DB Provider for SQL. ”

The next step is to fill out the necessary information on the connection tab.

Test the connection, and troubleshoot as necessary if it fails to connect.

I’m sure you can do all kinds of fancy other things with the tool.. heck, we only used two of the four tabs, but I’m an SCCM guy and not a SQL guru so I leave that for other folks to blog about.

Where do I get this cool UDL file?

I saved the best for last.  This cool tool… you already have it.  It is part of every windows OS that I have used.  Create a simple text file, rename the extension from TXT to UDL, and TADA… you got yourself a handy utility.  Delete the file when you are done to clean-up after yourself.

Credit goes to Faruk Celik for clueing me into this from his blog post: https://blogs.msdn.com/b/farukcelik/archive/2007/12/31/basics-first-udl-test.aspx