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
