Qlik Community

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
Partner
Partner

OLE DB - MS Access Database via UNC path

Hi guys,

First post on this super helpful community so please advise if there are any rules I'm missing!

We have an Access database which is stored on a Win 2012 server = server A

We have a Qlik Sense installation on another Win 2012 server = server B

Both servers are separate AWS instances - our security groups have been configured to allow all open ports/no firewall between servers. Windows Firewall has been turned off during the configuration phase

We aim to load data to the Qlik Sense server (server B from the Access database stored on the other server (server A)

Each server is standalone. We are able to ping via IP address and hostname (added a static entry in etc/hosts file as using Google DNS)  between servers

We have a user account called 'audit' in the Administrators group on server A

The Access database is not password protected and is in accdb format.

The database is called test.accdb

The database is stored in C:/database. That directory is NFS shared as 'database' to "Everyone, Administrators, audit" on server A

So... the full path to the database is: C:/database/test.accdb and the UNC path is \\<server IP address>\database\test.accdb

We can map that share as a drive using the \\<server IP address>\database on server B using credentials 'audit' and can read/write to the folder

Now here is the part we're having difficulty with (I've spent the last 6 hours on it and hopefully I'm doing something silly wrong....)

We want to connect from Qlik to this accdb using the OLE DB connector.

We have tried the following providers to no avail:

- Office 12.0 Access Database Engine OLE DB Provider 32bit

- Office 15.0 Access Database Engine OLE DB Provider 32bit

- Microsoft Jet 4.0 OLE DB Provider 32bit

I have used the following configurations (and every combination)....

Data source: \\<IP address>\database\

Data source: <IP address>\database\

Data source: \\<IP address>\database\test.accdb

Data source: <IP address>\database\test.accdb

Data source: \\<hostname>\database\test.accdb

Data source: <hostname>\database\test.accdb

Windows Integrated Security

Specific username password=

Username: <blank>

Password: <blank>

Username: Administrator

Password: <Admin password>

Username: audit

Password: <Audit password>

Username: <hostname>\Administrator

Password: <Admin password>

Username: <hostname>\audit

Password: <Audit password>

No matter which combination we use, we recieve "Test failed" in red text... There is no more information and from looking through the Logs (seeing as it's not noted in the Qlik Sense help) seems to be under %ProgramData%/Qlik/Sense/Log/Engine/System.

====

"ResultText=Error: Failed to load data connection <id> ..... Invalid connection string.

====

Steps completed so far:

- Turn off firewalls on both servers

- Switch Qlik Engine to Legacy mode (https://help.qlik.com/en-US/sense/3.2/Subsystems/Hub/Content/LoadData/disable-standard-mode.htm)

-

I'm hoping someone can help me out here, I will be eternally grateful!

Please let me know if I need to give any further information

Cheers!

Lachlan

1 Reply
Partner
Partner

Re: OLE DB - MS Access Database via UNC path

This looked to be a problem confined to our AWS instances - unsure if there was a problem with the installation itself or the operating systems we're incorrectly configured, however when attempting the same procedures on new installations on Server 2016 and local VMs it seems to be working correctly.