AD Token based authentication Login failed for user '<token-identified principal>'. in entity framework 6 for Azure SQL

Viewed 26194

I have done Azure AD authentication for my SQL database. For that I followed bellow steps.

  1. I set the Azure AD admin for the SQL database in the portal

  2. List item

  3. get authentication Token

    private static string GetAccessTokenAsync(string clientId, string clientSecret, string authority, 
    string resource, string scope)
    {
        var authContext = new AuthenticationContext(authority, TokenCache.DefaultShared);
        var clientCred = new ClientCredential(clientId, clientSecret);
        var token = authContext.AcquireTokenAsync(resource, clientCred).Result.AccessToken;
    
    
        return token;
    }
    
  4. did the sql connection

        string clientId = ConfigurationManager.AppSettings["ida:AADClientId"];
        string clientSecret = ConfigurationManager.AppSettings["ida:AADAppKey"];
        var authority = string.Format("https://login.microsoftonline.com/{0}", tenantId);
        var resource = "https://database.windows.net/";
        var scope = "";
        try
        {
            var token = GetAccessTokenAsync(clientId, clientSecret, authority, resource, scope);
    
            var builder = new SqlConnectionStringBuilder();
            builder["Data Source"] = $"{dbServer}.database.windows.net";
            builder["Initial Catalog"] = dbName;
            builder["Connect Timeout"] = 1500;
            builder["Persist Security Info"] = false;
            builder["TrustServerCertificate"] = false;
            builder["Encrypt"] = true;
            builder["MultipleActiveResultSets"] = false;
    
            SqlConnection con = new SqlConnection(builder.ToString());
            con.AccessToken = token;
            return con;
        }
    
  5. DB Context Class

     public partial class DBEntities : DbContext
        {
    //string dbConnectionString = 
          string.Concat(ConfigurationManager.AppSettings["subdbconnectionstring"], '"', 
    string.Format(ConfigurationManager.AppSettings["dbconnectionstring"], 
    ConfigurationManager.AppSettings["DBPassword"]),'"');
    
    //string test = ConfigurationManager.AppSettings["subdbconnectionstring"] + "\"" + ConfigurationManager.AppSettings["dbconnectionstring"];
    public DBEntities(SqlConnection con)
         : base(con, true)
    {
        {
            Database.SetInitializer<DBEntities>(null);
            ((IObjectContextAdapter)this).ObjectContext.CommandTimeout = 1800;
        }
     }
    
  6. Finally connecting Database table

     var con = AuthenticationHelper.GetSqlConnectionAsync(Constants.CDSDBServer, Constants.CDSDBDatabaseName);
            using (var dbContext = new DBEntities(con))
            {
    
                var teamRolesList = await dbContext.TEAM_ROLE.
                                     Where(t=> t.IsDeleted.Equals(false))
                                    .Select(t => new TeamRole { RoleId = t.RoleId, RoleName = t.RoleName, IsDeleted = t.IsDeleted, UserInput=t.UserInput,AllowMultiples=t.AllowMultiples }).
                                    ToListAsync();
    

}

Now I am getting error while connecting to Table,

The underlying provider failed on Open.Login failed for user '<token-identified principal> 

at System.Data.Entity.Core.EntityClient.EntityConnection.<OpenAsync>d__8.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Data.Entity.Core.Objects.ObjectContext.<EnsureConnectionAsync>d__9.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Data.Entity.Core.Objects.ObjectContext.<ExecuteInTransactionAsync>d__3d`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task 
  task)
at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.<ExecuteAsyncImplementation>d__9`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Data.Entity.Utilities.TaskExtensions.CultureAwaiter`1.GetResult()
at System.Data.Entity.Core.Objects.ObjectQuery`1.<GetResultsAsync>d__e.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Data.Entity.Utilities.TaskExtensions.CultureAwaiter`1.GetResult()
at System.Data.Entity.Internal.LazyAsyncEnumerator`1.<FirstMoveNextAsync>d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Data.Entity.Infrastructure.IDbAsyncEnumerableExtensions.<ForEachAsync>d__5`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
at CompanyDataService.Controllers.TeamController.<GetAllTeamRoles>d__0.MoveNext() in D:\sol\vs_project\DataService\DataService\Controllers\TeamController.cs:line 32
1 Answers
Related