How to parameterise full connection string in Azure SQL Database linked service

Question

Guides I've seen this show how to parameterise the components of the connection string, like the database name or user, but I want to make the whole connection string a parameter. When I try this, it doesn't let me save the JSON because it tries to validate the format of the connection string. Is there a way to do this? I can only fetch a full connection string during the pipeline. Perhaps as an alternative someone could suggest how to break down the connection string within the pipeline so the separate parameters can be used?

Answer 1

Hi @Steven Darby ,

Welcome to Microsoft Q&A and thanks for your query.

As per my analysis, I don't see a way to parameterize the whole connection string directly. But as a workaround you can store the complete connection string in Azure Key vault and and parameterize the azure key vault secret value in your Azure SQL linked service as shown below.

Then while using this parameterized linked service in your dataset you can pass secret values dynamically from pipeline parameters -> dataset parameters -> linked service parameters as shown in below GIF.

Hope this helps. Do le t us know if you have further query.

Thank you.

----------

Please do consider to click on "Accept Answer" and "Upvote" on the post that helps you, as it can be beneficial to other community members.