question

StanS-6753 avatar image
0 Votes"
StanS-6753 asked KalyanChanumolu-MSFT commented

Azure SQL Managed Instance - Managed Identity

Hi,
I recently spun up an Azure SQL Managed Instance and restored a DB. In my connection string for my on-prem app, I can configure the SA UN/PW that I specified during the MI creation, and successfully connect the app to the DB. This was just to test the fact I can get from my app, to the MI DB.

Of course, I would prefer not to have a UN or PW contained within the Connection String. Is it possible to connect to a SQL MI WITHOUT using a UN and PW in the connection string?

Cheers

azure-sql-database
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

KalyanChanumolu-MSFT avatar image
1 Vote"
KalyanChanumolu-MSFT answered KalyanChanumolu-MSFT commented

@StanS-6753 Thank you for reaching out.

Yes, you can use Azure AD Service Principal with Managed Instance.
This way, the web app can authenticate via Managed Identity and does not require any credentials to be specified in the connection string
Please check this article for more details.

If you are using EntityFramework, you can acquire the token in the constructor of your DbContext

 public MSXDataContext(DbContextOptions<MSXDataContext> options) : base(options)
 {
     var connection = (SqlConnection)Database.GetDbConnection();
     connection.AccessToken = new Microsoft.Azure.Services.AppAuthentication.AzureServiceTokenProvider().GetAccessTokenAsync("https://database.windows.net/").Result;
 }

If you need to use a connection string, use the below format. Replace <server-name> and <db-name> with your server name and database name.

 "server=tcp:<server-name>.database.windows.net;database=<db-name>;UID=AnyString;Authentication=Active Directory Interactive". 


If an answer is helpful, please "Accept answer" or "Up-Vote" for the same which might be beneficial to other community members reading this thread.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OK, so what would I need to specify in the connection string to connect to the instance and the DB?

0 Votes 0 ·

@StanS-6753, I am not able to format the code snippet in the comment, so I have updated the original answer to include the details.
Please check and let me know if you have further questions.


If an answer is helpful, please "Accept answer" or "Up-Vote" for the same which might be beneficial to other community members reading this thread.

0 Votes 0 ·