Connection string for parameterized Linked Services - Azure SQL / Azure Synapse

Alok Thampi 111 Reputation points
2022-11-22T16:31:49.537+00:00

Hi,

I have a requirement where in I need to parameterize my Azure SQL/Azure Synapse linked service using 2 parameters 'DBServer' and 'DBName'. I configured my collaboration branch as below and everything works as expected in my Synapse workspace.

263088-image.png

Even the linked service ARM template shows the connection string values as expected.

263069-image.png

However when I publish the code (using the publish button in Synapse workspace), the code in the Synapse Live mode does not contain these parameters defined. Please see the snippet below.

263039-image.png

Even the code in the TemplateParameter file of the publish branch is blank.

263172-image.png

I tried removing the parameters and put in the actual values for Server and DBName, it still shows no values in the publish branch/Live mode. While I understand that the git mode does not store secrets, this case of mine does not have any secrets and I am authenticating via Managed Identity.
Is there a solution to deal with this scenario? I would not prefer to store the connection string in KeyVault and refer it back in the linked service as it does not make the solution completely dynamic.

Thanks in advance,
Alok

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,681 questions
0 comments No comments
{count} vote

Accepted answer
  1. Bhargava-MSFT 28,956 Reputation points Microsoft Employee
    2022-11-23T22:14:16.443+00:00

    Hello @Alok Thampi ,

    Welcome to the MS Q&A platform.

    You seem to have not defined the connection string on the override template parameters.

    Please use this override template parameter in the SQL connection string:

    "Data Source=@{linkedService().DBServer};Initial Catalog=@{linkedService().DBName}" to reflect the below values shown in my screenshot.

    263594-image.png

    In case if you are using SQL authentication, please use this:

    Ex:
    "Data Source=@{linkedService().DBServer};Initial Catalog=@{linkedService().DBName;Persist Security Info=False;User ID=YourUserID;Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

    I hope this helps. Please let me know if you have any further questions.

    ------------------------------

    • Please don't forget to click on 130616-image.png and upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification
    • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful