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

Question

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

Alok Thampi 151

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.

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

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.

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

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

Accepted answer

0 additional answers

Your answer

Answer 1

Bhargava-MSFT 31,261 Microsoft Employee Moderator

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.

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 and upvote 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

Alok Thampi 151 Reputation points

2022-11-24T02:09:53.35+00:00

Hello @Bhargava-MSFT ,

Thanks for the details, I tried this one and it works when we need to deploy to other environments (Test or Prod) using Azure DevOps release pipelines.

If we need to deploy the code to Dev Synapse Live, we can just click the 'Publish' button and we don't really require the Azure DevOps to move the code, right?
Or is there a restriction within Synapse where we need to do a DevOps deployment even to Dev environment if there is a connection string defined in the Linked Services?

Thanks,
Alok
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2022-11-24T03:12:21.21+00:00

Hello @Alok Thampi ,
Yes, your understanding is correct. If we need to deploy the code to Dev Synapse Live, we can just click the 'Publish' button from the collaboration branch. "Publish" only works from the collaboration branch.
Please note: if you are using feature branches for your development work, you need to make a pull request to merge the changes from the feature branch.
-------
If this answers your question, please consider accepting the answer by hitting the Accept answer button, as it helps the community.
Alok Thampi 151 Reputation points

2022-11-24T03:29:35.73+00:00

Hi @Bhargava-MSFT ,

Thanks again for the quick feedback.

Yes, so that's where I am facing the issue. The screenshots that I have pasted in the original question is from the Synapse Live / publish branch parameters. Even if I have the dynamic parameters configured in the collaboration branch, clicking the 'Publish' button does not move these dynamic parameters to Synapse Live.

As per my understanding on the previous comment, clicking the Publish button should move the code to Synapse Live and that doesn't work in my case unfortunately. Not sure if I am missing anything else here.

The below screenshot is from Synapse Live.

Could you please advise?

Thanks,
Alok
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2022-11-24T04:20:37.473+00:00

Hello @Alok Thampi ,
This is strange. I never saw this behavior before.
I just tested my Azure SQL-linked connection using dynamic parameters. After publishing from the Collaboration branch to live mode, I could see the dynamic parameters on the synapse live mode.
Do you have any other synapse connected to Git? I just wanted to see if you see the issue with the specific Synapse workspace or if this issue is with all workspaces.
Alok Thampi 151 Reputation points

2022-11-24T06:22:41.97+00:00

Hello @Bhargava-MSFT ,

Indeed strange..!!
I tried one another time and it works after the page was refreshed post clicking the 'Publish' button.
I am sure that I had tried refreshing the page multiple times before also and since I didn't see it working, I raised a question here.

Anyway, it's all good now and I can say that the issue is resolved. :)
You may please mark this issue as closed. Thanks a ton for the quick help on this one.

Thanks,
Alok
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2022-11-24T17:10:03.323+00:00

Hello @Alok Thampi ,
Glad to know that the issue is resolved :)
Thank you for accepting the answer and taking the survey.

Share via

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

0 additional answers

Your answer