You Can’t Get There From Here: My Checklist for Connecting to Databases

I grew up with the Maine story “Which Way to Millinocket” that famously ends in “You can’t get there from here.” Sometimes I feel like that when connecting to a brand new database in Tableau, here are a couple of recent errors:

 

1

“Unable to connect to ODBC Data Source. Check that the necessary drivers are installed and that the connection properties are valid. Unable to connect to the server […]. Check that the server is running and that you have access privileges to the requested database.”

The message doesn’t indicate what the exact problem is nor how to fix it, just some unhelpful suggestions. It’s the “You can’t get there from here” story.

image001

[IBM][CLI Driver] SQL30081N A communication error has been detected. Communication protocol being used: “TCP/IP”. Communication API being used: “SOCKETS”. Location where the error was detected: “10.31.10.31”. Communication function detecting the error: “selectForConnectTimeout”. Protocol specific error code(s): “0”, “*”, “*”. SQLSTATE=08001

Unable to connect to the server […]. Check that the server is running and that you have access privileges to the requested database.

This one is even worse…there’s a bunch of developer-ese that is most likely leftover code from  back when IBM still shipped Token Ring and other protocols, it has no meaning to us. It’s like the other famous Maine farmer-giving-directions story where the farmer says “Go a few miles or thereabouts and take a left at the corner where the Hanson’s barn used to be that burned down awhile back.” The only marginally useful bit of the error message is the suggestion at the end (that is repeating what’s already at the top of the dialog).

The challenge with error messages like these is that the developers are doing the best they can and interconnecting pieces of software (like Tableau and your database of choice) is hard, sometimes there’s just not much information available to explain why things don’t work. Even more challenging is that all users like us care about is what we need to do next. This post is about what I do next when I run into errors like this. I use a checklist:

Checklist for Database Connection Errors

  1. Double-check that whatever information you were given about the database connection is correct and try again. For example I once was trying (and trying) to connect to a database server named something like “mhlsql” only it was really “mh1sql”. Oops!
  2. Triple-check that your information is correct. For example I was once given a database password that had a lot of gobbledygook and numbers and manually typed in the numbers again and again and had accidentally reversed two digits. If you can copy & paste information into the forms then do that instead of typing.
  3. Check whether you have network connectivity in general. Browsing a random site on the web covers this. I’ve been frustrated thinking I couldn’t connect to a server when in fact the entire building had lost internet access.
  4. Depending on your system and permissions you may need to skip this step: Check whether you can connect to the database server using a local (i.e. on your machine) database querying application like SQL Server Management Studio, MySQL Workbench, TOAD, etc.*** or an existing BI or reporting tool like Cognos, Crystal Reports, etc. This requires:
    1. Having the right database driver. You can check your local driver against the versions at http://www.tableau.com/support/drivers and install a new one if necessary. Also databases are sensitive to 32-bit and 64-bit distinctions so you’ll need to know what is right for your computer *and* the database. This is a place where you may need to ask your IS/IT department for information and/or to install the driver.
    2. Has that database server authorized your IP address? If you’re connecting to a database server for the first time it might be locked down and blocking your IP address. You can do some testing of this in step 5 below on using ping, telnet, etc. but ultimately this may need to go to IS/IT.
    3. Do you have the right username/password or credentials to connect? Again, if you’re connecting to a database server for the first time you might not be set up to connect to that server.
    4. Use the querying application or BI/reporting tool to check whether you can access the particular database as well as table(s)/view(s)/stored procedure(s) that you want to.  For example you might be able to connect to the desired database but not have permissions on certain tables in the database.
    5. Verify that your Tableau data source is using the same settings from steps #3. For example I’ve made a mistake where I accidentally connected to the production database in Tableau (where my credentials were limited) and the test database in the querying tool and then was wondering why I got errors in Tableau.
  5. If you don’t have a querying application or you tried one and couldn’t get that to connect to my source then check whether you can ping or telnet or Remote Desktop/ssh into that database server machine (i.e. know that the machine is up). These aren’t always available depending on the configuration of the server and what kind of permissions you have.
    1. You can run ping from the command line, the command is ping [hostname or IP address]. Getting the hostname or IP address for some databases can be difficult, for example on Windows if you just have a SQL Server database name. If you check your DSN (Windows) you can get the IP address or host name, you’ll need to open up the ODBC Administrator application which Microsoft has moved around in different releases, do a web search for your version. If this doesn’t work it could be that ping has been disabled on the database server machine or that the database server is refusing connections from my machine.
    2. You can also run telnet from the command line, the command is telnet [hostname or IP address] [port]. Again you can sometimes find the port in the DSN, you can also do a web search to find the default port for my database. If this doesn’t work it’s not a sign that the database doesn’t exist because the port could have been mapped to something else or it could be that the database server is refusing connections from your machine.
    3. If you have login credentials on the machine that the database server is running on then Remote Desktop or ssh can be useful to validate that your machine can at least “talk” to the remote machine.
  6. If none of the above works then if you have a co-worker who does have a successful setup try connecting from their machine. On Windows if you’re using saved DSN’s then you’ll also need to check that the DSNs are exactly the same on both machines.
  7. Do a web search and hopefully find something.
  8. Wave the white flag and submit a trouble ticket to IS/IT.

*** In general having a local querying application is useful in debugging performance issues because your can grab a query from the Tableau Performance Recorder or Tableau logs and then run it to see what it does.

If you have any other tips, please submit them in the comments below!

Do you like this post? Would you like to get this kind of help available to you? Check out DataBlick, we offer Tableau and Alteryx consulting, training, and on-demand support.

3 thoughts on “You Can’t Get There From Here: My Checklist for Connecting to Databases

    1. Jonathan Drummey Post author

      Thanks! Cloning the connection (by saving a workbook or saving the data source) is a good point however I’ve found it’s not particularly reliable, here are three ways that has gone wrong for me:

      1) The DSN someone else was using didn’t exist on my machine, so I couldn’t open up the workbook.
      2) I had permissions on a set of tables and a new employee didn’t, so they couldn’t open up the workbook.
      3) Someone else created a data source and when I tried to open it my machine’s IP address wasn’t in the trusted list for the database server.

      In all of these cases we’d see some variation of the “can’t connect to data source” error and have to go through some amount of the checklist to figure out what was going on.

      Jonathan

      Reply
      1. Alexander Mou

        Got it. Thanks!

        Cloning makes the new setup a bit easier by already filling some fields like server address, port number etc. It’s particularly useful if it’s in the case of same owner with different workbooks.

        Reply

Leave a Reply to Alexander Mou Cancel reply