Azure Data Factory: Get UserName in Linked Service using Azure Key Vault Reference, is it possible?

Question

I know that you can create an Azure Key Vault Linked Service in ADF that can then be used in other linked services to get the Password Secret/Connection String secret. This is what the JSON looks like when you retrieve the Password from Keyvault secret for a FILE SYSTEM linked service (refer to screenshot for ADF UI snippet):

{
"name": "FILESERVER_blah",
"properties": {
    "annotations": [],
    "type": "FileServer",
    "typeProperties": {
        "host": "\\\\ServerName\\ShareName",
        "userId": "ThisIsMyUserName@blah.com",
        "password": {
            "type": "AzureKeyVaultSecret",
            "store": {
                "referenceName": "KEYVAULT_maddisandbox",
                "type": "LinkedServiceReference"
            },
            "secretName": "MySecretName"
        }
    },
    "connectVia": {
        "referenceName": "shir-madie-sandbox",
        "type": "IntegrationRuntimeReference"
    }
}}

With this logic I figured that I could just create a UserName secret in KeyVault and reference it in the same manner. So I updated the Linked Service Json to the following:

{
"name": "FILESERVER_akv_dynamic",
"properties": {
    "type": "FileServer",
    "annotations": [],
    "parameters": {
        "UserSecretName": {
            "type": "string",
            "defaultValue": ""
        },
        "PasswordSecretName": {
            "type": "string",
            "defaultValue": ""
        },
        "Host": {
            "type": "string",
            "defaultValue": ""
        }
    },
    "typeProperties": {
        "host": "@linkedService().Host",
        "userId": {
            "type": "AzureKeyVaultSecret",
            "store": {
                "referenceName": "KEYVAULT_maddisandbox",
                "type": "LinkedServiceReference"
            },
            "secretName": "@linkedService().UserSecretName"
        },
        "password": {
            "type": "AzureKeyVaultSecret",
            "store": {
                "referenceName": "KEYVAULT_maddisandbox",
                "type": "LinkedServiceReference"
            },
            "secretName": "@linkedService().PasswordSecretName"
        }
    },
    "connectVia": {
        "referenceName": "shir-madie-sandbox",
        "type": "IntegrationRuntimeReference"
    }
}}

When I do this and attempt to run the test connection, it comes back with this error:

Failed to convert the value in 'userid' property to 'System.String' type. Please make sure the payload structure and value are correct.

I am aware I can use KeyVault REST API to get the Secret value and pass that on to my Activities/Datasets/Linked Services, but I would much rather use native functionality and save a step by referencing straight in the Linked service Json, especially since I have the KeyVault linked service set up in my factory.

Can someone explain how to make this work if possible? And if it is not possible why it is not possible? How is the password secret being handled differently than the UserName secret I am trying to retrieve?

Thank you

KranthiPakala-MSFT KranthiPakala-MSFT · Accepted Answer · 2021-04-15T18:48:33

Currently it is by design that only the secrets or connection string (For connectors using connection string in linked service like SQL Server, Blob storage, etc., you can choose either to store only the secret field e.g. password in AKV, or to store the entire connection string in AKV. You can find both options on the UI.) are allowed to be retrieved from AKV by ADF linked service.

Hence it is not possible to retrieve a username from AKV by an ADF linked service. It is a design limitation.

Related MS doc: Store credential in Azure Key Vault

But if you have any specific feedback regarding the same, please feel free to share your suggestion/idea in ADF user voice forum: https://feedback.azure.com/forums/270578-azure-data-factory.

Azure Data Factory: Get UserName in Linked Service using Azure Key Vault Reference, is it possible?

1 Answers