Some time ago I was asked to check if there is a possibility to access one of our SQL Server instances from a new hardware we got. That seems to be easy task, right? I thought so. Later, when I log in to the server, I immediately realized it will be not so easy to test connectivity to this SQL Server. That was pure Windows Server installation, without any additional tools. There was neither SQL Server Management Studio nor SQLCMD. In addition I had no Admin rights and was not allowed to install or add any software without official CR. Fortunately my teammate gave me an advice, to use UDL files. I never heard about that before and I was really amazed how easy solution that was.
What is an *.udl file?
UDL extension stands for Universal Data Link. These files are used by Data Link API which exposes a user interface to create and manage OLE DB connections. This functionality was introduced in Windows OS at least in Windows 95, maybe even earlier. That means you can use it on every Windows machine you work on. You no longer need to worry about additional tools.
How to use it?
It is incredibly simple and that is a beauty of this solution.
First step is to create an empty *.udl file. The easiest way is to open the Notepad and save empty file with *.udl extension. To do it:
- Open Notepad
- Go to [File] -> [Save As…]
- Choose where you want to save file
- Change [Save as type:] to “All files”
- Set file name with *.udl extension (i.e. TestConnection.udl)
- Hit [Save] button
- Close Notepad
Second step is to open this newly created file. You can do it just by double-clicking on it. Data Link Properties window will appear.
On the first [Provider] tab you can choose an OLE DB data provider you want to use to test your connection. If you don’t know what to choose than leave the default one.
On the second [Connection] tab you can specify SQL Server name, authentication type, and database name. You can do it exactly the same way as in SQL Server Management Studio.
On the third [Advanced] tab you can specify initialization properties required by selected OLE DB provider. This tab is provider-specific. If you choose provider for SQL Server then major part of this tab will remain disabled, but it will be active when you choose provider for Analysis Services.
On the fourth [All] tab you can specify additional connection properties such as Application name or Fail-over partner.
Third step is to finally test a connectivity. You can do it from second [Connection] tab by hitting [Test Connection] button. That’s it!
Below you can find both results: successful and failed connection test.
I hope you like it and you will find it useful.
Additional MSDN Links:
Data Link API Overview
Configuring Data Controls to Use Universal Data Link (.udl) Files
Microsoft OLEDB Universal Data Link Configuration
One thought on “How to test connectivity to the SQL Server”