Questions  /  Working with Data  /  Import Data

Accessing your CARTO database from external applications

Overview of the SQL direct connection feature

Introduction

The Direct SQL Connection is a feature that allows Enterprise users to connect directly to their CARTO PostgreSQL database. This means they can access and modify data stored in CARTO from external applications that support PostgreSQL as a data source. These applications include GIS desktop tools like QGIS, BI applications like Tableau or Power BI and data analytics platforms like Databricks.

Configuration

To configure this feature you need to go to your CARTO account’s Settings page, accessible by clicking on your icon in the top right corner of the dashboard. From the icon menu select the Connections option.

configuration

To connect from an external application, you will need to provide the IP address you are connecting from and generate a client certificate. These are security measures to ensure that access to your database is restricted.

If you are connecting from a desktop tool like QGIS or Tableau Desktop, you will need to enter the IP address for the computer where QGIS or Tableau Desktop is installed. If you are connecting from a server, you will need to provide the server IP address. You can enter more than one IP address and you can specify IP addresses using CIDR notation with masks. In the future you will be able to allow access from any IP address, without needing to specify individual IP addresses.

If you are configuring the IP address parameter using the computer where your external application is installed, you can click on the Get your IP address link beneath the text box and it will populate automatically.

ip

After you have entered at least one IP address, you will be able to generate a new certificate. This certificate is used to perform client validation and allows CARTO validate your identity.

First you must click on the NEW CERTIFICATE button. Then you will need to enter a name to identify your certificate. You can create more than one certificate.

ip_certificate

As a best practice we recommend generating different certificates, one for each computer you will be connecting from.

Finally, you will be redirected to a modal dialog where you can download your certificate.

download_certificate

You will be able to close the modal dialog only after clicking on the button to download your certificate.

The certificate

The downloaded ZIP file contains the following component files:

  • readme.txt: Details the content of the ZIP file.
  • client.crt: Certificate file that will be sent to the server for validation.
  • client.key: Private matching key file in RSA PEM format.
  • client.key.pk8: Private matching key file in DER PKCS #8 format.
  • server_ca.pem: Root certificate for CARTO server.

We provide the private key file in two different formats: RSA PEM and DER PKCS #8. We do this in order to ensure that you are able to connect to CARTO independently from your application (some client applications support only one of the two formats). For instance, connections using ODBC must use the RSA PEM format while connections with JDBC must use the DER PKCS #8 format.

Connecting to CARTO

When you are connecting to CARTO from an external application, you must use a PostgreSQL driver/connector that allows you to send the client certificate for validation. You cannot use a connector that provides an SSL connection to the PostgreSQL server but doesn’t let you include a client certificate.

There are three different modes of SSL connection to a PostgreSQL server:

  • require
  • verify-ca
  • verify-full

We recommend using the verify-full mode because it will try to validate the identities of both the client and the server, including the hostname.

When you are connecting to the CARTO database, you will need to specify a CARTO API key as the password. The API key you use will determine which operations can be performed and which tables are accessible. We recommend generating specific keys instead of using your Master API Key. We advise against exposing your Master API Key since it allows unrestricted access to your database. You can find detailed information about generating API Keys in your CARTO account in this section of our Developer Center.

For specific instructions for connections from external applications, please refer to the following tutorials: