Tutorials  /  Data Management  /  Connect Data

How to connect to CARTO from external applications using ODBC

Connecting to CARTO from external applications using ODBC.

This tutorial shows to connect with the CARTO database using the ODBC connection. We would strongly recommend reading the SQL direct connection article to know more about this feature before reading this one.

Introduction

Some apps like Tableau Desktop require to specify the database name, others like Power BI require that database name is left blank. When using ODBC connections, you should always use the RSA PEM file, with “.key” extension.

This tutorial will show how you can create a DSN using the ODBC Data Sources application included with Windows operating system.

Warning for macOS and Linux users

If you are using macOS or Linux, you can still create ODBC DSNs using ini files or GUI applications like ODBC Manager.

Create a DSN using ODBC Data Sources on Windows

Launch ODBC Data Sources application

  • If it has not been previously installed, download and install the PostgreSQL ODBC driver (MSI) from the this URL.

  • Create the following folder on your computer: C:\Users\<username>\AppData\Roaming\postgresql

  • Copy the “server_ca.pem” file to the folder created in step 2 and rename the file to root.crt.

  • Launch the ODBC Data Sources (64 bits) application

launch_odbc

Add System DSN specifying the PostgreSQL Unicode (x64) driver

Add the following connect options:

  • Data Source: CARTO
  • Database: blank or cartodb (depending on application)
  • Server: dbconn-geuw.carto.com or dbconn-gusc.carto.com
  • User Name: <your CARTO username>
  • SSL Mode: verify-full
  • Port: 5432
  • Password: <your API key>

new_data_source

launch_odbc

Setting libpq parameters

This step assumes that you have already copied the files to the C:\Certs directory). If you haven’t done that, we would strongly recommend doing it before continue in this step of the configuration.

Once you added the system DSN parameters and copied the files to C:\Certs directory, you would need to configure the libpq parameters.

In order to do that, click on the option Options -> Datasource. Within this menu, go to Page 3 and enter the next libpq parameters.

  • sslcert=C:\\Certs\\client.crt sslkey=C:\\Certs\\client.key
  • sslrootcert=C:\\Certs\\server_ca.pem
  • application_name=[Tableau|PowerBI|Qlik|...]

launch_odbc

After enter the parameters, click on Apply to set them. Click on OK button to close the Datasource dialog.

On the main dialog, click on Test to verify that you can connect successfully to CARTO.