Database Features
Tips and Tricks on using Exasol - from SQL and Lua Scripts to transactions and performance
Showing results for 
Search instead for 
Did you mean: 
Background This how-to describes the necessary steps to use Kerberos based single sign-on (SSO) in Exasol. EXAoperation The configuration of EXAoperation is explained in the Operational Manual: Prerequisites Database and used drivers / EXAplus have version 6.0.8 or newer. How to install SSO with Kerberos?   Step 1:   Create Exasol user with Kerberos authentication Create user CREATE   USER   <identifier> IDENTIFIED BY   KERBEROS PRINCIPAL <string-literal> The Kerberos principal is usually formed like this:   < user >@<realm> Alter User ALTER   USER   <identifier> IDENTIFIED BY   KERBEROS PRINCIPAL <string-literal> Rename User Renaming a user with Kerberos authentication will not affect the Kerberos principal specified by CREATE USER or ALTER USER. More details on the creation of users can be found in the user manual. Step 2: Client configuration After these steps, the database is ready to accept users that authenticate via Kerberos. For the client side, different options to use this authentication method exist: ODBC New connection string parameters KERBEROSSERVICENAME Service name part of the Kerberos principal. Default: exasol KERBEROSHOSTNAME Hostname of the Kerberos principal. Default: use hostname from the connection string Kerberos based single sign-on will be used if user and password are not specified and the SQL_DRIVER_PROMPT flag is not set during SQLDriverConnect(). ODBC Single-Sign-On on Linux You can either use your existing ticket for your user or use a keytab file. Use existing ticket: We assume that you already have a ticket for your user from the KDC. If not, obtain a ticket using kinit: kinit user@YOUR.REALM Use keytab file:  To use a keytab file, you have to make sure there are no tickets in the cache using kdestroy. Then you have to tell Kerberos where your keytab file is located: export KRB5_CLIENT_KTNAME=/home/<your_user>/<your_keytab_file_name>          Alternative: add the location of the keytab file to the krb5.conf: [libdefaults] default_client_keytab_name = /home/<your_user>/<your_keytab_file_name> We assume you have a valid krb5.conf Create an odbc.ini configured for Kerberos. Here is an example: [exa_linux] Driver = /home/<your_user>/KerberosTest/EXASolution_ODBC-6.0.8/lib/linux/x86_64/ EXAHOST = exasol-host:8563 EXALOGFILE = odbc.log LOGMODE = Debug Comm KerberosServiceName = exasol KerberosHostName = kerberos.hostname. for .exasol Make sure your environment variables are correctly set (this might vary depending on the environment) export KRB5CCNAME=DIR:/path/to/your/cache/ export KRB5_CONFIG=/path/to/your/krb5.conf export ODBCINI=/path/to/your/odbc.ini You should now be able to use the   exa_linux   DSN isql exa_linux -v ODBC Single-Sign-On on Windows On Windows just install the Exasol ODBC driver. Then in the ODBC Configuration tool from Windows add a new DSN for the Exasol ODBC driver. Now you can setup the connection: Specify any DSN name Specify connection string (host: port) Specify "Additional parameter" in the second tab (Advanced) of the configuration tool to "kerberosServiceName=<yourServiceName>. You may require kerberosHostName too, depending on your Active Directory configuration. For this you have to extend the additional parameters as follows: "kerberosServiceName=<yourServiceName>;kerberosHostName=<yourKrbHostName>". Compatibility Mode If it is not possible to specify the additional parameters, the "compatibility mode" using username and password can be used. For this, you can specify the Kerberos service name in the "User name" field as: Kerberos:<yourServiceName> If the kerberosHostName has to be specified, the "User Name" field has the following format: Linux Kerberos:<yourServiceName>@<yourKrbHostName> Windows Kerberos:<yourServiceName>/<yourKrbHostName> JDBC New connection string parameters If one of the following connection string parameters is set, Kerberos single-sign-on will be used for JDBC. kerberosservicename Service name part of the Kerberos principal. Default: exasol kerberoshostname Hostname of the Kerberos principal. Default: use hostname from the connection string JDBC Single-Sign-On On Linux Using ticket cache We assume that you already have a ticket for your user from the KDC. If not, obtain a ticket using kinit: kinit user@YOUR.REALM If you now use JDBC with the connection string parameters specified above, authentication should work without asking for a username or password. For illustration we provide an example Java application: package com.exasol; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.Properties; public class KerberosTest { static Properties props = System .getProperties(); private void testKerberosConnection( String h, String p) { String connectionString= "jdbc:exa:" + h + ":" + p + ";kerberosservicename=exasol;kerberoshostname=kerberos.hostname. for .exasol" ; //Optional connection string parameters: ";debug=1;logdir=/home/<your_user>" try { Class .forName( "com.exasol.jdbc.EXADriver" ); } catch (ClassNotFoundException e) { e.printStackTrace(); } Connection driverConn= null ; try { //Connectiong to the EXASOL Server as the current OS user driverConn = DriverManager.getConnection(connectionString); } catch (SQLException e) { e.printStackTrace(); } try { if (driverConn!= null && !driverConn.isClosed()) System .out.println( "Kerberos connection test success!" ); else System .out.println( "Kerberos connect failed!" ); } catch (SQLException e) { e.printStackTrace(); } } public static void main( String [] args) { ( new KerberosTest()).testKerberosConnection(args[0], args[1]); System .out.println( "Done." ); } } How to run the test application: java false -cp exajdbc.jar:/path/to/example com/exasol/KerberosTest <your_exasol_host_name> <your_exasol_port_nr> In case of problems with Kerberos not working as expected, you can enable command line Kerberos debugging by setting the following parameter value to true: true These logs displayed will help you understand the issues related to kerberos not working.  Using keytab file To configure the Exasol GSSAPI connection and to specify the keytab file you can use a gss.config file: { required principal= "<your_user>@<your_realm>" useKeyTab= true doNotPrompt= true keyTab= "/home/<your_user>/<your_keytab_file_name>" ; }; How to run the test application: java<your_user>/gss.config false -cp exajdbc.jar:/path/to/example com/exasol/KerberosTest <your_exasol_host_name> <your_exasol_port_nr> JDBC Single-Sign-On   On   Windows First, install the Exasol JDBC driver. In the Java sample provided above, you can modify the connection string as follows: String connectionString= "jdbc:exa:" + h + ":" + p + ";kerberosservicename=<your_kerberos_service_name>;kerberoshostname=<your_kerberos_hostname>;debug=1;logdir=c:\\Users\\<your_user>" ; Compile the sample and run it java -cp "c:\Program Files (x86)\EXASOL\EXASolution-6.0\JDBC\exajdbc.jar" ;c:\path\to\example;C:\Users\<your_user>\Waffle\Bin\jna-4.0.0.jar;c:\Users\<your_user>\Waffle\Bin\jna-platform-4.0.0.jar;c:\Users\<your_user>\Waffle\Bin\waffle-jna.jar kerberosTest <your_exasol_server_host> <your_exasol_port> Compatibility Mode Similar to ODBC, the "compatibility mode" is supported. If you use a username in the following format, the password will be ignored and it behaves as if the respective Kerberos connection string parameters were set (see table above). Note that only the service name is mandatory. Linux Kerberos:<yourServiceName>@<yourKrbHostName> Windows Kerberos:<yourServiceName>/<yourKrbHostName> EXAplus GUI In the login dialog in EXAplus, the user can choose Username or Kerberos in the dropdown list. If Kerberos is chosen, the GUI will change, so that the user could input service name, host and connection string.   CLI For Login with Kerberos, EXAplus should be started with parameter -k. Then the user can input service name, host and connection string. $ /usr/opt/EXASuite-6/EXASolution-6.0.8/bin/Console/exaplus -k EXAplus 6.0.8 (c) EXASOL AG Service name: exasol Host: Connection String (localhost:8563): mynode:8563 Thursday, March 8, 2018 12:57:48 PM CET Connected to database exa.mynode.8563 as user null . EXASolution 6.0.8 (c) EXASOL AG SQL_EXA> Manipulation of JVM Properties In the Preferences > Other menu, the user can define JVM Properties, which will be used when Java is called for starting EXAplus. The default value of JVM Properties is -Xmx512m. This feature is only implemented in EXAplus GUI for Linux and Windows. For MacOS, SSO with Kerberos is not available, hence the JVM option is not applicable on MacOS. Windows, Linux     
View full article
Background This article will explain about the details on encryption at Exasol Database. It gives you answers for questions like : Is data transfer through client connection encrypted by default or not? How to check encryption is enabled in database? How to enable/disable encryption? What is the parameter forceProtocolEncryption used for How to enforce encrypted client connections ? and so on..   Explanation In Exasol database, when data is transferred through a network , the data is by default encrypted (from Exasol versions 6.0 and above). Exasol uses ChaCha20 encryption for JDBC, ODBC, ADO.NET, and CLI. For WebSockets, Exasol uses TLS v 1.2 encryption.  On all clients and drivers, the encryption can be enabled by using their respective connection string parameters, for example: EXAPlus: -encrpytion <ON|OFF> JDBC: encryption=<1|0> (1 = on, 0= off, default is 1) ODBC: ENCRYPTION=<"Y"|"N"> (Y is default) ADO.NET: encryption=<ON|OFF> (on is default)   How to check if data transferred was encrypted or not: One can check the ‘encrypted’ column in exa_dba_sessions or exa_dba_audit_sessions tables that encryption was set to true or false for that particular session.   The Parameter forceProtocolEncryption: In addition to these driver properties, one can set a database parameter in EXAoperation to force incoming connections to be encrypted. The parameter is: -forceProtocolEncryption=1. This can be done while creating a database or setting it in Exaoperation and restarting the database again.   Further Clarifications: If the parameter '-forceProtocolEncryption=1' is set to the database, it means that regardless of what the client requests, protocol encryption will be FORCED (i.e. required) by Exasol for the connection. If either Exasol or the client requests encryption, encryption will be used. So we can conclude that no matter which client/driver you use, if the parameter '-forceProtocolEncryption=1' is set over the database, then all the connections are encrypted.   Additional notes: With '-forceProtocolEncryption=1', clients are only rejected if they do not support encryption at all (e.g. older drivers). An unencrypted connection is only allowed if both Exasol and the client disable encryption. Having this parameter (forceProtocolEncryption=1) set, means that even if the client/driver side encryption is turned off then (with the exception of -- the driver being not old/does not support encryption) then the client/driver is forced to encrypt data. In other case (when this parameter would not have been set) then client/driver connection would be allowed to transfer data UNENCRYPTED.   Additional references: bug related to this topic: EXASOL-2649  
View full article
Questions What data can you read using subconnections? What data can you insert using subconnections? How to establish parallel connections? What about transactions in parallel mode? What about transactions in parallel mode? Answer You can use subconnections to read or insert data in parallel from and into the EXASOL server. This will increase performance considerably. Using this interface you can read/write data in parallel from different physical hosts or from different processes or in one process from different threads. What data can you read using subconnections? You need a statement that produces a large result set. The result set can be then retrieved in parts from the parallel connections. For small results this interface has no advantage. What data can you insert using subconnections? Like when reading data, using this interfaces only makes sense if you want to insert large amounts of data. You can simply insert rows into a table in EXASOL using a prepared insert statement. The data coming through the parallel connections will be put together by the server into the right table. How to establish parallel connections? First you need a normal JDBC connection. On this you can use the method   EnterParallel()   to start operating with subconnections. You will receive connection strings for all new parallel connections you can start now. Start the subconnections with auto commit off. After this you can start reading or sending data, nearly like in a normal connection. Attention: You can specify the maximum number of subconnections in   EnterParallel() . This number may be reduced by the server because only one subconnection is allowed per database node. You have to establish the subconnections by using all connection strings received from   Get Worker Hosts() . Subconnections can only be used after all connections have been established. What about transactions in parallel mode? Start the subconnections with auto commit off. Commits should be made only on the main connection after the subconections have inserted all data and they have closed the prepared statements. An Java example is attached. In the example a main connection reads the connection strings for the subconnections from the server. For each subconnection a thread is started that inserts a few rows. Commit is executed on the main connection. Then other threads read the data from the table.
View full article
Problem When switching from EXAplus, you would like to change the shortcut for executing the current statement to do Auto completion create LUA Scripts / UDFs  use of Favorites Solution Change keyboard shortcuts When switching from EXAplus, the first thing that you would like to change is the shortcut for executing the current statement. In DBVisualizer this is set to   Ctrl + .  per default.  In EXAplus on the other hand this shortcut is defined as Ctrl + Enter. To chance it go to Tools -> Tool Properties and open the Key Bindings Dialog in the tree, create a copy of the current keymap called exasol and change the Binding for Main menu -> SQL Commander -> Execute Current:   Auto completion  It's important to note that in DbVisualizer the auto completion of table/column names works a little bit different than in EXAplus. In EXAplus you had to type first the schema name to access the tables. In DBVisualizer on the other hand you can directly access the table or column name without the need to fully qualify the object. It's also important to note that in DBVisualizer if you are in the context of a schema only the tables of that schema are shown in the auto completion. You can also customize the behavior of the auto-completion in Tools -> Tool Properties in the Dialog SQL Commander -> Auto Completion:   Creation of LUA Scripts / UDFs  In DBVisualizer LUA scripts / UDFs should be created as a SQL block. The start of the SQL Block is   --/   and the end of the block is defined with   /: --/ CREATE LUA SCRIPT MY_CAT RETURNS TABLE AS return query([[select * from cat]]) /   Per default   keywords in DBVisualizer are displayed in uppercase, so if you open an existing LUA script keywords like are automatically transformed to uppercase, so the case sensitive LUA script is not working anymore, please change this setting in Tool Properties -> Tools - SQL Formatting to "Keep Case Asis"   Please also have a look at the official documentation Favorites versus Bookmarks In EXAplus frequently used SQL Statements have been stored in the favorite tab. In DBVisualizer you have a similiar feature called Bookmarks. Learn more in the official documentation: . EXAplus commands EXAplus commands like   set autocommit off  are not working in DBVisualizer. DBVisualizer has it's syntax for it's client commands starting with @, e.g.   @set autocommit off;   See also official documentation: **  Additional Notes Known Issues of DBVisualizer  The open schema call is currently not interpreted by DBVisualizer Newly created schemas are not shown in the SQL Editor drop down menu -> Workaround reconnect the connection R Scripts sent from a Windows OS using DBVisualizer can't be executed because of windows CRLF, Following Workaround  In the current version, DbVisualizer uses the platforms linefeed convention, i.e. CRLF for Windows and LF for all other platforms. There is a way to force DbVisualizer to use LF for the text it sends to the database regardless of platform:   1) Open the file named   DBVIS-HOME/resources/dbvis-custom.prefs   (where DBVIS-HOME is the DbVisualizer installation folder) in a text editor, 2) Add the following on a new row:   dbvis.exasol.ReplaceCRLFwithLF=true 3) Restart DbVisualizer. 
View full article
Background In rare cases, to debug a problem Exasol will require detailed information regarding the status of the EXAplus client. As EXAplus is a java application, that information can be supplied as a   java stacktrace   using oracle's java SDK. Prerequisites Java SDK must be installed on the machine where EXAplus was started. The SDK can be downloaded here: How to get a java stacktrace for EXAplus? While the following examples and paths were generated on the Windows (tm) operating system, the process does also apply to MacOSX and linux systems. Step 1:   Open a console Any console will do, depending on your operating system. Step 2: Change into the 'bin' directory of the JDK installation: cd "C:\Program Files\Java\jdk1.8.0_20\bin" Please note: The actual path may vary by operating system, installation and java version. This is only required if the bin folder is not included in your PATH variable. Step 3: Determine the PID of exaplus This can be done with the system's process monitor (windows task manager) or with the   jps   program that is part of the JDK: Here, we have two running java programs:   jps   itself and something named   Program . The latter is exaplus, with PID 2252. Step 4: Generate the stacktrace The actual stacktrace is produced with   jstack   from the java SDK: jstack's output can also be redirected into a file: Step 5: Attach the generated stacktrace to your ticket with Exasol.
View full article
Top Contributors