1 Reply Latest reply: Jun 5, 2017 3:40 AM by Lachlan Wells RSS

    OLE DB - MS Access Database via UNC path

    Lachlan Wells

      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