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

Question

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.

Answer 1

I created Azure sql database and created synapse pool in Azure portal. I created SQL linked service in synapse pool with managed identity authentication. to authenticate with managed identity, I created user in SQL database with synapse pool name using below query

create  user [SynapsePoolName] from  external  provider;

ALTER  ROLE db_owner ADD  MEMBER [SynapsePoolName];

Image for reference:

Linked service:

I published the Linked Service with publish button. Image for reference:

It published successfully.

When I open the linked service after publish, I am getting the parameters.

Image for reference:

I got the parameters in template also.

Image for reference:

It worked in my machine kindly check from your side.