Storing passwords anywhere is never recommended practice, and that is why so called "Integrated Authentication" is always recommended when connection from a Web App running IIS against SQL Server.

With the built-in support for Managed Identity in Azure App Service/Functions, and using the latest drivers, it is now possible to achieve the same in Azure Cloud.

An overview of Managed Identity and it's benefits is available here.

In this blog post, I will show how you can configure your Azure Deployment (using for example Azure DevOps) to use Managed Identity.

Using Managed Identity wit EF Core from an App Service requires five distinct steps, in summary:

  • Enable Azure AD Integration for your Azure SQL DB logical server
  • Create a User Assigned Managed Identity resource
  • Assign the User Assigned Managed Identity to the App Service identities
  • Give the User Assigned Managed Identity access to your SQL DB
  • Update your project and change your connection string
  • Enjoy!

Configure AAD Integration for Azure SQL DB

First configure AAD integration for your Azure SQL DB logical server, if you have not done so already, for example as described in my blog post here.

Create a User Assigned Managed Identity resource

Now create an User Assigned Managed Identity in the same resource group as your Azure SQL DB (you can also use System Assigned, but automation is much easier with a system assigned identity).

You can use an ARM template (or any other supported way to call the ARM API)

Assign the User Assigned Managed Identity to your App Service

You can also do that via your ARM Template

Give the User Assigned Managed Identity access to the SQL DB

You can use a script similar to this, running for example in an Azure DevOps Azure PowerShell task, to first get the properties of the managed identity:

$ResourceName = "my-resource-group"
$MIName = "user-assigned-managed_identity"
Write-Output "Getting Managed Identity properties"
Install-Module -Name Az.ManagedServiceIdentity -Scope CurrentUser -Force
$mi = Get-AzUserAssignedIdentity -ResourceGroupName $ResourceName -Name $MIName
$miObjectId = $mi.PrincipalId
$appId = $mi.ClientId

Then give the managed identity access to the SQL database (getting the $sqlToken and the SID is covered in my previous blog posts here and here)

Write-Output "Giving User Assigned Managed Identity database access"
$sid = ConvertTo-Sid -appId $appId
$Query = "IF NOT EXISTS(SELECT 1 FROM sys.database_principals WHERE name ='$MIName')
            BEGIN
                CREATE USER [$MIName] WITH DEFAULT_SCHEMA=[dbo], SID = $sid, TYPE = E;
            END
            IF IS_ROLEMEMBER('db_datareader','$MIName') = 0
            BEGIN
                ALTER ROLE db_datareader ADD MEMBER [$MIName]
            END
            IF IS_ROLEMEMBER('db_datawriter','$MIName') = 0
            BEGIN
                ALTER ROLE db_datawriter ADD MEMBER [$MIName]
            END;"
$sqlInstance = $sqlServer + ".database.windows.net"
 
Invoke-Sqlcmd -ServerInstance $sqlInstance -Database $sqlDatabase -AccessToken $sqlToken -Query $Query

Update project and configure your connection string

Finally, update your .NET EF Core project to use the latest version of Microsoft.Data.SqlClient, which contains enhanced support for Azure AD and update your connection string!

To update your project, add a reference to the latest version:

<PackageReference Include="Microsoft.Data.SqlClient" Version="2.1.2" />
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="5.0.5" />

And finally update your connection string - use the object id / principal id of the user assigned managed identity ($miObjectId above) as "User Id" in your connection string:

Server=myserver.database.windows.net;Database=mydatabase;User Id=xxxx;Authentication=Active Directory Managed Identity;Connect Timeout=60

With SqlClient version 3 or later, application id must be used instead ($appId above)

Notice the authentication setting: Authentication=Active Directory Managed Identity

Now you can connect to your database from EF Core without storage of any passwords, using a centrally managed identity.

Comments or questions for this blog post?