How to connect my DataFactory instance to an Access Database?-CodePudding

So I'm setting up a dataflow to pull data from an Access DB and into my Datafactory and then pushing to the lake/datawarehouses.

I've set up an integration runtime on the VM that has the file on it. I'm using my own credentials for the moment to try and get the connection working.

I started using this guide: https://docs.microsoft.com/en-us/azure/data-factory/connector-microsoft-access?tabs=data-factory

To get things set up. This is the connection string:

Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\Users\<myusername>\Documents\test_access.accdb;

Athentication is Basic, with my username and password. I've tested the credentials for a regular filesystem linked service in Datafactory and they work fine.

I've installed Access on the VM and opened the database. Checked and reinstalled drivers, ensured that the Integration Runtime user and my user have access to the HKEY_LOCAL_MACHINE\SOFTWARE\ODBC registry key. The integration runtime is working for other files/linked services on datafactory. All this said, I keep on getting this error:

Error code
9603
Details
ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x83c Thread 0x132c DBC 0x6c7884e8 Jet'. ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x83c Thread 0x132c DBC 0x6c7884e8 Jet'. ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] The Microsoft Access database engine cannot open or write to the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view and write its data. ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x83c Thread 0x132c DBC 0x6c7884e8 Jet'. ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x83c Thread 0x132c DBC 0x6c7884e8 Jet'. ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] The Microsoft Access database engine cannot open or write to the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view and write its data. Activity ID: 1ac4d9fc-17ee-4e0d-b5cc-4e0a2d6931da.

I only need read access. I've saved a copy to reference, so it's not locked by another user. I've logged onto the VM and opened the same database with that user, so it doesn't seem like it can be a permission error. I'm not sure what I can try next.

I've found that no matter what user/password combination I put in, I still get the same error, so leaning towards a driver issue perhaps?

CodePudding user response：

I figured it out.

The NT Service\DIAHostService account is the service account for the Integration Runtime. Now, even though in Datafactory, you need to specify a particular user who will open the Access database. It turns out the service account running the IR also needs full (read and write) access to the file in order for the connection to succeed.

I want to read files—and not just locally—so I need to run the IR using my own user. So I reinstalled IR, granted my own user permissions to the IR config files, ensured this user and the user input on Datafactory have read and write access to the Access database, and finally things are working.