May 18, 2014, 9:43 PM — If one of the systems you manage has to connect and get data from a remote system that you don't manage, it's a good idea to have a couple tools on hand that will make it fairly easy to test your connection before you need to integrate the data collection in a more complex procedure. One way to do this is to have a series of tools that you can use from a Windows system that provide an easy and reliable way for you to verify your connection parameters (username, password, port, etc.) and test your database queries. Once you do these things, you can move ahead with more confidence when implementing your database connections on your application. You will have ruled out a number of potential problems. In this post, I describe some of the tools that can help you do this.
Before I begin, let me say that the tools described in this post are not at all specific to testing Unix systems. Instead, they just provide easy ways to test database connections and queries. I like these tools for verifying my login credentials and ensuring my queries work as intended before implementing them in more complex applications and for verifying that the results that I'm seeing are what I should expect to see.
One easy way to verify that an account provided to you will allow you to connect to a remote database is to use something call a "UDL" file. You can easily create one on a Windows system. A UDL file is a file with the extension "udl" and all you need to do to create one is open notepad and save an empty file with this extension. Select File => Save As..., Change "Save as type" to "All Files" and type something like dbtest.udl in the File name field. That's it. When you then click on the file's icon, a surprising thing happens. A tool opens up that you can use for describing database connections and testing them.
The UDL (Data Link Properties) form that opens up has four tabs -- Provider, Connection, Advanced, and All. Since I had to connect one of my Unix systems to a Microsoft SQL Server database, I selected Microsoft OLE DB Provider for SQL Server on the Provider tab. In the Connection tab, I had to enter the name of the server, the username and password provided to me for the connection and the name of the database that I needed to connect to. Then I hit the Test Connection button and, voila!, I ran into an error -- Test connection failed because of an error in initializing provider ... Server does not exist or access denied. Did I do something wrong? No. There was something that still needed a little kick in the asset on the database side. The point is that I could have struggled for quite a while on my Unix server application, changing settings and trying to determine what I was doing wrong. Instead, a very simple test proved to me that the other side of the connection wasn't yet ready for me to connect.
flickr / Tim Morgan