Connect to Azure SQL Database using Managed Identity

Question

I am trying to configure connecting to azure sql using managed identity. However before, I need to connect to sql azure from visual studio using AD identity.

I have followed steps here.

https://learn.microsoft.com/en-us/azure/app-service/tutorial-connect-msi-sql-database?tabs=windowsclient%2Cef%2Cdotnet#3-modify-your-project

I have this code in console app.

        SqlConnectionStringBuilder builder = new 
        Microsoft.Data.SqlClient.SqlConnectionStringBuilder();
        builder.DataSource = "sqlserveraddress";
        builder.InitialCatalog = "dbaddress";

        string connstring = builder.ConnectionString;

        await using var conn = new Microsoft.Data.SqlClient.SqlConnection(connstring)
        {
            AccessToken = await GetAzureSqlAccessToken()
        } ;

        await conn.OpenAsync();

As required, I have added my visual studio App service authentication identity user in sql azure db.

CREATE USER "[email protected]" FROM EXTERNAL 
PROVIDER;
 ALTER ROLE db_datareader ADD MEMBER 
 "[email protected]";
 ALTER ROLE db_datawriter ADD MEMBER 
 "[email protected]"; 
 ALTER ROLE db_ddladmin ADD MEMBER 
 "[email protected]";

GO

Now if i try running my console application, I get following error.

If i try to decode the access token it shows the user I have added to sql azure. [email protected].

What Am I Missing here??

Also with Microsoft.Data.SqlClient you dont need acquire manually the token, see this article: learn.microsoft.com/en-us/sql/connect/ado-net/sql/… — Thomas
– Thomas, Commented Jun 12, 2022 at 5:14
Did you configure visual studio to use azure authentication: learn.microsoft.com/en-us/dotnet/api/overview/azure/… ? — Thomas
– Thomas, Commented Jun 12, 2022 at 5:31

Utkarsh Pal · Accepted Answer · 2022-07-01 11:48:12Z

0

You need to set the created Managed Identity as admin in SQL Server.

Follow this: Azure SQL Server -> Settings -> Azure Active Directory

Now click on Set Admin option and search for the Managed Identity to which you want to give access.

Click on Save.

answered Jul 1, 2022 at 11:48

Utkarsh Pal

4,5781 gold badge8 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Connect to Azure SQL Database using Managed Identity

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related