Search code examples
ms-accessazure-virtual-machineazure-data-factoryon-premises-instances

Azure Data Factory - connect to onprem accdb

I need to retrieve data from Access onprem database by using adf. I have integration runtime installed on azure vm. Access database is on the other virtual machine. I know that on the azure virtual machine I should have a path to that Access database but how to do that?

Solution

  • I know that on the azure virtual machine I should have a path to that Access database but how to do that?

    • To connect on-premises Access database with Data factory you need to install SHIR on that particular Virtual Machine. Only for port 443 outbound traffic. This may be open by default.
    • The Integration Runtime Machine needs to have the Microsoft Access ODBC driver installed for the data storage.

    Microsoft Access 2016 version of ODBC driver doesn't work with this connector. Use Microsoft Access 2013 or 2010 version of ODBC driver instead.

    • In linked service select that particular SHIR hosted on that particular Virtual Machine.
    • Provide correct values to the linked service properties like connectionString, authenticationType.

    Reference- Copy data from and to Microsoft Access using Azure Data Factory follow this official document.