ADF error when reusing parameter in replace function

Question

I am parameterizing a Linked Service (SQL server) in ADF, but have trouble reusing parameters for different service properties as Dynamic Content.

I have created more parameters for the SQL Server properties:

• ServerName
• Environment
• DatabaseName
• DBUserName

A Key Vault is used to store sensitive information for the properties, where the Secret names are created like like "POC-USER-MYDOMAIN-MYUSER".

The DBUserName parameter for the Linked Service contains a Windows Login like "MyDomain\MyUser". I use the DBUserName parameter for property "User name" and for the password stored in Key Vault.

Property "User name" has this dynamic content "@{linkedService().DBUserName}", and the Key Vault Secret name has this dynamic content "@{linkedService().Environment}-USER-@{replace(linkedService().DBUserName, '', '-')}".

Linked service

When execute "Test connection" I use these parameters:

Parameters

And "Test connection" returns this error:

Error

I can get it working, if I create a new parameter named "DBUserNameCopy", copy value from "DBUserName". Then change either property "User name" or property "Key Vault Secret name" dynamic content to use the new parameter. And execute "Test connection" with:

DoubleParameters

So the two properties dynamic content is working correct, but only if they don't share one parameter.

I tried different things to avoid this error, but ended up with the conclusion: You can not use same parameter in more properties, if you use the replace function (I don't know if it's related to all functions).

Anyone know how to get this to work?

Answer 1

I tried this scenario, and it seems that you cannot use the same linked service parameter in two dynamic expressions. In your case you used the DBUsername twice, once in the user name dynamic expression and the second in constructing the key vault secret name. Aside from your workaround, to create a parameter with a different name, I would manipulate the value you pass to the key vault secret name parameter outside the linked service, do this in the data set that references the linked service, in the data set definition, include the dynamic expression that prepares the parameter value.