What are the steps to successfully connect the application to Azure SQL Database after setting up the connection string and adding the App Authentication NuGet package.
How to connect an already existing .Net Core application to Azure SQL Database with managed identity
6
-
What have you tried? What practical problem are you facing? Where is your code? P.s. I'm assuming you've done some basic research and found a tutorial like this one: learn.microsoft.com/en-us/azure/azure-sql/database/… or something similar? If not, why not?ADyson– ADyson2020-06-16 17:58:09 +00:00Commented Jun 16, 2020 at 17:58
-
I did find learn.microsoft.com/en-us/azure/app-service/… but i am looking for something in .Netcore without entity framework.user215– user2152020-06-16 18:15:26 +00:00Commented Jun 16, 2020 at 18:15
-
I am stuck and I am unable to find AccessToken method under SqlConnection. How to open the connection to Azure SQL Database?user215– user2152020-06-16 18:19:35 +00:00Commented Jun 16, 2020 at 18:19
-
Follow the tutorial I linked you to. It uses .NET Core and ADO.NETADyson– ADyson2020-06-16 19:32:59 +00:00Commented Jun 16, 2020 at 19:32
-
Thanks but is there a way to use managed identity (using access token)?user215– user2152020-06-16 19:38:28 +00:00Commented Jun 16, 2020 at 19:38
|
Show 1 more comment
1 Answer
If you want to use Azure Managed Identity to connect Azure SQL database in .Net Core MVC project, We can use the package Microsoft.Data.SqlClient with SqlConnection.AccessToken.
The detailed steps are as below.
Create MSI
Configure SQL Database
a. Use your Azure Sql AD admin to connect Azure SQL vai SSMS
b. Add the MSI to the database you need use
USE [<db name>] GO create user [<your msi name>] from external provider ALTER ROLE db_owner ADD MEMBER [<function app name>]Code
/*
Install SDK Microsoft.Azure.Services.AppAuthentication and Microsoft.Data.SqlClient
*/
public async Task<IActionResult> Index()
{
List<StarWar> starWars = new List<StarWar>();
var connectionString = "Server=tcp:<server-name>.database.windows.net,1433;Database=<database-name>;";
using (var conn = new SqlConnection(connectionString))
{
conn.AccessToken = await (new Microsoft.Azure.Services.AppAuthentication.AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/");
await conn.OpenAsync();
var sql = "SELECT * FROM [dbo].[StarWars]";
using (SqlCommand command = new SqlCommand(sql, conn))
{
using (SqlDataReader reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
StarWar starWar = new StarWar();
starWar.episode = Convert.ToInt32(reader["episode"]);
starWar.score = Convert.ToInt32(reader["score"]);
starWar.name = Convert.ToString(reader["name"]);
starWars.Add(starWar);
}
}
}
}
return View(starWars);
}
