Parametrize Azure Key Vault Secret Name for Synapse SQL DB

Alec Brown 0 Reputation points
2023-05-10T08:34:51.4566667+00:00

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.

User's image

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

User's image

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?

Azure Key Vault
Azure Key Vault
An Azure service that is used to manage and protect cryptographic keys and other secrets used by cloud apps and services.
1,159 questions
Azure SQL Database
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,553 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Albert Tanure 0 Reputation points MVP
    2023-05-10T13:42:33.1366667+00:00

    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.

    User's image

    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.

    I hope it can help you.

    Cheers

    0 comments No comments