Create AKV linked service to Azure SQL Database with parameters for the database name in Microsoft adf

Question

So in ADF, im trying to create a linked service to an Azure SQL Database where the connection string is stored in the key vault. The connection host many databases so the database name is not declared.

However, I also want to be able to add a parameter that allows me to enter a database name.

However I just don’t know how to do this, and I cant see an option when a Key Vault connection is selected.

The reason why I want a parameter, is because the Linked server is used as a database source in a pipeline, and when I see what tables are available in the drop down its just lists the default system ones. Plus, there are some duplicate table names between databases.

I really want to avoid creating multiple secrets and linked services – so if anyone knows any tricks that would be great. I’m also open to having to “specify dynamic contents in JSON format”

I have tried creating a 2nd linked service that jumps on the back of the original one that declares the key vault secret. So when it comes to selecting the linked service name, i say "the original one", this then allows me to add a parameters. However it just times out, and the connection fails, so i don't think that is the right approach.

Answer 1

If you want to provide connection string in Azure Key vault you should give different connection strings for different databases. So save the password of server in Azure key vault create access policy to data factory to access secret.

Create linked service for Azure key vault linked service in ADF

Select Connection string option and select enter manually option add server name, to enter database details dynamically for that create parameter and select add dynamic content:

Add @linkedService().dbName as dynamic content

To select password, you can use Azure key vault select Key Vault linked service and secret test the connection

In this way you can select multiple databases.