Integrate logic apps with SQL server by API connection using credentials kept in key vault.
Published Apr 06 2023 08:01 PM 3,166 Views
Microsoft

Introduction:

===================================================================================

This blog introduces the detailed procedures to Integrate logic apps with SQL server by API connection using credentials kept in key vault. 

 

Solution

===================================================================================

STEP1: As we chose to use “SQL server Authentication”, please save the SQL server’s password in KeyVault:

Serena_Li_0-1680768304255.png

 

STEP2: I fetched the ARM template for SQL-API connection from network trace during API connection creating process:

 

(Note: Please change the highlighted part as your environment)

 

templatea.json

 

{

              "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",

              "contentVersion": "1.0.0.0",

              "parameters": {

                            "connections_sql_name": {

                                          "defaultValue": "sql-5",

                                          "type": "String"

                            },

                            "password": {

                                          "type": "String"

                                         

                            }

              },

              "variables": {},

              "resources": [

                            {

                                          "properties": {

                                                        "api": {

                                                                      "id": "/subscriptions/a781a931-d3dc-4bd0-8f43-05528dd0b6fb/providers/Microsoft.Web/locations/eastus/managedApis/sql"

                                                        },

                                                        "parameterValueSet": {

                                                                      "name": "sqlAuthentication",

                                                                      "values": {

                                                                                    "server": {

                                                                                                  "value": "serena0227sqloauth.database.windows.net"

                                                                                    },

                                                                                    "database": {

                                                                                                  "value": "keyvaulttest"

                                                                                    },

                                                                                    "username": {

                                                                                                  "value": "serenaliqing"

                                                                                    },

                                                                                    "password": {

                                                                                                  "value": "[parameters('password')]"

                                                                                    }

                                                                      }

                                                        },

                                                        "displayName": "testarm"

                                          },

                                          "kind": "V2",

                                          "location": "eastus",

                                          "type": "Microsoft.Web/connections",

                                          "apiVersion": "2016-06-01",

                                          "name": "[parameters('connections_sql_name')]"

                            }

              ]

}

 

templatea.parameters.json

 

{

    "$schema": https://schema.management.azure.com/schemas/2019-04-01/deploymentParameters.json#,

    "contentVersion": "1.0.0.0",

    "parameters": {

        "connections_sql_name": {

            "value": "sql-5"

        },

        "password": {

            "reference": {

                                                        "keyVault": {

                                                                      "id": "/subscriptions/a781a931-d3dc-4bd0-8f43-05528dd0b6fb/resourceGroups/KeyVault-APIConnection-Test/providers/Microsoft.KeyVault/vaults/test0621keyvault"

                                                        },

                                                        "secretName": "password"

                                          }

        }

    }

}

 

STEP3:Use the  Powershell command to deploy both files: New-AzResourceGroupDeployment -ResourceGroupName KeyVault-APIConnection-Test -TemplateFile "templatea.json" -TemplateParameterFile "templatea.parameters.json"

 

Serena_Li_1-1680768304280.png

 

 

STEP4: Check the deployed API connection in portal and add access policy:

 

Serena_Li_2-1680768304304.png

 

 

Serena_Li_3-1680768304316.png

 

STEP5: Test the deployed API connection, it can works:

 

Serena_Li_4-1680768304324.png

 

References

==================

https://github.com/MicrosoftDocs/azure-docs/blob/main/articles/logic-apps/set-up-devops-deployment-s...

 

Co-Authors
Version history
Last update:
‎Apr 06 2023 07:24 PM
Updated by: