Connect to a MS Access database secured with a Workgroup security file (MDW) using UCanAccess

Question

I am using UCanAccess driver to connect to an MS Access database without security from Knime software. Now, I need to do the same connection but with a MS Access DB (MDB file) secured by a workgroup security file (MDW).

Anyone knows how to do this using UCanAccess? or maybe using another driver?

When I connect to that database from Power BI, I use an odbc connector with the following url

jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\bsc_uob\Indicadores_UOB_cliente.mdb;SystemDB=C:\bsc_uob\Seguridad.mdw;

But i cannot use odbc driver in knime.

I need something similar for UCanAccess. I tried to do this:

jdbc:ucanaccess://C:\bsc_uob\Indicadores_UOB_cliente.mdb;SystemDB=C:\bsc_uob\Seguridad.mdw

But I got this error:

ERROR Database Connector 0:20 Execute failed: Could not create connection to database: Decoding not supported. Please choose a CodecProvider which supports reading the current database encoding.

Answer 1

I don't have Knime, but I was able to apply the following steps to have LibreOffice Base open an Access .mdb file that was protected with user-level security (ULS).

First, I used the instructions here to do a normal setup of UCanAccess and LibreOffice Base. I verified that I could open an Access database file that was not ULS-protected.

Then I added the following three (3) JAR files to the UCanAccess lib\ folder:

1. jackcess-encrypt-2.1.4.jar (available here)
2. bcprov-jdk15on-1.52.jar (available here)
3. CryptCodecOpener.jar (available here)

so it contained

gord@gord-dv7-xubuntu0:~$ ll Downloads/JDBC/UCanAccess/lib
total 5580
drwxrwxr-x 2 gord gord    4096 Jan 17 05:54 ./
drwxrwxr-x 5 gord gord    4096 Jan 17 05:38 ../
-rw-rw---- 1 gord gord 2902942 Dec  1  2017 bcprov-jdk15on-1.52.jar
-rw-rw---- 1 gord gord  284220 Nov 16  2013 commons-lang-2.6.jar
-rw-rw---- 1 gord gord   62050 Sep 22  2015 commons-logging-1.1.3.jar
-rw-rw---- 1 gord gord    1146 Jan 16 17:40 CryptCodecOpener.jar
-rw-rw---- 1 gord gord 1467326 Oct  8  2013 hsqldb.jar
-rw-rw---- 1 gord gord  882908 Mar 10  2018 jackcess-2.1.11.jar
-rw-rw---- 1 gord gord   86730 Dec  1  2017 jackcess-encrypt-2.1.4.jar

Then I added ;jackcessOpener=com.gordthompson.ucanaccess.crypto.CryptCodecOpener to my connection URL so it became

jdbc:ucanaccess:///home/gord/Documents/protected.mdb;jackcessOpener=com.gordthompson.ucanaccess.crypto.CryptCodecOpener

and I could open a ULS-protected .mdb file.