To connect an Azure SQL Server from an Azure asp.net Web API with managed identity authentication, provide the connection string in the following format in Appsettings.json:
```csharp
"ConnectionStrings": {
"QuotesDatabase": "Server=tcp:<servername>.database.windows.net,1433; Database=<databasename>;" }
```
Use the code below for the connection:
```csharp
var connectionString = Configuration.GetConnectionString("<connectionstringname>");
services.AddTransient(a =>{
var sqlConnection = new SqlConnection(connectionString);
var credential = new DefaultAzureCredential();
var token = credential
.GetToken(new Azure.Core.TokenRequestContext(
new[] { "https://database.windows.net/.default" }));
sqlConnection.AccessToken = token.Token;
return sqlConnection;
```
Set admin as desired on the SQL Server:
Choose an administrator account for Azure service authentication to retrieve the token credentials.
Image for reference:
Enable the system-assigned managed identity in the "on" state of the Azure app service.
Log in to the SQL Server with an administrator account, add a user to the database, and assign a role to the user:
```csharp
create user [<appName>] from external provider;
alter role db_datareader add member [<appName>];
alter role db_datawriter add member [<appName>];
```
The database successfully connects to the app.
Image for reference:
To connect Azure sql database from web API through system assigned managed identity authentication mention the connection string in below format in Appsetting.json:
"ConnectionStrings": {
"QuotesDatabase": "Server=tcp:<servename>.database.windows.net,1433; Database=<databasename>;" }
Use below code for connection.
var connectionString = Configuration.GetConnectionString("<connectionstringname>");
services.AddTransient(a =>{
var sqlConnection = new SqlConnection(connectionString);
var credential = new DefaultAzureCredential();
var token = credential
.GetToken(new Azure.Core.TokenRequestContext(
new[] { "https://database.windows.net/.default" }));
sqlConnection.AccessToken = token.Token;
return sqlConnection;
set admin for sql server as you want.
choose administrator account for azure service authentication to retrieve the token credentials.
Image for reference:
Enable system assigned manage identity in on state of Azure app service.
Login to sql server with administrator add user to the database and assign role to the user
create user [<appName>] from external provider;
alter role db_datareader add member [<appName>];
alter role db_datawriter add member [<appName>];
The database successfully connected to the app.
Image for reference:
