How to get/set parameterized connection string from Key Vault in Azure Data Factory?

1
votes

I have a parameterized connection string in Azure Data Factory Linked Services as below:

Integrated Security=False;Encrypt=True;Connection Timeout=30;Data Source=xyz;User ID=admin;Password=password;Initial Catalog=@{linkedService().LSDBName};

The value for database is passed from a pipeline variable at runtime.

I want to save this connection string to Azure Key Vault, but the issue is that after the value is read from the key vault, the linked service parameter "LSDBName" is not dynamically replaced by the actual value and it tries to connect to "@{linkedService().LSDBName}" as the database name.

Is there any way to secure a dynamically parameterized connection string in key vault? Or a workaround to achieve this?

Thanks!

2
azureazure-data-factoryazure-keyvaultazure-data-factory-2
What are you using for your pipeline software? Azure Devops? – David C
How did you save this connection string to AKV? – Jay Gong
I've got the exact same issue. Did you ever resolve this? – Lee Harrison
@DavidC I'm using Azure Data Factory. – coder_andy
@LeeHarrison This solution did not work for us. We had to move only password to AKV and had the rest of the connection string injected from ARM template parameter. – coder_andy

2 Answers

1
votes

If you want to store the entire connection string in key vault then you have to pass the connection string in "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;" format. Create separate connection string for each database and store it in key vault with different secrets and then create parameterized linked service in ADF, giving these secrets as parameter.

-1
votes

My idea is using Set Variable Activity+Azure Function Activity.

First step is using Set Variable Activity to get the LinkedService Connection String.

Second Step is passing the variable as parameter into Azure Function Activity. Then use AKV sdk to store the connection string value in the azure function inside.

Incidentally, I think your connection string has been parameterized already, security issues have been avoided. You don't have to store it into AKV again because mostly we read private information from AKV, rather than write store information into AKV in ADF. Just my own opinion.

enter image description here