Parametrize Azure Key Vault Secret Name for Synapse SQL DB

Question

I am trying to set up a generic Azure SQL Database connection in Synapse with parametrized fields but I get connection failed when parametrizing the secret name.

This is my current set up, with server, database and service account taken from parameters set up in the linked service.

The Secret name gives me the option for 'add dynamic content' and allows me to select this parameter, but when I test the connection I get this error

Is the linkedService out of scope for passing secrets to the keyvault from the parameter? If I remove that parameter and hardcode the keyvault secret in, the connection tests fine. Unfortunately this means I'll need to have a linked service for every connection, rather than a generic one I can just pass these values through to.

Is what I'm trying to do even possible?

Answer 1

Hi Alec, how are you?

This integration is possible, yes, however, there are some prerequisites.

I just wanted to list them to make sure everything was set up correctly.

1. The Synapse or DataFactory resource must have permission to access the Key Vault. This can be done by accessing the Key Vault -> Access Policy -> Create. Then you should configure the necessary permission (suggestion to keep the settings as strict as possible). In the case of querying Secretes, only "Get" and "List" permissions are needed. The permission is associated with an identity, configured in Azure AD. Synapese or DataFactory has a menu called "Properties" and you can get the value of the "Object ID", which is the identity reference that must be associated in the Key Vault access policies
2. The Database you want to access must be accessible to Azure resources or the network settings, NSG, must allow with this connectivity.
3. During the Linked Server configuration process, it will be possible to associate the Key Vault to the service and, consequently, select the variable that contains the desired secret.

As you can see in this image, I configured an Azure Key Vault and I'm getting the secret dbsecret from the KeyVault. At the bottom of the image, you can see the connection was successfully.

• You can check the following references as well:
  https://learn.microsoft.com/en-us/azure/data-factory/store-credentials-in-key-vault#prerequisites
• https://learn.microsoft.com/en-us/azure/data-factory/parameterize-linked-services?tabs=data-factory

I hope it can help you.

Cheers