How do I create an SQL database data source?

Structured Query Language (SQL) is used to manipulate and extract information from an SQL database. Klipfolio lets you connect to an SQL database and execute an SQL query to retrieve information to create a data source. This article provides basic instructions on creating an SQL database data source. If, after following these instructions, you are still unable to connect to Klipfolio, refer to these troubleshooting tips.

Check out this great tutorial series on building data visualizations that reference data from an SQL database.

Supported drivers

The following JDBC drivers are supported:

  • MS SQL (Microsoft SQL Server)
  • MySQL
  • Oracle Thin
  • Oracle OCI
  • Sybase SQL Anywhere
  • PostgreSQL
  • Firebird
  • DB2
  • Amazon Aurora*

Note: ODBC drivers are not supported. Also, make sure you have Klipfolio's IP addresses whitelisted. 

*For Amazon Aurora, make sure you have Klipfolio's IP addresses set up in your DB security group. Learn more

Creating an SQL database data source 

 To create an SQL database data source

  1. Click Library > Data Sources.
  2. Click Create a New Data Source and select SQL Query.
  3. Fill in the following fields and select whether you want to Include column headers:
    • Host
    • Port
    • Database
    • Driver
    • Username
    • Password
    • SQL Query
  4. If accessing a database behind a firewall, click Use an SSH tunnel and type the required credentials.
  5. Click Execute Query and follow the instructions to save the data source.

Troubleshooting tips for connecting to an SQL database

There are two options for querying an SQL database:

  • Port forwarding to an external IP address
  • Using an external SSH tunnel

If you followed the instructions above but still cannot connect to Klipfolio, consider the following questions:

    1. Have you whitelisted our Klipfolio IP addresses? See the full list of Klipfolio's Whitelist IP addresses
    2. Is your database's external IP address publicly accessible and are you able to resolve the IP address to your database server? To test internally, ping or telnet your database's IP address. If unsuccessful, check your firewall configuration.
    3. For multiple instances of SQL server, check your SQL Server Configuration Manager to verify you have the correct port for the database to which you want to connect. The first SQL instance uses port 1433, other instances might use a different port number.
    4. Is the port open? Go to Port forwarding tester to test your port's status. If the port is not open, it might be a bad route or the destination SQL device might not be properly configured to listen to that port.
    5. For an SSH Tunnel, is the domain account valid?
    6. In Klipfolio, at the connect to an SQL database page, are you entering the IP address of the database server as the Host Name? Note: The Host field should not point to the SQL Server instance, just to the local host. For example, local host, not localhostSQLEXPRESS2012
    7. In Klipfolio, at the connect to an SQL database page, are you entering the actual database name at the Database field?
    8. Are you using an SQL Stored Procedure? When working with Klipfolio, you cannot use temporary tables or multi-selects in your Stored Procedure.

Tip: You can use a query of Select 1 to test an SQL connection.