3 min read
Use Azure DevOps Self Hosted agents with Azure App Service access restrictions
By default, when you deploy a new Azure WebApp, Function app or API app it will be publicly available to the internet. For the current customer I’m...
2 min read
I was recently looking at a way to automatically set an Azure AD group as the SQL admin for our Azure SQL databases with ARM tempplates. We use SQL authentication and Azure AD authentication for our SQL databases. The password for the SQL admin gets generated randomly as part of our pipeline and stored in Keyvault. We also have a dedicated team of SQL DBAs who would need to connect to the deployed SQL resources using their Azure AD credentials. Technically it is possible as per this link to set an Azure AD group as the SQL admins but I could not find a good example on how to do this with ARM.
You will need 3 things to be able to set an Azure AD SQL admin:
- The tenantID. This is your Azure AD tenant ID and will need to be passed on to the tenantId property.
- ObjectID of the group. You can find this in your AzureAD group details and needs to be passed on to the sid property.
- Name of the group.
End to end ARM template example
If you take the example provided in the link above it’s not going to work. You need to specify the SQL server name before ActiveDirectory. To do this we use a concat function to concatenate the servername with the ActiveDirectory name like this:
"name": "[concat(variables('sqlServerName'),'/ActiveDirectory')]"
Below you can find an end to end solution to deploy a new Azure SQL database and immediately setting an Azure AD group as the SQL admins.
{ "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#", "contentVersion": "1.0.0.0", "parameters": { "guidValue": { "type": "string", "defaultValue": "[newGuid()]" }, "SQLNamePrefix": { "type": "string", "defaultValue": "[resourceGroup().name]" }, "sqlAdministratorLogin": { "type": "string", "defaultValue": "SQLAdmin", "metadata": { "description": "The administrator username of the SQL Server." } }, "sqlAdministratorLoginPassword": { "type": "securestring", "metadata": { "description": "The administrator password of the SQL Server." } }, "location": { "type": "string", "defaultValue": "[resourceGroup().location]", "metadata": { "description": "Location for all resources." } }, "LinkedTemplatePrefix": { "type": "string" } }, "variables": { "sqlServerName": "[take(concat('sql-',parameters('SQLNamePrefix'),'-',uniqueString(parameters('guidValue'))),32)]", "ADtenantID": "91xxxbe-xxx-43bf-axxx0-c2fxxx47349", "ADobjectID": "3exxc5-17xx3-xxb-9axxb2-e1xxx74b76", "ADLogin": "Applications Team - Database Administrator" }, "resources": [ { "name": "[variables('sqlServerName')]", "type": "Microsoft.Sql/servers", "apiVersion": "2019-06-01-preview", "location": "[parameters('location')]", "tags": { "displayName": "SqlServer" }, "properties": { "administratorLogin": "[parameters('sqlAdministratorLogin')]", "administratorLoginPassword": "[parameters('sqlAdministratorLoginPassword')]", "version": "12.0" } }, { "name": "[concat(variables('sqlServerName'),'/ActiveDirectory')]", "type": "Microsoft.Sql/servers/administrators", "dependsOn": [ "[resourceId('Microsoft.Sql/servers', variables('sqlServerName'))]" ], "apiVersion": "2019-06-01-preview", "properties": { "administratorType": "ActiveDirectory", "login": "[variables('ADLogin')]", "sid": "[variables('ADobjectID')]", "tenantId": "[variables('ADtenantID')]" } } ], "outputs": { "sqlServerFqdn": { "type": "string", "value": "[reference(concat('Microsoft.Sql/servers/', variables('sqlServerName'))).fullyQualifiedDomainName]" }, "sqlServerName": { "type": "string", "value": "[variables('sqlServerName')]" } } } And as you can see, when we deploy a new Azure SQL database the AD admin will be set automatically as part of our template deployment:
Alex
2 min read
Create an Automatic Service Principal Azure RM Service Connection in Azure DevOps via Azure CLI
With more and more of our development and infrastructure projects being built and released via Azure DevOps, I find myself creating a few DevOps...
9 min read
Auth Series #5 - Call Microsoft Entra ID/Azure AD Protected Web API via ASP.NET Core MVC using Authorization Code Flow
Auth Series #5 - Call Microsoft Entra ID/Azure AD Protected Web API via ASP.NET Core MVC using Authorization Code Flow This is the 5th tutorial...