Cannot connect to a database

Databases can work locally, on a server, or in the cloud. For server and cloud databases, you need a network connection. To verify that connection is available, use ping and telnet commands.

With the ping command, you can ensure that the destination computer is reachable from the source computer. Open a command line and type the following command: ping -a <host_IP>, where -a is a command option that resolves addresses to hostnames (if it is possible). If you use hostnames with the ping command, a hostname is resolved to the IP address. For example, ping -a example.com resolves to PING example.com (93.184.216.34).

ping -a <host_IP>

With the telnet command, you can test connectivity to remote computers and issue commands. If you specify a port as a parameter for the telnet command, you can test connectivity to a remote host on the given port. If the connection is successful, you see the message: Connected to <host_IP>.

telnet <host_IP> <port_number>

note

For security reasons, DBMS usually drops all telnet connections. The telnet command allows you to check if the port is opened for communication.

Each database (MySQL, PostgreSQL, Oracle, or any other vendor) has its own connection settings. Most database include the connection settings:

• Host: A hostname of a computer or another device that stores a database. It can be an IP address 127.0.0.1 or a domain name localhost.

• Database: A name of the database to which you want to connect. You can find the database name in the settings of your database server, or you can ask your database administrator. In some cases, it is possible to run a query in a database command line to see the names of all available databases. For example, in MySQL you can run SHOW DATABASES;.

  

• User: A name of a user that has sufficient privileges to perform actions with a database. Run a query in a database command line to see the names of all available databases. For example, in MySQL you can run SHOW GRANTS;.

  

• Password: A password of the user.

• Port: A number that identifies a connection point between hosts. Hosts use port numbers to determine to which application, service, or process a connection must be established. Different database vendors use different ports for their databases. The following list is a list of default port numbers.

  Vendor	Default port
  Amazon Redshift	5439
  Apache Derby	1527
  Apache Cassandra	9042
  Apache Hive	10000 (Hive Server2) or 9083 (Hive Metastore)
  Azure SQL Database	1433
  ClickHouse	8123
  Couchbase Query Query Service	11210
  Exasol	8563
  Greenplum	5432
  H2	8082
  HSQLDB	9001
  IBM Db2 LUW	50000
  MariaDB	3306
  Microsoft SQL Server	1433 (TCP), 1434 (UDP might be required)
  MySQL	3306
  Oracle	1521
  PostgreSQL	5432
  Snowflake	443
  SQLite	None
  Sybase ASE	5000
  Vertica	5433

  note

  Real port numbers might be different on your system. Verify that you use a correct port number with your database administrator, server settings, or hosting provider.

Verify that the connection settings for the selected data source are correct. For more information about creating and changing a data source, refer to Create a data source and Sessions.

With a JDBC driver, you can interact with a database management system (DBMS) from DataSpell. Each DBMS requires its own JDBC driver. Ensure that the driver version and the DBMS version are compatible with each other.

From DataSpell, you can download drivers for all supported vendors. You can check the full list of supported vendors in the Drivers list . Alternatively, you can add your own driver to an existing vendor, or create a new driver entry for the vendor that is not on the Drivers list.

To open the Drivers list, in the Database tool window ( Window | Tool Windows | Database), click the Data Source Properties icon or press ShiftEnter.

Download a driver and select the driver version

To download drivers from the JetBrains FTP server, select a vendor from the Drivers list, and click the Download ver. <version_number> link in the Driver files pane.

To change the driver version, click the ver. <version_number> link in the Driver files pane and select the driver version that you need.

To make a connection to a database more secure, some services require SSH or SSL usage.

SSH

Secure Shell or SSH is a network protocol that is used to encrypt a connection between a client and a server.

In DataSpell, you can create an SSH connection one of the following ways:

• Using the DataSpell SSH tunnel. The IDE will create an SSH tunnel using the SSH configuration that you set.

• Creating an SSH tunnel manually using PuTTy, Pageant, or ssh-client.

DataSpell SSH tunnel

DataSpell can create an SSH tunnel based on the SSH configuration that you set. To access the SSH configuration settings, press CtrlAlt0S to open the IDE settings and select Tools | SSH Configuration.

To use an SSH tunnel for the data source, select the Use SSH tunnel checkbox in the SSH/SSL tab of Data Sources and Drivers dialog (ShiftEnter).

note

In most cases, you do not need to modify the General tab settings after setting the SSH configuration in SSH/SSL tab, as DataSpell will connect to the local end of the SSH tunnel. The exception is when you create an SSH tunnel manually, in that case you need to set localhost as Host on the General tab.

Upon the connection, the SSH tunnel hostname is resolved on the machine with SSH server, as the server establishes connection to a database.

All created SSH connections are shared between all the data sources that you have in a project. If you do not want to share a connection between projects, select the Visible only for this project checkbox in the SSH connection settings.

Connect to a database with SSH

1. Open data source properties. You can open data source properties by using one of the following options:

   • In the Database tool window ( Window | Tool Windows | Database) , click the Data Source Properties icon .

   • Press CtrlAltShift0S.

2. Select a data source profile where you want to change connection settings.

3. Click the SSH/SSL tab and select the Use SSH tunnel checkbox.

4. Click the Add SSH configuration button ().

5. In the SSH dialog, click the Add button.

6. If you do not want to share the configuration between projects, select the Visible only for this project checkbox.

7. In Host, Username, and Port fields, specify your connection details.

8. From the Authentication type list, you can select an authentication method:

   • Password: Access the host with a password. To save the password in DataSpell, select the Save password checkbox.

   • Key pair (OpenSSH or PuTTY): Use SSH authentication with a key pair. To apply this authentication method, you must have a private key on the client machine and a public key on the remote server. DataSpell supports private keys that are generated with the OpenSSH utility.

     Specify the path to the file where your private key is stored and type the passphrase (if any) in the corresponding fields. To have DataSpell remember the passphrase, select the Save passphrase checkbox.

   • OpenSSH config and authentication agent: Use a credentials helper application that manages your SSH keys, such as ssh-agent.

9. Click OK to confirm the new SSH configuration settings.

10. In the Local port field of Data Sources and Drivers dialog, specify the local port number from which you want to forward the connection. Otherwise, DataSpell selects the port dynamically.

tip

For more information about working with SSH keys, refer to the Generating a new SSH key and adding it to the ssh-agent tutorial.

Disable SSH connection to a database

1. Open data source properties. You can open data source properties by using one of the following options:

   • In the Database tool window ( Window | Tool Windows | Database) , click the Data Source Properties icon .

   • Press CtrlAltShift0S.

2. Select a data source profile where you want to change connection settings.

3. Click the SSH/SSL tab and clear the Use SSH tunnel checkbox.

4. Click Apply.

Create SSH tunnel manually

Create the SSH tunnel with PuTTY (Windows)

1. Download and run the latest version of the PuTTY SSH and Telnet client (download the client from https://www.putty.org/).

2. In the PuTTY Configuration dialog, navigate to Connection | SSH | Auth.

3. In the Private key file for authentication field, specify the path to your private key file and click Open.

4. In the command line window, specify the username that you use for the SSH tunnel and press Enter. Do not close the command line window.

5. In the Database tool window ( Window | Tool Windows | Database) , click the Data Source Properties icon on the toolbar.

6. Select a data source profile where you want to change connection settings.

7. Click the SSH/SSL tab and select the Use SSH tunnel checkbox.

8. From the Auth type list, select OpenSSH config and authentication agent.

9. In Proxy host, Proxy user, and Port fields, specify connection details.

10. To ensure that the connection to the data source is successful, click Test Connection.

Create the SSH tunnel with Pageant (Windows)

Pageant is an SSH authentication agent for PuTTY, PSCP, PSFTP, and Plink. Pageant stores your private key, and as long as it is running, it provides the unlocked private key to PuTTY or other tools like DataSpell. You can find the Pageant icon in the Windows taskbar.

1. Download the latest version of Pageant (download the client from https://www.putty.org/).

2. In the Windows taskbar, right-click the Pageant icon and select Add Key.

3. In the Select Private Key File dialog, navigate to the private key file (the PPK file) and click Open.

4. (Optional) Enter the private key passphrase and press Enter.

5. In the Database tool window ( Window | Tool Windows | Database) , click the Data Source Properties icon on the toolbar.

6. Select a data source profile where you want to change connection settings.

7. Click the SSH/SSL tab and select the Use SSH tunnel checkbox.

8. From the Auth type list, select OpenSSH config and authentication agent.

9. In Proxy host, Proxy user, and Port fields, specify connection details.

10. To ensure that the connection to the data source is successful, click Test Connection.

Create the SSH tunnel with the ssh-agent (macOS and Linux)

Run all commands for ssh-agent in the command line.

1. Ensure that ssh-agent is running.

   ssh-agent

2. Add your key to the agent (in the following example, the key path is ~/.ssh/id_rsa).

   ssh-add ~/.ssh/id_rsa

3. (Optional) On macOS, you can add -K option to the ssh-add command to store passphrases in your keychain. On macOS Sierra and later, you need to create the config file in ~/.ssh/ with the following text:

   Host *
   UseKeychain yes
   AddKeysToAgent yes
   IdentityFile ~/.ssh/id_rsa

   If you have other private keys in the .ssh directory, add an IdentityFile line for each key. For example, if the second key has the id_ed25519 name, add IdentityFile ~/.ssh/id_ed25519 as an additional line for the second private key.

4. List all added keys.

   ssh-add -L

5. In the Database tool window ( Window | Tool Windows | Database) , click the Data Source Properties icon on the toolbar.

6. Select a data source profile where you want to change connection settings.

7. Click the SSH/SSL tab and select the Use SSH tunnel checkbox.

8. From the Auth type list, select OpenSSH config and authentication agent.

9. In Proxy host, Proxy user, and Port fields, specify connection details.

10. To ensure that the connection to the data source is successful, click Test Connection.