Data factory SQL connection String with keyvault

Question

I exported arm template from Datafactory V2, when Importing the template it is asking me to manually enter SQL database connection string. To minimize the human interaction I made the following changes.

        {
            "name": "[concat(parameters('factoryName'), '/myFactory')]",
            "type": "Microsoft.DataFactory/factories/linkedServices",
            "apiVersion": "2018-06-01",
            "properties": {
                "type": "AzureSqlDatabase",
                "typeProperties": {
                    "connectionString": "[concat('Server=tcp:',parameters('sqlServerName'),'.database.windows.net,1433;Initial Catalog=', parameters('sqlDatabaseName'), ';Persist Security Info=False;User ID=',parameters('sqlServerUserName'),';Password=(password)',';MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30')]",
                    "password": {
                        "type": "AzureKeyVaultSecret",
                        "store": {
                            "referenceName": "AzureKeyVault1",
                            "type": "LinkedServiceReference"
                        },
                        "secretName": "sql-password"
                    }
                }
            },
            "dependsOn": [
                "[concat(variables('factoryId'), '/linkedServices/AzureKeyVault1')]"
            ]
        },

So currently when deploying to Datafactory V2 and test connection to this SQL server, I got

Cannot connect to SQL Database: 'tcp:mysqlserver.database.windows.net,1433', 
Database: 'mydatabase', User: 'admin'. Check the linked service configuration
is correct, and make sure the SQL Database firewall allows the integration runtime to access.
Login failed for user 'admin'., SqlErrorNumber=18456,

If I manually input all the connections in the portal UI, I can easily connect to the database and test successfully so it is not a firewall issue.

Then I think there could be 2 issue:

1.how the password from keyvault is consumed in the connectionstring. I didn't find much information about it online.

2. When I open the created Sql Linked service, I notice the Fully qualified domain name is missing,If i manually add it in then the connection works.

The SQL connection UI

Answer 1

Throwing this as an alternative answer/approach.

Store the connection string in it's entirety in Key Vault. If doing this then the reference would look like:

{
  "name": "[concat(parameters('factoryName'), '/',parameters('connectionNameAdventureWorks'))]",
  "type": "Microsoft.DataFactory/factories/linkedServices",
  "apiVersion": "2018-06-01",
  "properties": {
    "annotations": [],
    "type": "AzureSqlDatabase",
    "typeProperties": {
      "connectionString": {
        "type": "AzureKeyVaultSecret",
        "store": {
          "referenceName": "[variables('azkDataAnalyticsReferenceName')]",
          "type": "LinkedServiceReference"
        },
        "secretName": "[variables('azkAdventureWorksSecretName')]"
      }
    }
  },
  "dependsOn": [
     "[concat(variables('factoryId'), '/linkedServices/',variables('azkDataAnalyticsReferenceName'))]" 
  ]
}

And even more secure approach would be to add Data Factory as a Managed Identity and then run a sql script to add the user If doing this then all there is no need for any credentials to be passed at all.

One downside is if the DataFactory is deleted and recreated then the managed identity permissions would need to be reassigned to the sql database.