Migrate a .NET application to use passwordless connections with Azure SQL Database

Article
09/29/2023

Application requests to Azure SQL Database must be authenticated. Although there are multiple options for authenticating to Azure SQL Database, you should prioritize passwordless connections in your applications when possible. Traditional authentication methods that use passwords or secret keys create security risks and complications. Visit the passwordless connections for Azure services hub to learn more about the advantages of moving to passwordless connections. The following tutorial explains how to migrate an existing application to connect to Azure SQL Database to use passwordless connections instead of a username and password solution.

Configure the Azure SQL Database

Passwordless connections use Microsoft Entra authentication to connect to Azure services, including Azure SQL Database. Microsoft Entra authentication, you can manage identities in a central location to simplify permission management. Learn more about configuring Microsoft Entra authentication for your Azure SQL Database:

For this migration guide, ensure you have a Microsoft Entra admin assigned to your Azure SQL Database.

Navigate to the Microsoft Entra page of your logical server.
Select Set admin to open the Microsoft Entra ID flyout menu.
In the Microsoft Entra ID flyout menu, search for the user you want to assign as admin.
Select the user and choose Select.

Configure your local development environment

Passwordless connections can be configured to work for both local and Azure hosted environments. In this section, you'll apply configurations to allow individual users to authenticate to Azure SQL Database for local development.

For local development, make sure you're signed-in with the same Azure AD account you want to use to access Azure SQL Database. You can authenticate via popular development tools, such as the Azure CLI or Azure PowerShell. The development tools with which you can authenticate vary across languages.

Sign-in to Azure through the Azure CLI using the following command:

az login

You will need to install the Azure CLI to work with DefaultAzureCredential through Visual Studio Code.

On the main menu of Visual Studio Code, navigate to Terminal > New Terminal.

Sign-in to Azure through the Azure CLI using the following command:

az login

Sign-in to Azure using PowerShell via the following command:

Connect-AzAccount

Create a database user and assign roles

Create a user in Azure SQL Database. The user should correspond to the Azure account you used to sign-in locally via development tools like Visual Studio or IntelliJ.

In the Azure portal, browse to your SQL database and select Query editor (preview).
Select Continue as <your-username> on the right side of the screen to sign into the database using your account.
On the query editor view, run the following T-SQL commands:
```
CREATE USER [user@domain] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [user@domain];
ALTER ROLE db_datawriter ADD MEMBER [user@domain];
ALTER ROLE db_ddladmin ADD MEMBER [user@domain];
GO
```
Running these commands assigns the SQL DB Contributor role to the account specified. This role allows the identity to read, write, and modify the data and schema of your database. For more information about the roles assigned, see Fixed-database roles.

Update the local connection configuration

Existing application code that connects to Azure SQL Database using the Microsoft.Data.SqlClient library or Entity Framework Core will continue to work with passwordless connections. However, you must update your database connection string to use the passwordless format. For example, the following code works with both SQL authentication and passwordless connections:

string connectionString = app.Configuration.GetConnectionString("AZURE_SQL_CONNECTIONSTRING")!;

using var conn = new SqlConnection(connectionString);
conn.Open();

var command = new SqlCommand("SELECT * FROM Persons", conn);
using SqlDataReader reader = command.ExecuteReader();

To update the referenced connection string (AZURE_SQL_CONNECTIONSTRING) to use the passwordless connection string format:

Locate your connection string. For local development with .NET applications, this is usually stored in one of the following locations:
- The appsettings.json configuration file for your project.
- The launchsettings.json configuration file for Visual Studio projects.
- Local system or container environment variables.

Replace the connection string value with the following passwordless format. Update the <database-server-name> and <database-name> placeholders with your own values:

Server=tcp:<database-server-name>.database.windows.net,1433;Initial Catalog=<database-name>;
Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication="Active Directory Default";

Test the app

Run your app locally and verify that the connections to Azure SQL Database are working as expected. Keep in mind that it may take several minutes for changes to Azure users and roles to propagate through your Azure environment. Your application is now configured to run locally without developers having to manage secrets in the application itself.

Configure the Azure hosting environment

Once your app is configured to use passwordless connections locally, the same code can authenticate to Azure SQL Database after it's deployed to Azure. The sections that follow explain how to configure a deployed application to connect to Azure SQL Database using a managed identity. Managed identities provide an automatically managed identity in Microsoft Entra ID (formerly Azure Active Directory) for applications to use when connecting to resources that support Microsoft Entra authentication. Learn more about managed identities:

Create the managed identity

Create a user-assigned managed identity using the Azure portal or the Azure CLI. Your application uses the identity to authenticate to other services.

Azure portal
Azure CLI

At the top of the Azure portal, search for Managed identities. Select the Managed Identities result.
Select + Create at the top of the Managed Identities overview page.
On the Basics tab, enter the following values:
- Subscription: Select your desired subscription.
- Resource group: Select your desired resource group.
- Region: Select a region near your location.
- Name: Enter a recognizable name for your identity, such as MigrationIdentity.
Select Review + create at the bottom of the page.
When the validation checks finish, select Create. Azure creates a new user-assigned identity.

After the resource is created, select Go to resource to view the details of the managed identity.

Use the az identity create command to create a user-assigned managed identity:

az identity create --name MigrationIdentity --resource-group <resource-group>

Associate the managed identity with your web app

Configure your web app to use the user-assigned managed identity you created.

Complete the following steps in the Azure portal to associate the user-assigned managed identity with your app. These same steps apply to the following Azure services:

Azure Spring Apps
Azure Container Apps
Azure virtual machines
Azure Kubernetes Service
Navigate to the overview page of your web app.

Select Identity from the left navigation.
On the Identity page, switch to the User assigned tab.
Select + Add to open the Add user assigned managed identity flyout.
Select the subscription you used previously to create the identity.
Search for the MigrationIdentity by name and select it from the search results.
Select Add to associate the identity with your app.

Use the following Azure CLI commands to associate an identity with your app:

Retrieve the fully qualified resource ID of the managed identity you created using the az identity show command. Copy the output value to use in the next step.

az identity show --name MigrationIdentity -g <your-identity-resource-group-name> --query id

You can assign a managed identity to an Azure App Service instance with the az webapp identity assign command. The --identities parameter requires the fully qualified resource ID of the managed identity you retrieved in the previous step. A fully qualified resource ID starts with '/subscriptions/{subscriptionId}' or '/providers/{resourceProviderNamespace}/'.

az webapp identity assign \
    --resource-group <resource-group-name> \
    --name <webapp-name> \
    --identities <managed-identity-id>

If you are working with Git Bash, be careful of path conversions when using fully qualified resource IDs. To disable path conversion, add MSYS_NO_PATHCONV=1 to the beginning of your command. For more information, see Auto translation of resource IDs.

You can assign a managed identity to an Azure Spring Apps instance with the az spring app identity assign command.

az spring app identity assign \
    --resource-group <resource-group-name> \
    --name <app-name> \
    --service <service-name> \
    --user-assigned <managed-identity-id>

You can assign a managed identity to a virtual machine with the az containerapp identity assign command.

az containerapp identity assign \
    --resource-group <resource-group-name> \
    --name <app-name> \
    --user-assigned <managed-identity-id>

You can assign a managed identity to a virtual machine with the az vm identity assign command.

az vm identity assign \
    --resource-group <resource-group-name> \
    --name <virtual-machine-name> \
    --identities <managed-identity-id>

You can assign a managed identity to an Azure Kubernetes Service (AKS) instance with the az aks update command.

az aks update \
    --resource-group <resource-group-name> \
    --name <cluster-name> \
    --enable-managed-identity \
    --assign-identity <managed-identity-id> \
    --assign-kubelet-identity <managed-identity-id>

You can use Service Connector to create a connection between an Azure compute hosting environment and a target service using the Azure CLI. The Service Connector CLI commands automatically assign the proper role to your identity. You can learn more about Service Connector and which scenarios are supported on the overview page.

Retrieve the client ID of the managed identity you created using the az identity show command. Copy the value for later use.
```
az identity show --name MigrationIdentity --resource-group <resource-group> --query clientId
```

Use the appropriate CLI command to establish the service connection:

If you're using an Azure App Service, use the az webapp connection command:

az webapp connection create storage-blob \
    --resource-group <resource-group-name> \
    --name <webapp-name> \
    --target-resource-group <target-resource-group-name> \
    --account <target-storage-account-name> \
    --user-identity "client-id=<your-identity-client-id>" "subs-id=<your-subscription-id>"

If you're using Azure Spring Apps, use the az spring connection command:

az spring connection create storage-blob \
    --resource-group <resource-group-name> \
    --service <service-instance-name> \
    --app <app-name> \
    --deployment <deployment-name> \
    --target-resource-group <target-resource-group> \
    --account <target-storage-account-name> \
    --user-identity "client-id=<your-identity-client-id>" "subs-id=<your-subscription-id>"

If you're using Azure Container Apps, use the az containerapp connection command:

az containerapp connection create storage-blob \
    --resource-group <resource-group-name> \
    --name <containerapp-name> \
    --target-resource-group <target-resource-group-name> \
    --account <target-storage-account-name> \
    --user-identity "client-id=<your-identity-client-id>" "subs-id=<your-subscription-id>"

Create a database user for the identity and assign roles

Create a SQL database user that maps back to the user-assigned managed identity. Assign the necessary SQL roles to the user to allow your app to read, write, and modify the data and schema of your database.

In the Azure portal, browse to your SQL database and select Query editor (preview).
Select Continue as <username> on the right side of the screen to sign into the database using your account.

On the query editor view, run the following T-SQL commands:

CREATE USER [user-assigned-identity-name] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [user-assigned-identity-name];
ALTER ROLE db_datawriter ADD MEMBER [user-assigned-identity-name];
ALTER ROLE db_ddladmin ADD MEMBER [user-assigned-identity-name];
GO

Running these commands assigns the SQL DB Contributor role to the user-assigned managed identity. This role allows the identity to read, write, and modify the data and schema of your database.

Important

Use caution when assigning database user roles in enterprise production environments. In those scenarios, the app shouldn't perform all operations using a single, elevated identity. Try to implement the principle of least privilege by configuring multiple identities with specific permissions for specific tasks.

You can read more about configuring database roles and security on the following resources:

Update the connection string

Update your Azure app configuration to use the passwordless connection string format. Connection strings are generally stored as environment variables in your app hosting environment. The following instructions focus on App Service, but other Azure hosting services provide similar configurations.

Navigate to the configuration page of your App Service instance and locate the Azure SQL Database connection string.

Select the edit icon and update the connection string value to match following format. Change the <database-server-name> and <database-name> placeholders with the values of your own service.

Server=tcp:<database-server-name>.database.windows.net,1433;Initial Catalog=<database-name>;
Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication="Active Directory Default";

Save your changes and restart the application if it does not do so automatically.

Test the application

Test your app to make sure everything is still working. It may take a few minutes for all of the changes to propagate through your Azure environment.

Next steps

In this tutorial, you learned how to migrate an application to passwordless connections.

You can read the following resources to explore the concepts discussed in this article in more depth:

Share via