Unable to create dynamic linked service for Microsoft Access database

Question

Hi Team

I am trying to create Linked service for Microsoft Access database using dynamic connection string where i am passing connection string value through parameter, test connection working fine but I am getting following error while saving the linked service..

Failed to encrypt sub-resource payload { "Id": "/subscriptions/bvdfvbdfbv773844fvvvvdf/resourceGroups/test-resourcegroup/providers/Microsoft.DataFactory/factories/poc-msaccess/linkedservices/access_db", "Name": "access_db", "Properties": { "parameters": { "file_path": { "type": "String", "defaultValue": "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\Users\aaaaa\Downloads\ms_access_files\demo.accdb;" } }, "annotations": [], "type": "MicrosoftAccess", "typeProperties": { "connectionString": "", "authenticationType": "", "userName": "", "password": "" }, "connectVia": { "referenceName": "onprem-ir", "type": "IntegrationRuntimeReference" } } } and error is: Failed to encrypted linked service credentials on self-hosted IR 'onprem-ir', reason is: InternalServerError, error message is: Internal Server Error..

I am stuck on this issue from many weeks. Any help with this issue will be highly appreciated.

Thanks,

Dinesh

Answer 1

Hello Dinesh Kumar,

Thanks for posting your question in the Microsoft Q&A forum.

Ensure your self-hosted IR is up-to-date and running, Open the Integration Runtime Configuration Manager on the machine where it's installed and check the status and version.

Make sure the user account running the IR service has sufficient permissions (The IR service typically runs under the NT SERVICE\DIAHostService account)

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful

Answer 2

Hello @Dinesh Kumar ,

It seems like there is an issue with encrypting the credentials for the linked service. The error message suggests that there is an internal server error while encrypting the credentials on the self-hosted integration runtime onprem-ir. To troubleshoot this issue, you can try the following steps:

• Check if the self-hosted integration runtime onprem-ir is running and connected to the Data Factory.
• Check if the credentials you are using to connect to the Microsoft Access database are correct and have the necessary permissions.
• Try creating a new linked service with the same parameters and see if you are still facing the same issue.
• If the issue persists, you can try contacting Microsoft support for further assistance.

---

I hope that this response has addressed your query and helped you overcome your challenges. If so, please mark this response as Answered. This will not only acknowledge our efforts, but also assist other community members who may be looking for similar solutions.

Answer 3

The issue was resolved after I used Azure Key Vault to provide the password, instead of directly specifying it in the Password property.

Below is the error message I found in the log of the self-hosted integration runtime server.

Property name: password, Exception: 'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Failed to convert the value 'Masked Secret' in 'password' property to 'Microsoft.DataTransfer.Security.SensitiveString' type. Please make sure the payload structure and value are correct.,Source=Microsoft.DataTransfer.DataContracts,StackTrace= at Microsoft.DataTransfer.Common.Shared.HelperMethod.ConvertTypeT