How do I access my Azure SQL Database using my managed identity?

Question

Hi all;

I have a managed identity created. How do I (step by step please as I remember this as being complicated):

1. Set the database to allow access via managed identity?
2. What is the connection string I then provide to the production server?
3. How do I keep the dev server using the explicit setting in Azure App Server | Environment Variables | Connection strings?

thanks - dave

Answer 1

David Thielen Thank you for getting back.

1. Enable system-assigned managed identity on azure App service.

2. Grant SQL database access to the managed identity.

You'll connect to the SQL database with a Microsoft Entra user account and grant the managed identity access to the database.

Open your preferred SQL tool and login with a Microsoft Entra user account (such as the Microsoft Entra user we assigned as administrator). This can be accomplished in Cloud Shell with the SQLCMD command.

Bash

sqlcmd -S <server-name>.database.windows.net -d <db-name> -U <aad-user-name> -P "<aad-password>" -G -l 30

b. In the SQL prompt for the database you want, run the following commands to grant permissions to your function. For example,

SQL

CREATE

<identity-name> is the name of the managed identity in Microsoft Entra ID. If the identity is system-assigned, the name is always the same as the name of your Function app.

For step by step please check this link Tutorial: Connect to SQL Database from .NET App Service without secrets using a managed identity.

How to modify connection string

How do I change the SQL Database connection to use a managed identity instead?

https://learn.microsoft.com/en-us/azure/app-service/tutorial-dotnetcore-sqldb-app?tabs=copilot&pivots=azure-portal#how-do-i-change-the-sql-database-connection-to-use-a-managed-identity-instead

Additional helpful links:

https://learn.microsoft.com/en-us/azure/app-service/tutorial-connect-msi-sql-database?tabs=windowsclient%2Cefcore%2Cdotnet#modify-connection-string

https://www.youtube.com/watch?v=Uv-qNbUStSM

Please let me know after going through all the above if you are still block. I will be happy to assit you.

Regards,

Oury