Loading...

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

Creating a SQL Server Managed Identity Connection in Azure Resource Manager Templates
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. 😉🍻

Published on:

Learn more
The CRM Chap
The CRM Chap

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

Share post:

Related posts

Send emails via SMTP relay with Azure Communication Service

We’ve come across multiple cases where customers want to send emails from Applications migrated to Azure through some kind of SMTP service. Th...

2 hours ago

Five Key Updates on WS2012 ESUs enabled by Azure Arc

We have a myriad of key updates for customers enrolled in WS2012/R2 ESUs enabled by Azure Arc! As we continue to refine and expand the offer, ...

19 hours ago

Improve security posture in Azure service connections with AzurePipelinesCredential

Learn about the new AzurePipelinesCredential, designed to support federated identity credential authentication through Azure Service Connectio...

21 hours ago

Join us in July for Securing AI Apps on Azure!

Join us in July for a series of free live-streams sharing best practices for securing your AI apps on Azure. You'll learn about managed id...

1 day ago

Announcing Azure Monitoring Agent support in Azure Landing Zones

Introduction   Hello and welcome to another blog post about Azure Landing Zones, the best practice framework for accelerating your cloud...

4 days ago

Out-of-band updates to address Azure Synapse SQL issue caused by Windows updates

Microsoft has issued an out-of-band update to resolve an issue with Azure Synapse SQL Serverless Pool databases entering a "Recovery pending" ...

4 days ago

[Mitigated] Azure Lab Services - Lab Plan Outage

Azure Lab Service is experiencing an outage that is affecting Lab Plans, but not Lab Accounts. This outage intermittently impacts all operatio...

4 days ago
Stay up to date with latest Microsoft Dynamics 365 and Power Platform news!
* Yes, I agree to the privacy policy