none
Trouble creating linked service to on-premise MS Access database with Azure Data Factory V2 (Preview).

    Question

  • I am using Azure Data Factory V2 (Preview) and creating pipelines to shift on-premise data into Azure.  I have created and installed an Integration Runtime (IR) self hosting fine and am successfully moving data out of an on-premise SQL Server database into Azure Blob Storage.

    I need to do the same approach now for a Microsoft Access Database, which for this setup/test is sitting on my C: drive.

    Following instructions here;

    https://docs.microsoft.com/en-us/azure/data-factory/connector-odbc#microsoft-access-source

    Creating a new linked service in the Azure Data Factory UI tool, I'm picking ODBC and connecting via the existing named IR self-hosted runtime.

    For the connection string I'm trying the usual connection strings you'd use.  For example;

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\FolderA\FolderB\MyDatabase.mdb;

    I'm trying double back slashes in the filename path too but no success.

    Failed to connect to the database. Error message: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

    In the Data Factory UI I have also tried using the Advanced JSON Editor to specify all the settings.  Have also tried these in the IR Config Manager Preview tool.

    {
    "name": "kCreateLinkedService",
    "properties": {
    "type": "Odbc",
    "typeProperties": {
    "connectionString": {
    "type": "SecureString",
    "value": "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\\Program Files\\FolderA\\FolderB\\MyDatabase.mdb;Database Password=sometopsecretpwd;"
    },
    "authenticationType": "Anonymous",
    "userName": "<username>",
    "password": {
    "type": "SecureString",
    "value": "<password>"
    }
    },
    "connectVia": {
    "referenceName": "NVDIntegrationRuntime-SelfHosted-x42 ",
    "type": "IntegrationRuntimeReference"
    }
    }
    }

    Any ideas?

    Thanks,

    Richard.


    Friday, April 20, 2018 3:11 PM

All replies

  • It's been a year, have you gotten this to work? I'm in the same boat. I use the similar JSON for my Connection String. 

    I even tried setting up a DSN in my local ODBC Administrator and referencing that in an ODBC Linked Service. Also tried the IR Tool's Diagnostics tab. I get the same error message every time: 

    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.

    I don't get it. I specify the file location (yes, double backslashes are needed to escape the JSON); the file is NOT open by any other process or user; there is no password set on it.

    Any help or advice would be greatly appreciated.

    Regards,


    Todd C - MCSE SQL Server Business Intelligence MCITP BI Dev 2008 MSCTS SQL Server 2005 Please mark posts as answered where appropriate.

    Friday, April 12, 2019 5:14 PM
  • I have managed to read from Access.

    Steps:

    1. Install an integration runtime
    2. ***Install Microsoft Access Database Engine***  (while you are waiting for the Registry to stabilize, do step 3)
    3. Export as Access file into a place your Integration Runtime can find
    4. Test the connection in the Integration Runtime Manager (make sure the Registry has stabilized first)
    5. Set up and test the Linked Service Connection in data factory.  The connection string is the same as you used in step 4.  Don't forget to set the Integration Runtime to be the self-hosted one instead of Auto-Resolve).
    6. Set up and test the Dataset in data factory.  You may need to enter the table name by hand.
    7. click preview in the dataset


    Monday, April 15, 2019 10:04 PM
  • Yeah, the file location was the key, It needs to be 'neutral', as NOT under any particular user folder.

    Todd C - MCSE SQL Server Business Intelligence MCITP BI Dev 2008 MSCTS SQL Server 2005 Please mark posts as answered where appropriate.

    Monday, April 15, 2019 10:06 PM
  • I got that error before I discovered I had to install the MIcrosoft Access Database Engine.
    Monday, April 15, 2019 10:44 PM
  • Somewhere along the line I tried "Access Database" feature in Microsoft Access.  I'm not sure if this influenced the outcome.  Honestly this is the first time I've used Access since I was in college.
    Monday, April 15, 2019 10:50 PM