This article contains all the information you might need on using Azure SQL databases through Entity Framework from within an App Service with managed service identities (MSI) configured (where the MSI is used to authenticate with the Azure SQL database) all set-up using Azure Pipelines CI\/CD. There is some information out there on all the different parts but it turns out that a lot of investigation was needed to get everything working together smoothly.<\/p>\n\n\n\n
Read the tutorial created by Microsoft, to see what it takes to do most of what we want manually , here: <\/p>\n\n\n\n
Tutorial: Secure Azure SQL Database connection from App Service using a managed identity<\/a><\/p>\n\n\n\n This is what we want to perform automatically in the deployment pipeline. There are also some other tweaks you might need in your situation and these are described at the end. Anyway, we are going to show the following parts:<\/p>\n\n\n\n We will assume you have a basic understanding of ARM templates and Azure DevOps YAML pipelines throughout this article.<\/p>\n\n\n\n You can find the ARM template reference for SQL Servers and SQL Databases here<\/a>.<\/p>\n\n\n\n We will split up the ARM snippet into a few separate snippets. One for creating the server and two snippets for creating inner resources (a database and an AAD administrator configuration).<\/p>\n\n\n\n The server snippet creates the server and configures a regular administrative login. A regular administrator login is still mandatory (and this is not an AAD login). It is probably best to generate your SQL Admin password during your deployment and write it to a key vault for safekeeping. We will use this account later on when adding our AAD accounts as users to the database.<\/p>\n\n\n\n The following creates a database using the old style DTU based resource configuration.<\/p>\n\n\n\n Note:<\/strong> the API version is set to a relatively old one. Using the newest one has a different syntax and by default creates vCore based databases.<\/p><\/blockquote>\n\n\n\n Though not strictly necessary for adding AAD accounts later on at the database level, you can add one AAD account as server administrator via the ARM template with the following resource:<\/p>\n\n\n\n Now that our database is all in order it is time to configure our App Service with an MSI in its ARM template:<\/p>\n\n\n\n We will need some information regarding the MSI created for our web application in our deployment pipeline so we will also add the following ARM output parameters:<\/p>\n\n\n\n NOTE:<\/strong> Another way to reference this info is described here<\/a> but I have not tried it myself.<\/p><\/blockquote>\n\n\n\n With your ARM template updated, if you didn’t have any output parameters before, you might need to add some tasks to your pipeline. We are using YAML, and the following steps would take care of ARM template deployment and retrieval of the output parameters afterwards:<\/p>\n\n\n\n NOTE:<\/strong> The Now comes the tricky part, actually giving the MSI access to the database. According to the original tutorial we would need to execute the following piece of SQL to add the user (where <identity-name> is the name of the web application, since system assigned MSIs have the same name as their parent web application):<\/p>\n\n\n\n If you were to try to perform this in the pipeline with a SQLCMD operation using the server admin username and password configured in the SQL Server creation step, you would find out there is a sneaky caveat here. You can only add AAD users with this syntax if you are actually logged in to the database with an AAD user yourself. You could only accomplish this if you had made the Azure Pipelines service connection’s service principal the AAD administrator of the SQL Server in the first step. And then somehow access its client ID and secret and supply this to SQLCMD (if that would actually work, I haven’t tried). We need another way, and I wasn’t the first to think so, someone already asked here:<\/p>\n\n\n\n Is there any way to add managed identity as db user from pipelines?<\/a><\/p>\n\n\n\n So our SQL script will now read:<\/p>\n\n\n\n However, we still need to retrieve the SID for the AAD user somewhere. Something you will not find in the MSI object created by the ARM template. You will also not find it in the service principal properties page in your AAD (the MSI is just a glorified service principal with an application registration). The link provided in the Microsoft Docs github issue above comes to the rescue:<\/p>\n\n\n\n Can’t Create Azure SQL Database Users Mapped to Azure AD Identities using Service Principal<\/a><\/p>\n\n\n\n Though the above powershell script will work to create a correct SID for AAD users and AAD groups object IDs. If you perform this on the object ID of a MSI (the When putting this all together in a nice little YAML template for reuse:<\/p>\n\n\n\n NOTE:<\/strong> The The template can now be called as follows:<\/p>\n\n\n\n This is actually not that different from the original tutorial. I’ve been implementing all this stuff in a ASP.NET Core 3.1 application so the steps are basically this:<\/p>\n\n\n\nConfigure Azure SQL via an ARM template<\/h1>\n\n\n\n
SQL Server<\/h2>\n\n\n\n
{\n \"type\": \"Microsoft.Sql\/servers\",\n \"kind\": \"v12.0\",\n \"name\": \"[parameters('SqlServerName')]\",\n \"apiVersion\": \"2019-06-01-preview\",\n \"location\": \"[resourceGroup().location]\",\n \"properties\": {\n \"administratorLogin\": \"[parameters('SqlServerAdminName')]\",\n \"administratorLoginPassword\": \"[parameters('SqlServerAdminPassword')]\",\n \"minimalTlsVersion\": \"1.2\",\n \"version\": \"12.0\"\n },\n \"resources\": [\n \/\/ inner resources go here (also remove this comment).\n ]\n}<\/pre>\n\n\n\nDatabase<\/h2>\n\n\n\n
{\n \"type\": \"databases\",\n \"name\": \"[parameters('SqlDatabaseName')]\",\n \"location\": \"[resourceGroup().location]\",\n \"apiVersion\": \"2014-04-01\",\n \"dependsOn\": [\n \"[resourceId('Microsoft.Sql\/servers', parameters('SqlServerName'))]\"\n ],\n \"properties\": {\n \"collation\": \"[parameters('SqlDatabaseCollation')]\",\n \"edition\": \"[parameters('SqlDatabaseEdition')]\",\n \"requestedServiceObjectiveName\": \"[parameters('SqlDatabaseServiceObjective')]\"\n }\n}<\/pre>\n\n\n\nARM based AAD administrator<\/h2>\n\n\n\n
{\n \"type\": \"administrators\",\n \"name\": \"activeDirectory\",\n \"apiVersion\": \"2019-06-01-preview\",\n \"location\": \"[resourceGroup().location]\",\n \"properties\": {\n \"administratorType\": \"ActiveDirectory\",\n \"login\": \"[parameters('AADAdminLogin')]\",\n \"sid\": \"[parameters('AADAdminSid')]\",\n \"tenantId\": \"[parameters('AADAdminTenantId')]\"\n },\n \"dependsOn\": [\n \"[concat('Microsoft.Sql\/servers\/', parameters('SqlServerName'))]\"\n ]\n}<\/pre>\n\n\n\nConfigure an App Service with a managed service identity (MSI).<\/h1>\n\n\n\n
{\n \"apiVersion\": \"2015-08-01\",\n \"name\": \"[parameters('WebAppName')]\",\n \"type\": \"Microsoft.Web\/sites\",\n \"location\": \"[resourceGroup().location]\",\n \"identity\": {\n \"type\": \"SystemAssigned\"\n }\n ...\n}<\/pre>\n\n\n\n\"outputs\": {\n \"ManagedServiceIdentityPrincipalId\": {\n \"type\": \"string\",\n \"value\": \"[reference(concat(resourceId('Microsoft.Web\/sites', variables('webAppName')), '\/providers\/Microsoft.ManagedIdentity\/Identities\/default'), '2018-11-30').principalId]\"\n },\n \"ManagedServiceIdentityClientId\": {\n \"type\": \"string\",\n \"value\": \"[reference(concat(resourceId('Microsoft.Web\/sites', variables('webAppName')), '\/providers\/Microsoft.ManagedIdentity\/Identities\/default'), '2018-11-30').clientId]\"\n }\n}<\/pre>\n\n\n\n- task: AzureResourceGroupDeployment@2\n displayName: 'Deploy ARM template'\n inputs:\n azureSubscription: 'Your service connection name here'\n resourceGroupName: '$(resourceGroupName)'\n location: '$(location)'\n csmFile: 'ARMTemplate.json'\n csmParametersFile: 'ARMTemplate.parameters.json'\n\n- task: keesschollaart.arm-outputs.arm-outputs.ARM Outputs@5\n displayName: 'Retrieve ARM Outputs'\n inputs:\n ConnectedServiceNameARM: 'Your service connection name here'\n resourceGroupName: $(resourceGroupName)<\/pre>\n\n\n\n
keesschollaart.arm-outputs.arm-outputs.ARM Outputs@5<\/code> task is found in this extension<\/a>.<\/p><\/blockquote>\n\n\n\nAdd the MSI as a user to the database<\/h1>\n\n\n\n
CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;\nALTER ROLE db_datareader ADD MEMBER [<identity-name>];\nALTER ROLE db_datawriter ADD MEMBER [<identity-name>];\nALTER ROLE db_ddladmin ADD MEMBER [<identity-name>];\nGO<\/pre>\n\n\n\n
CREATE USER [<identity-name>] WITH default_schema=[dbo], SID=<SID>, TYPE=E;\nALTER ROLE db_datareader ADD MEMBER [<identity-name>];\nALTER ROLE db_datawriter ADD MEMBER [<identity-name>];\nALTER ROLE db_ddladmin ADD MEMBER [<identity-name>];\nGO<\/pre>\n\n\n\n
[guid]$guid = [System.Guid]::Parse($objectId)\nforeach ($byte in $guid.ToByteArray())\n{\n $byteGuid += [System.String]::Format(\"{0:X2}\", $byte)\n}\nreturn \"0x\" + $byteGuid<\/pre>\n\n\n\nManagedServiceIdentityPrincipalId<\/code> returned by the ARM template) your user will be created, but the MSI won’t actually have access. For service principals like the MSI, the SID needs to be created from the application ID (the ManagedServiceIdentityClientId<\/code> returned by the ARM template).<\/p>\n\n\n\nparameters:\n armServiceConnection: '' # The Azure Service Connection that has access to the specified database.\n serverName: '' # The SQL Server name\n databaseName: '' # The SQL Database name\n sqlAdminUsername: '' # A SQL user with permissions to create new users.\n sqlAdminPassword: '' # Password of the SQL user.\n identityName: '' # The name of the user to create in the database.\n identityObjectId: '' # The Object ID of the AAD user or group to add (or the application ID of a service principal).\n isGroup: false # Indicates if the Object ID references a group instead of a user or service principal.\n\nsteps:\n- task: PowerShell@2\n displayName: Convert ObjectID into SID\n inputs:\n targetType: 'inline'\n script: |\n [guid]$guid = [System.Guid]::Parse(\"${{ parameters.identityObjectId }}\")\n $byteGuid = \"0x\"\n foreach ($byte in $guid.ToByteArray())\n {\n $byteGuid += [System.String]::Format(\"{0:X2}\", $byte)\n }\n Write-Host \"##vso[task.setvariable variable=identitySid]$byteGuid\"\n\n- task: PowerShell@2\n displayName: Create identity type\n inputs:\n targetType: 'inline'\n script: |\n if(\"${{ parameters.isGroup }}\" -eq \"true\")\n {\n Write-Host \"##vso[task.setvariable variable=identityType]X\"\n } else {\n Write-Host \"##vso[task.setvariable variable=identityType]E\"\n }\n\n- task: geeklearningio.gl-vsts-tasks-azure.execute-sql-task.ExecuteSql@1\n displayName: 'Add identity to SQL database users'\n inputs:\n ConnectedServiceName: ${{ parameters.armServiceConnection }}\n ScriptType: InlineScript\n Variables: |\n identityName=${{ parameters.identityName }}\n identitySid=$(identitySid)\n identityType=$(identityType)\n InlineScript: |\n IF NOT EXISTS (\n SELECT [name]\n FROM sys.database_principals\n WHERE [name] = '$(identityName)'\n )\n BEGIN\n CREATE USER [$(identityName)] WITH default_schema=[dbo], SID=$(identitySid), TYPE=$(identityType);\n ALTER ROLE db_datareader ADD MEMBER [$(identityName)];\n ALTER ROLE db_datawriter ADD MEMBER [$(identityName)];\n ALTER ROLE db_ddladmin ADD MEMBER [$(identityName)];\n END\n GO\n ServerName: ${{ parameters.serverName }}\n DatabaseName: ${{ parameters.databaseName }}\n SqlUsername: ${{ parameters.sqlAdminUsername }}\n SqlPassword: ${{ parameters.sqlAdminPassword }}<\/pre>\n\n\n\ngeeklearningio.gl-vsts-tasks-azure.execute-sql-task.ExecuteSql@1<\/code> task is found in this extension<\/a>. It has the ability to add a firewall rule for the build agent before performing any SQL operations, and removing this firewall rule after the SQL operations are completed.<\/p><\/blockquote>\n\n\n\n- template: add-aad-user-to-sql.v1.yml@templates # this is what I called my template.\n parameters:\n armServiceConnection: 'Your service connection name here'\n serverName: your-sql-server-name.database.windows.net\n databaseName: YourDatabaseName\n sqlAdminUsername: $(SqlServerAdminUsername)\n sqlAdminPassword: $(SqlServerAdminPassword)\n identityName: your-web-app-name\n identityObjectId: $(ManagedServiceIdentityClientId)<\/pre>\n\n\n\n
Use the MSI to connect to the database<\/h1>\n\n\n\n
Microsoft.Azure.Services.AppAuthentication<\/code> package into the project containing your DBContext implementation (the last version as of this writing is 1.5.0).<\/li>using Microsoft.Data.SqlClient;\nusing Microsoft.EntityFrameworkCore;\nusing Microsoft.Azure.Services.AppAuthentication;\nusing Microsoft.EntityFrameworkCore.SqlServer.Infrastructure.Internal;\n\nnamespace Your.Namespace.Here\n{\n public class YourDBContext : DbContext\n {\n public YourDBContext(DbContextOptions<YourDBContext> options)\n : base(options)\n {\n \/\/ You might want to skip retrieving the access token in case you are running against a local DB or perhaps a sqlite DB for unit testing.\n if (Database.IsSqlServer() && !options.GetExtension<SqlServerOptionsExtension>().ConnectionString.Contains(\"(localdb)\"))\n {\n var conn = (SqlConnection)Database.GetDbConnection();\n conn.AccessToken = (new AzureServiceTokenProvider())\n .GetAccessTokenAsync(\"https:\/\/database.windows.net\/\").Result;\n }\n }\n }\n}<\/pre>\n\n\n\nServer=tcp:your-sql-server-name.database.windows.net,1433;Database=YourDatabaseName;<\/code><\/li><\/ul>\n\n\n\n