CICD Deployment Synapse Azure SQL Linked Service with Managed Identity and Parameter breaks

Question

Within my DEV Synapse Workspace I have a Linked Service configured to the ondemand pool with Managed identity and a Parameter:

I deploy this Linked Service through Azure DevOps Pipelines with the marketplace extension and methods described here https://learn.microsoft.com/en-us/azure/synapse-analytics/cicd/continuous-integration-deployment

I configured a template-parameters-definition file in the root of my workspace folder in Git: https://learn.microsoft.com/en-us/azure/synapse-analytics/cicd/continuous-integration-deployment#use-custom-parameters-of-the-workspace-template

And I provide Override Parameters in the deployment to the Acc environment: "Integrated Security=False;Encrypt=True;Connection Timeout=30;Data Source=xxx-acc-ondemand.sql.azuresynapse.net;Initial Catalog=@{linkedService().DatabaseName}"

Deployment completes without errors but the linked service is now configured with SQL Authentication instead of MI. See screenshot:

Trying to connect to the sql-database through this Linked Service results in the following error:

Cannot connect to SQL Database: 'xxx-acc-ondemand.sql.azuresynapse.net', Database: '@{body('dataset2exxx6').DatabaseName}', User: ''. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access. Login failed for user '<token-identified principal>'., SqlErrorNumber=18456,Class=14,State=1, Activity ID: e3xxx6d

Linked Service Template created by workspace publish:

{  
			"name": "[concat(parameters('workspaceName'), '/X_Syn_sqldb')]",  
			"type": "Microsoft.Synapse/workspaces/linkedServices",  
			"apiVersion": "2019-06-01-preview",  
			"properties": {  
				"parameters": {  
					"DatabaseName": {  
						"type": "string"  
					}  
				},  
				"annotations": [],  
				"type": "AzureSqlDatabase",  
				"typeProperties": {  
					"connectionString": {  
						"type": "SecureString",  
						"value": "[parameters('x_Syn_sqldb_properties_typeProperties_connectionString')]"  
					}  
				},  
				"connectVia": {  
					"referenceName": "[parameters('x_Syn_sqldb_properties_connectVia_referenceName')]",  
					"type": "IntegrationRuntimeReference"  
				}  
			},  
			"dependsOn": [  
				"[concat(variables('workspaceId'), '/integrationRuntimes/', parameters('X_Syn_sqldb_properties_connectVia_referenceName'))]"  
			]  
		}  

Any help would be appreciated!

Answer 1

The workaround we now implemented is by storing the Connection Strings in the Key vault and using the database name parameter to build the Key vault Secret Name and return the corresponding connection string to the right database.