Azure SQL database linked service for parameterising server name and db name

Question

Hello,

I am using parameters in the Azure SQL database linked service for server and db name. Though the parameters used are not secured string type, connection string in ARM template for Azure sql db linked service gets into secure mode and thus @{linkedService().sql_server_name} and @{linkedService().sql_db_name} are not assigned. Due to that, I am not able to parameterize the value and also the parameters are not getting deployed to the connection string in higher environments during pipeline deployment. I have done the same for Azure storage and that works but sql db linked service not.

Please refer the screenshots.

Answer 1

Hello @Rajendran, Prakash ,
Thanks for the question and using MS Q&A platform.

As I understand, the problem is that during ARM template creation, your SQL connection string is being turned into secure string when you do not want it to. You do not want it as secure string, because you parameterized the linked service.

So, we need to tell Data Factory to not do that to connection strings. Warning: below solution will apply this to ALL connection strings, not just the single one in question.

In the Data Factory, go to the Management tab. Then under "Source Control" click "ARM template". Then click "Edit parameter configuration". This will bring you to where you can choose what gets parameterized and how.

You are looking for "connectionString" under "Microsoft.DataFactory/factories/linkedServices": {

we want to change it away from

"connectionString": "|:-connectionString:secureString",  

to

"connectionString": "=",  

Then try "Export ARM Template" and see if it works as expected. Check both this specific linked service, and some other one.

Please do let me if you have any queries.

Thanks
Martin

---

• Please don't forget to click on or 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

Answer 2

Hi Martin,

Indeed it made changes to the ARM template parameters by adding the connection properties. Just wondering why the default value is not getting assigned to sql connection string as just like blob storage. At the moment I am passing value as "Integrated Security=False;Encrypt=True;Connection Timeout=30;Data Source=@{linkedService().sql_server_name};Initial Catalog=@{linkedService().sql_db_name}" via release pipeline using ARM template deployment. How can I tell ADF to assign this value by default when it generates ARM template during publish? Or would it be a problem if I manually edit the ARMTemplateforFactory.json file to have default value for sql db linked service?