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](/images/Azure-e1557238846431.png)
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 moreRelated posts
Azure NetApp Files now stores sensitive data DoD IL5 compliant in Azure US Government regions
Table of Contents Introduction Why Azure NetApp Files? DoD IL5 compliance in Azure Government Azure NetApp Files reaches feature parity betwee...
Enhancements to Azure Monitor Baseline Alerts for Azure Landing Zones
Introduction Welcome to our latest blog post where we dive into a number of exciting new key updates, highlight the new portal accelera...
Azure Cosmos DB Vector Search with DiskANN Part 1: Full Space Search
Vector Search with Azure Cosmos DB Azure Cosmos DB NoSQL features advanced vector indexing and search capabilities powered by DiskANN, a suite...
Azure Developer CLI (azd) – September 2024
This post announces the September release of the Azure Developer CLI (`azd`). Including remote container build support, multiple hooks per eve...
IBM Power Virtual Server and Microsoft Azure Multi-cloud Integration Patterns
IBM Power Virtual Server and Microsoft Azure Multi-cloud Integration Patterns &nbs...
Azure CLI docker container base Linux image is now Azure Linux
Starting from the version 2.64.0 of Azure CLI, the base Linux distribution of Azure CLI is now Azure Linux. Impact of the change ...
Enhancing Data Security and Digital Trust in the Cloud using Azure Services.
Introduction Think of Client-Side Encryption (CSE) as a strategy that has proven to be most effective in augmenting data security and ...
Optimizing Azure Table Storage: Automated Data Clean-up using a PowerShell script with Azure Automat
Scenario This blog’s aim is to manage Table Storage data efficiently. Imagine you have a large Azure Table Storage that accumulates logs from ...
Exciting New Features for GitHub Copilot for Azure
We've been on a roll adding new features to the private preview and we've added some of my favorites in our latest release! Building App...