How to configure Connection string without username and password by using managed instance Azure AD authentication with Azure SQL

Question

Hi there ,

I am having 4 .net web applications hosted in IIS in the domain joined Azure VM.

I am using Azure Sql databases for all the applications(All dbs in a elastic pool of sql server) .

Coming to connection strings for DB connectivity ,right now I am using Admin Id and password in web.config

connectionString= "Server=server1234.database.windows.net,1433;Initial Catalog={DBname};Persist Security Info=False;User ID={SqlServerAdminId};Password=*******;TrustServerCertificate=False;" />

Now the requirement is to use managed identity(Azure AD Authentication) and make my connection string without userId & password.

connectionString="Data Source=server1234.database.windows.net;Initial Catalog={{DBname}};Authentication=Active Directory Integrated;"

I have created 4 different user Ids for 4 applications in Azure AD

even I have created a group in Azure Active Directory as all these 4 Ids as part of that group and I have set the group as admin.

and in IIS I am using that specific ids for that specific site

I have gone through couple of sites and most of their example is based on Azure App service(which in my case is an application hosted on VM )

could someone help me on this, I think I'm missing something related to adding each userid & db roles(db_datareader, db__datawriter) to each individual database to make it work

Answer 1

Hi Raghava,

Welcome to Q&A Forum; this is a great place to get support, answers, and tips.

Thank you for posting your question; I'll be more than glad to help you out.

You are correct... you have to grant those users access to the database!

I think this blog post would help you to create the right connections. (add contained Azure AD user)

https://www.mssqltips.com/sqlservertip/5242/adding-users-to-azure-sql-databases/

I hope my answer is helpful to you,

Your

Bjoern Peters

If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!