Using Azure SQL DB Authentication with Managed Identity is giving the error
“Login failed for user ””
while executing the database command. Here is my code:
public ReturnData GetData()
{
string connectionString = "Server=database.windows.net,1433;Initial Catalog=Mydatabase;"
var credential = new DefaultAzureCredential();
var token = credential.GetToken(new Azure.Core.TokenRequestContext(new[] {"https://database.windows.net/.default" }));
SqlConnection sqlConnection = new SqlConnection(connectionString);
sqlConnection.AccessToken = token.Token;
Database database = new SqlDatabase(sqlConnection.ConnectionString);
using(DbCommand dbCommand=database.GetStoredProcCommand(DatabaseConstants.Procedure_Name))
{
dbCommand.CommandTimeout=3600;
((SqlDatabase)database).AddInParameter(dbCommand,DatabaseConstants.D_ATM, DbType.Int32, id);
DataSet dataSet = database.ExecuteDataSet(dbCommand); if(dataSet!=null&&dataSet.Tables.Count>0&&dataSet.Tables[0].Rows.Count > 0)
{
DataRow row = dataSet.Tables[0].Rows[0];
}
}
}
Added user level permissions also. Need solution for this issue.
Raghu is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
The user is not authorized to access Azure SQL server that may be the reason to get login failed for user error. You can follow the below procedure to connect Azure SQL server from Azure web API with system assigned managed identity authentication:
Use 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 active directory admin as you require to the SQL server as shown below:
Choose administrator account for azure service authentication to retrieve the token credentials as shown below:
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 will successfully connect to the app as shown below:
For more information you can refer to the similar SO thread.
7