Using AzureDevOps Build Agent for Secure Azure SQL Database connection from App Service using a managed identity

Question

My struggle is specifically with fully automating things with the AzureDevOps build agent in the default configuration.

I'm trying to use the App Service Managed Identity (https://learn.microsoft.com/en-us/azure/app-service/overview-managed-identity) to access a SQL database.

The entire resource group is created by the AzureDevOps build agent during a single deployment.

The simplified version of my deployments:


New-AzResourceGroup `
    -Name $Env:RESOURCEGROUP_NAME `
    -Location $Env:AZURE_REGION_COMMON `
    -Force

#Create the functionapp
New-AzResource `
    -ResourceGroupName $Env:RESOURCEGROUP_NAME `
    -location $Env:AZURE_REGION_COMMON `
    -ResourceName $Env:APPFUNCTIONS_NAME_SAFE `
    -Kind 'functionapp' `
    -ResourceType 'Microsoft.Web/Sites' `
    -Properties @{} `
    -Force

#Apply the Managed Service Identity
Set-AzWebApp `
    -ResourceGroupName $Env:RESOURCEGROUP_NAME `
    -Name $Env:APPFUNCTIONS_NAME_SAFE `
    -AssignIdentity $true

# Create Sql Server

New-AzSqlServer `
    -ResourceGroupName $Env:RESOURCEGROUP_NAME `
    -location $Env:AZURE_REGION_COMMON `
    -ServerName $Env:SQLSERVER_NAME_SAFE `
    -ServerVersion $Env:SQLSERVER_VERSION `
    -SqlAdministratorCredentials (New-Object `
        -TypeName System.Management.Automation.PSCredential `
        -ArgumentList $Env:SQLSERVER_ADMIN_USERNAME, (ConvertTo-SecureString -String $Env:SQLSERVER_ADMIN_PASSWORD -AsPlainText -Force)) `
    -AssignIdentity

#Update Sql Server Firewall Rules for Azure
New-AzSqlServerFirewallRule `
    -ResourceGroupName $Env:RESOURCEGROUP_NAME `
    -ServerName $Env:SQLSERVER_NAME_SAFE `
    -AllowAllAzureIPs

#Update Sql Server Firewall Rules for non-Azure build machine
$ip = Invoke-RestMethod http://ipinfo.io/json | Select -exp ip
New-AzSqlServerFirewallRule `
    -ResourceGroupName $Env:RESOURCEGROUP_NAME `
    -ServerName $Env:SQLSERVER_NAME_SAFE `
    -FirewallRuleName "BuildMachine" `
    -StartIpAddress $ip `
    -EndIpAddress $ip

#Create Sql Database
New-AzSqlDatabase `
    -ResourceGroupName $Env:RESOURCEGROUP_NAME `
    -ServerName $Env:SQLSERVER_NAME_SAFE `
    -DatabaseName $Env:SQLSERVER_DATABASE_NAME

At this point; I'm not finding any powershell that the build agent can run to grant database permissions to the identity.

I do find references that suggest running something like this on the database

CREATE USER [$Env:APPFUNCTIONS_NAME_SAFE] FROM EXTERNAL PROVIDER
ALTER ROLE db_datareader ADD MEMBER [$Env:APPFUNCTIONS_NAME_SAFE]
ALTER ROLE db_datawriter ADD MEMBER [$Env:APPFUNCTIONS_NAME_SAFE]

This has been where I've focused my efforts, unsuccessfully.

When I log into the database as the DB admin (see SqlAdministratorCredentials above) then I get the following exception Exception calling "ExecuteNonQuery" with "0" argument(s): "Principal '<value of Env:$APPFUNCTIONS_NAME_SAFE>' could not be created. Only connections established with Active Directory accounts can create other Active Directory users.

To get past this I've manually added an AAD group, the azure devops service connection to that group. This enables the build agent to authenticate via AAD to the Sql Server.

Deploying again I get System.Management.Automation.MethodInvocationException: Exception calling "Open" with "0" argument(s): "One or more errors occurred." ---> System.AggregateException: One or more errors occurred. ---> System.AggregateException: One or more errors occurred. ---> AdalException: No mapping between account names and security IDs was done.

Which brings me to - How do I automate authentication of an AppService Identity to a Sql Database?

References

Here's how I'm executing SQL as the build agent

function Get-SqlServer{
    Get-AzSqlServer `
        -ResourceGroupName $Env:RESOURCEGROUP_NAME `
        -ServerName $Env:SQLSERVER_NAME_SAFE `
        -ErrorAction Ignore
}

function Add-User-Sql($database, $sql){

    $server = "tcp:$((Get-SqlServer).FullyQualifiedDomainName),1433"
    $adminName = $Env:SQLSERVER_ADMIN_USERNAME
    $adminPassword = $Env:SQLSERVER_ADMIN_PASSWORD

    $connectionString = "Server=$server;Database=$database;User ID=$adminName;Password=$adminPassword;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
    #$connectionString = "Data Source=$server; Authentication=Active Directory Integrated; Initial Catalog=$database;";
    $connection = New-Object -TypeName System.Data.SqlClient.SqlConnection($connectionString)

    $command = New-Object -TypeName System.Data.SqlClient.SqlCommand($sql, $connection)
    Write-Host "Add User [database=$database] on SqlServer [$Env:SQLSERVER_NAME_SAFE]"
    Write-Host $sql
    $connection.Open()
    $command.ExecuteNonQuery()
    $connection.Close()
}

Clean up Script

Remove-AzResourceGroup `
    -Name $Env:RESOURCEGROUP_NAME `
    -Force

May I ask if you were able to get any further with this? I am facing a similar situation as you were. — Asher, Aug 31 '21 at 11:09

score -1 · Answer 1 · answered Jun 11 '19 at 11:23

Perhaps I am missing something but given your explaination and AFAIK the latest error ("No mapping between account names and security IDs was done") in the flow followed by you would be because role member(s) is invalid or stale in some way.

And regarding your question of a way to automate authentication of an AppService Identity to a Sql Database, try command provided in this document if you already haven't.

Using AzureDevOps Build Agent for Secure Azure SQL Database connection from App Service using a managed identity

References

1 Answers1