Dbeaver Sql Client

Posted : admin On 1/29/2022
Dbeaver Sql Client

SQL clients can be used to connect to Denodo, however they must be configured before use. First, the Denodo JDBC driver needs to be downloaded to the client workstation. Then, the SQL client connection needs to be configured to use the driver. Each SQL client handles this differently.

DBeaver is the U-M recommended SQL client for use with Denodo. Follow the instructions below to configure DBeaver using Kerberos authentication with supplied userid/password (not SSO).

DBeaver is a SQL client software application and a database administration tool. For relational databases it uses the JDBC application programming interface (API) to interact with databases via a JDBC driver. For other databases (NoSQL) it uses proprietary database drivers. It provides an editor.

Sql

If you do not want to use DBeaver, there are several other SQL client options for connecting to Denodo:

  • Tableau
  • SQuirrel
  • SQL Workbench
  • DataGrip
File

Notes:

  • Oracle SQL Developer does not support the Denodo JDBC driver.
  • Tableau Desktop comes pre-configured with a Denodo connector. To use this connector, specify the Denodo VDP server URI, port and credentials.

Dbeaver Sql Client Software

Configuring DBeaver (Windows & Mac)

  1. DBeaver Overview DBeaver is a free, open source multiplatform database management tool and SQL client for developers and database administrators. DBeaver can be used to access any database or cloud application that has an ODBC or JDBC driver, such as Oracle, SQL.
  2. Academic License.
  3. DBeaver (Size 28 MB): Connection manager. Metadata browser. Execute queries. Working with SQL.
  1. Download driver denodo-vdp-jdbcdriver-7.0-update-20200310 and unzip the file.
    • Skip this step if you have the Denodo Virtual DataPort Administration Tool installed. It includes the driver as part of the installation.
  2. In DBeaver, navigate to Database > Driver Manager and click New.
  3. Configure the Edit Driver screen:
    • Driver Name: Denodo
    • Driver Type: Generic
    • Class Name: com.denodo.vdp.jdbc.Driver
    • URL Template: jdbc:vdb://{host}:{port}/{database}
    • Default Port: 9999
    • Default Database: gateway
    • Default User: your user name
  1. Click Add File, then navigate to and select the driver jar.
    • On Windows computers, the VDP Admin tool installs the driver to: C:Users%USERNAME%Virtual DataPort Administration Tool of Denodo Platform-7.0toolsclient-driversjdbc.
  2. Click OK.
  3. Navigate to Database > New Database Connection and select the Denodo database.
  4. Click Next.
  5. Configure the Connection screen:
    • Host: denodo.it.umich.edu
    • Default Port: 9999
    • Database: gateway (default)
    • User: your user name
    • Password: your Kerberos password
  1. Click the Driver properties tab and set:
    • useKerberos: true
    • useTicketCache: false
  1. Click Test Connection. If successful, the following dialog box displays. Click OK.
Dbeaver sql client login
  • If the test is unsuccessful, an error message displays. Common errors and their fixes are:
    • Error message: connection error: denodo.it.umich.edu
      • Fix: Enter the correct host name, port number, and ensure you’re on VPN if remote.
    • Error message: Error authenticating client with kerberos: Pre-authentication information was invalid (24)
      • Fix: Enter correct username/pw
    • Error message: authentication error: Database 'xxxxxxx' not found
      • Fix: Enter a valid database name to which you have access
  1. Click Finish.
  2. Expand the connection in the left navigation panel to open the Views folder.
Dbeaver

Dbeaver Sql Client

  1. Navigate to SQL Editor > New SQL Editor and write your query.

For any tables outside of the database specified in the connection, prefix them with their database name. For example, you specified its_data_insights1 in your connection but you need to query the calendar table from dw_time_and_labor. Your query would look as follows:

SELECT * FROM dw_accounts_receivable.account_tbl

Download Sqlite Browser

Additional Mac Installation Instructions

Free Db File Viewer

If Kerberos authentication does not work in step 10 after completing the installation instructions above, follow the steps below. Make sure DBeaver is not running before starting.

  1. Open dbeaver.ini located at /Applications/DBeaver.app/Contents/Eclipse.
  2. Add the following three lines to the end of dbeaver.ini, replacing XXXXXXX with your username:
    - Djavax.security.auth.useSubjectCredsOnly=false
    - Dsun.security.krb5.debug=true
    - Djava.security.krb5.conf=/Users/XXXXXXXX/DBeaver/krb5.ini
  3. Create a krb5.ini file in /Users/XXXXXXXX/DBeaver/krb5.ini with the following content:

[libdefaults]
dns_lookup_realm = false
ticket_lifetime = 24h
renew_lifetime = 7d
forwardable = true
rdns = false
default_ccache_name = KEYRING:persistent:%{uid}

default_realm = ADSROOT.ITCS.UMICH.EDU
[realms]

Dbeaver Sql Client Server

UMICH.EDU = {
kdc = kerberos-1.umich.edu
kdc = kerberos-2.umich.edu
kdc = kerberos-3.umich.edu
kdc = kerberos-4.umich.edu
admin_server = kerberos-admin.umich.edu
}

Db Browser For Sqlite Download

ADSROOT.ITCS.UMICH.EDU = {
kdc = adprod-dc-m1.adsroot.itcs.umich.edu:88
kdc = adprod-dc-m2.adsroot.itcs.umich.edu:88
kdc = adprod-dc-n1.adsroot.itcs.umich.edu:88
kdc = adprod-dc-n2.adsroot.itcs.umich.edu:88
kdc = adprod-dc-s1.adsroot.itcs.umich.edu:88
admin_server = adprod-dc-m1.adsroot.itcs.umich.edu:88
default_domain = umich.edu
}

[domain_realm]
umich.edu = UMICH.EDU
.umich.edu = UMICH.EDU
adsroot.itcs.umich.edu = ADSROOT.ITCS.UMICH.EDU
.adsroot.itcs.umich.edu = ADSROOT.ITCS.UMICH.EDU