DEV Community

LingamSuyambu
LingamSuyambu

Posted on Originally published at sqlcloudlab.blogspot.com

Connect .NET to Azure SQL with Managed Identity (No Passwords)

#azure #dotnet #sql #security

Originally published at SQL & Cloud Lab.

Connection strings with a SQL username and password end up in config files, pipelines and sometimes source control. With a managed identity, your Azure-hosted .NET app signs in to Azure SQL using Microsoft Entra ID (formerly Azure AD) and gets tokens automatically. There is no secret to store, rotate or leak.

How it works

Azure gives your App Service, Function App, container or VM an identity in Microsoft Entra ID. Your code asks for a token for that identity, and SQL Server accepts the token instead of a password. The client library handles token caching and renewal for you.

Step 1: enable the identity on your app

In the Azure portal, open your App Service (or Function App), go to Identity, and switch System assigned to On. Azure creates an identity with the same name as the app. Or with the Azure CLI:

az webapp identity assign --name payroll-api --resource-group rg-payroll
Enter fullscreen mode Exit fullscreen mode

Step 2: set a Microsoft Entra admin on the SQL server

Only a Microsoft Entra account can create Entra-based database users. On the logical SQL server, open Microsoft Entra ID (sometimes shown as Active Directory admin) and set an admin, ideally a group rather than a single person.

Step 3: create a database user for the app

Connect to the application database (not master) as the Entra admin, and run:

CREATE USER [payroll-api] FROM EXTERNAL PROVIDER;

ALTER ROLE db_datareader ADD MEMBER [payroll-api];
ALTER ROLE db_datawriter ADD MEMBER [payroll-api];
GRANT EXECUTE TO [payroll-api];   -- if the app calls stored procedures
Enter fullscreen mode Exit fullscreen mode

The name in brackets is the name of the managed identity, which for a system-assigned identity is the app's name. Grant only the permissions the app needs; avoid db_owner.

Step 4: change the connection string

With Microsoft.Data.SqlClient, no code changes are needed beyond the connection string:

Server=tcp:payroll-sql.database.windows.net,1433;Database=Payroll;
Authentication=Active Directory Default;Encrypt=True;
Enter fullscreen mode Exit fullscreen mode

Active Directory Default tries a chain of credential sources: managed identity when running in Azure, and your developer sign-in (Visual Studio, Azure CLI or similar) when running locally. The same configuration works in both places, and EF Core and Dapper both just use this connection string.

Running locally

To run the app on your machine, sign in with an account that also has a database user (for example, add your Entra group as a user with the same roles), then sign in with az login or in Visual Studio. No password is stored in appsettings.json.

User-assigned identities

If several apps should share one identity, or the identity must outlive a specific resource, create a user-assigned managed identity, attach it to the apps, create the database user with its name, and specify its client ID:

Server=tcp:payroll-sql.database.windows.net,1433;Database=Payroll;
Authentication=Active Directory Managed Identity;User Id=<client-id-of-identity>;Encrypt=True;
Enter fullscreen mode Exit fullscreen mode

Common errors

  • Login failed for user '': the database user does not exist in that database, or was created in master instead.
  • Principal could not be found: the name in CREATE USER does not match the identity's name exactly, or you are not connected as an Entra admin.
  • Works in Azure, fails locally: your own account has no database user, or you are signed in to a different tenant.

Finish the job: retire SQL logins

Once every app uses identities, consider enabling Microsoft Entra-only authentication on the server so SQL usernames and passwords cannot be used at all. That removes a whole class of credential leaks.

Related reading:

More SQL Server guides on my blog, SQL & Cloud Lab.

Top comments (0)