The CRM Chap

The CRM Chap

https://crmchap.co.uk

Anything and everything to do with the #PowerPlatform, #MSDYN365, #Azure and more!

Creating a SQL Server Managed Identity Connection in Azure Resource Manager Templates

Published

Featured image of post Creating a SQL Server Managed Identity Connection in Azure Resource Manager Templates

Suppose you have a sizeable investment within Microsoft Azure and, as part of this, you’re authenticating between different services within the platform. In that case, it’s essential to leverage Managed Identities as much as possible. They provide several distinct yet valuable benefits compared to different authentication approaches typically made available to us. For example:

  • They negate the need for us to maintain, recycle and put at risk credentials to access our different services on Azure; instead, the platform seamlessly handles authentication from one service to the other, with no need to perform additional authentication or authorization.
  • They support full compatibility with Azure Role-Based Access Controls (RBAC), thereby allowing us to assign them targeted roles against our resource(s) in much the same way as a standard user account.
  • When leveraging system-assigned Managed Identities, Microsoft will automatically handle the persistence and eventual removal of the identity should we choose to delete the resource in the future.
  • Like all the best things in life, there is no additional cost for their usage across all supported resource types.

Typical scenarios where I like to leverage them include having Azure Data Factory access a specific blob container or an Azure resource with a dedicated service account on a SQL Database, with the correct set of privileges assigned. There are many diverse usage scenarios for Managed Identities, and developers have no good excuse not to explore their potential usage when building out their solution.

I say explore very deliberately here. 😏 There will be some scenarios where they are not supported, or we face potential difficulty rolling them out. I had this very issue recently when I was attempting to deploy a Logic App leveraging a SQL Server Managed Identity connection. As this functionality is still in preview, it’s not unreasonable to expect that we may encounter some difficulties with it. The main challenge was defining the correct configuration within my Resource Manager (RM) template file. After some trial, error and poking around within the REST API, the solution turned out to be specific parameterValueSet values that we had to define when creating the appropriate Microsoft.Web/connections resource. Below is an example of how this needs to look:

{
    "type": "Microsoft.Web/connections",
    "apiVersion": "2016-06-01",
    "name": "MySQLServerConnection",
    "location": "[resourceGroup().location]",
    "properties": {
        "displayName": "SQL Server API Connection",
        "customParameterValues": {},
        "api": {
            "id": "[concat('/subscriptions/', subscription().subscriptionId, '/providers/Microsoft.Web/locations/', resourceGroup().location, '/managedApis/sql')]",
            "type": "Microsoft.Web/locations/managedApis"
            },
        "parameterValueSet": {
            "name": "oauthMI",
            "values": {}
        }
    },
    "tags": {
        "displayName": "SQL Server API Connection"
    }
}

From there, we can then look to leverage this as part of our Logic App in the following manner:

{
    "type": "Microsoft.Logic/workflows",
    "apiVersion": "2017-07-01",
    "name": "MyLogicApp",
    "location": "[resourceGroup().location]",
    "tags": {
        "displayName": "Sample Logic App with SQL Server MI Connection",
    },
    "identity": {
        "type": "SystemAssigned"
    },
    "properties": {
        "state": "Enabled",
        "definition": {
            "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
            "contentVersion": "1.0.0.0",
            "parameters": {
                "$sql_ServerName": {
                    "defaultValue": {},
                    "type": "string"
                }
            },
            "triggers": {
              //TODO: Insert trigger action here...
            },
            "actions": {
                "Sample_SQL_Action": {
                    "inputs": {
                        "body": {
                            "MyColumn1": "Test123",
                            "MyColumn2": "Test123"
                        },
                        "host": {
                            "connection": {
                                "name": "@parameters('$connections')['sql']['connectionId']"
                            }
                        },
                        "method": "post",
                        "path": "/v2/datasets/@{encodeURIComponent(encodeURIComponent(parameters('$sql_ServerName')))},@{encodeURIComponent(encodeURIComponent('MyDatabase'))}/tables/@{encodeURIComponent(encodeURIComponent('[dbo].[MyTable]'))}/items"
                    },
                    "runAfter": {},
                    "type": "ApiConnection"
                }
            },
            "outputs": {}
        },
        "parameters": {
            "$connections": {
                "value": {
                    "sql": {
                        "connectionId": "[resourceId('Microsoft.Web/connections', variables('MySQLServerConnection'))]",
                        "connectionName": "MySQLServerConnection",
                        "connectionProperties": {
                            "authentication": {
                                "type": "ManagedServiceIdentity"
                            }
                        },
                        "id": "[concat('/subscriptions/', subscription().subscriptionId, '/providers/Microsoft.Web/locations/', resourceGroup().location, '/managedApis/sql')]"
                    }
                }
            },
            "$sql_ServerName": {
                "value": "[concat(variables('MySQLResourceName'), '.database.windows.net')]"
            }
        }
    }
}

The critical thing to remember here is to ensure that we don’t miss out on the identity options on the Logic App resource. This is the crucial element that will enable the Managed Identity for our Logic App. From there, everything should deploy out cleanly and, after creating the appropriate EXTERNAL PROVIDER user account in our database, our Logic App will have little trouble getting into our database.

Despite the extra legwork involved in getting this particular example working, this does not detract from my original assertion on the benefit of Managed Identities. Any time invested in coming up with this work-around was well invested. And ultimately, the result is a solution that reduces complexity by negating the need to store credentials in the Logic App itself or a Key Vault and makes it easier for us to manage the security of our database user accounts without any compromise involved. I’ll drink to that. 😉🍻

Continue to website...

More from The CRM Chap