How to connect to various DBs with SQL Developer


Introduction

SQL Developer is a useful GUI tool provided by Oracle. It supports not only Oracle DB but also other various DBs. Now I'd like to show you how to connect to various DBs with it.

Basic information on SQL Developer
Latest version:20.4.1 (Till March 2021)
Supported OS:Windows/Linux/MacOS/Other
Price: Free

Connect to various DBs

DB Type JDBC Driver Default Port Comment
Oracle DB No need to add 1521 For 11gR2, 12c, 18c, 19c, 20c (On-P/Cloud)
Autonomous DB No need to add 1522 Need Wallet file
MySQL mysql-connector-java-8.0.23.jar 3306 The driver is also applicable to MariaDB.
MariaDB As above 3306
PostgreSQL postgresql-42.2.19.jar 5432
DB2 db2jcc.jar, db2jcc4.jar 5000
SQL Server jtds-1.3.1.jar 1433 The driver is also applicable to Sybase.

※JDBC driver version is as of March 2021.

You can change the UI Language Setting and Font Size in SQL Developer. There is a guide at the last part of this blog.
How to Change Language Setting in SQL Developer
How to Change Font Size in SQL Developer

Download SQL Developer
URL:https://www.oracle.com/tools/downloads/sqldev-downloads.html
This time, I use the Windows version (with JDK8). (An Oracle account is required to download.)

You can start it by unzipping the Zip file and then running sqldeveloper.exe. (No installation required)


If you want to import the settings of the previous version, click Yes and proceed.

Connect to Oracle DB

I connect to DB19c(VM) on Oracle Cloud.

  • Create a new connection

    Database Type: Select "Oracle" (By default)
    Username, Password: Enter your username and password.
    Hostname: Enter the Hostname or the Host IP address.
    SID or Service Name: Enter either DB SID or the service name. (Enter PDB service name if you want to connect to PDB.)
    If you are not sure about the DB Service Name, please check this: How to get the Service Name of OCI DBCS.
    Save Password: If checked, you can skip entering the password the next time you connect.

    Press "Test" button to test. If the test connection is OK, then press "Connect" button set up the connection.

  • After connection
    Now you can execute SQL commands.

Connection Issues
When failed to connect to target database, here are some common reasons, just for your reference.

  • Invalid User and Password.
  • Wrong User Role.
  • DB instance is not started yet.
  • The listener port (TCP 1521) is not open on the firewall of target DB server.
  • For database on Cloud, the listener port (TCP 1521) is not added to the Ingress Rule. (Check the security list or NSG.)

Connect to ADB

  • Create a new connection
    The wallet file is required, please download it from OCI console in advance. After downloading, you do NOT need to unzip it.
    Database Type: Select "Oracle" (By default)
    Connection Type: Select "Cloud Wallet"
    Service: Specify from the list of XXXX_high, XXXX_medium, XXXX_low
    You can get the Service information from tnsnames.ora (compressed in wallet ZIP file).

  • After connection

Connect to MySQL

After downloading, unzip the ZIP file.
  • Add JDBC Driver
    MENU -> Tools -> Preferences -> Database -> Third Party JDBC Drivers -> Add Entry
  • Add client's IP Address
    Please note that you will not be able to connect unless you allow the IP address of the SQL Developer client.
    Error Message:Failure - message from server:"Host '<IP Address>' is not allowed to connect to this MySQL server'
    Solution:Execute the following command on the MySQL server side. (Example of root user)

mysql> create user 'root'@'\' identified by '\';
mysql> grant all privileges on \*.* to 'root'@'\';

After adding IP address, check with the following command.

  • Create a new connection
    Database Type: Select "MySQL"
  • After connection

Connect to MariaDB

The MySQL JDBC driver can be used for MariaDB.

  • Add client's IP Address
    Please note that you will not be able to connect unless you allow the IP address of the SQL Developer client.
    Error Message:Failure: Access denied for user '<User>'@'<IP Address>'
    Solution:Execute the following command on the MariaDB server side. (Example of root user)

MariaDB [(none)]> create user 'root'@'\' identified by '\';
MariaDB [(none)]> grant all privileges on \*.* to 'root'@'\';
MariaDB [(none)]> select host,user from mysql.user;
  • Create a new connection
    Database Type: Select "MySQL"
  • After connection

Connect to PostgreSQL

  • Add client's IP Address
    Please note that you will not be able to connect unless you allow the IP address of the SQL Developer client.
    Error Message:Failure -Test failed: FATAL: no pg_hba.conf entry for host "<IP Address>", user "<Username>", database "<DB Name>"
    Solution:Edit the file below and add an entry.
         C:\Program Files\PostgreSQL\13\data\pg_hba.conf
  • Create a new connection
    Database Type: Select "PostgreSQL"
  • After connection

Connect to DB2

  • Download JDBC Driver
    URL:https://www.ibm.com/support/pages/db2-jdbc-driver-versions-and-downloads
    IBMid is required to download, please have it ready in advance.
    Select the appropriate driver for your DB2 version.
    (※This example uses db2jcc4.jar.)

    After downloading, unzip the ZIP file.

  • Add JDBC Driver
    MENU -> Tools -> Preferences -> Database -> Third Party JDBC Drivers -> Add Entry

  • Create a new connection
    Database Type: Select "DB2"
  • After connection

Connect to SQL Server

  • Add JDBC Driver
    MENU -> Tools -> Preferences -> Database -> Third Party JDBC Drivers -> Add Entry
  • Create a new connection
    Database Type: Select "SQLServer"
If there is a connection error, please refer to the following documents. https://docs.microsoft.com/en-us/troubleshoot/sql/connect/resolving-connectivity-errors * After connection

How to Change Language Setting in SQL Developer

By default, the display language in SQL Developer is the same as the OS language. You can change it as your need.
For example, you are using a Non-English language, and you want to switch the UI to English.
Here is the way to change language setting.

Edit following file:
C:\sqldeveloper\ide\bin\ide.conf

Add following lines to the end of the file, then restart SQL Developer.
AddVMOption -Duser.language=en
AddVMOption -Duser.country=US

How to Change Font Size in SQL Developer

Edit following file:
C:\Users\<username>\AppData\Roaming\SQL Developer\system20.4.1.407.0006\o.sqldeveloper\ide.properties
The string of system20.4.1.XXXXX may differs depending on the version.

Edit the following lines depending on the language you are using. After editing, restart SQL Developer.

End


Related Blogs
Personal Blogs on Oracle Cloud Infrastructure
How to connect to Oracle DB with OCI Database Tools
How to get the Service Name of OCI DBCS
SQL Developerで様々なDBに接続する方法 (Japanese version)