SQL API connection using AD integrated ARM template

[greatvivek11]

I need to automate the deployment of my logic app(consumption) which is using an SQL connector (ISE) trigger to connect with Azure SQL Server/Db using AD Integrated authentication.

When using the portal, I'm able to make connections but I don't know how to create the ARM template for this. I have looked into MS Docs and all the options point to either basic or windows authentication for the SQL server. I checked for AD too(which is in Non-ISE) connector but that wouldn't work for me as I don't have token:clientId and secret.

Can MS please create documentation for SQL API connector using AD-integrated auth?

This is work-related that's why I can't share specifics and I can't recreate the logic app workflow in my personal azure directory.

[satchsid]

@greatvivek11 Here is the Code to promote Azure Ad-integrated with Oauth but you would still have to authorize the connection from Azure Portal on the resource group. Here is the code that I have used for Logic app. Need to set the "ParametervalueSet" to oauth. { "type": "Microsoft.Web/connections", "apiVersion": "2016-06-01", "name": "[parameters('sql_8_Connection_Name')]", "location": "[parameters('logicAppLocation')]", "properties": { "displayName": "[parameters('sql_8_Connection_DisplayName')]", "customParameterValues": {}, "api": { "id": "[concat('/subscriptions/', subscription().subscriptionId, '/providers/Microsoft.Web/locations/', resourceGroup().location, '/managedApis/sql')]", "type": "Microsoft.Web/locations/managedApis" }, "parameterValueSet": { "name": "oauth", "values": {} } } }

[mycloud91]

Any solution is provided for the same in ARM template. Also trying to automate in terraform and it not possible.

[jorgeafc00h]

I'd like to know if there's another workaround , that can be automated with Azure CLI , or something, to avoid manual interaction,

[mycloud91]

@jorgeafc00h you can use the ARM template to automate. as the code given by @satchsid. however, if you use a service principal to authenticate SQL API connection. you have to set "ParametervalueSet" to "oauthSP"

[greatvivek11]

@jorgeafc00h @SaravananGuru Yes, we can use the ServicePrincipal Auth method to authenticate our logic apps with SQL Server. Follow below steps -

1. Get an SP created and get it assigned SQL server contributor roles to respective SQL instances in Azure or ask the SQL team to provide necessary privileges to that SP.
2. Keep the SP credentials in keyvault and reference them in your devops pipeline.
3. In Logic app use below code in SQL connection block { "parameterValueSet":{ "name":"oauthSP", "values":{ "token:TenantId":{ "value":"[subscription().tenantId]" }, "token:clientId":{ "value":"[parameters(clientId)]" }, "token:clientSecret":{ "value":"[parameters(clientSecret)]" } } } }