azure - 用户 '' 基于 AD token 的身份验证登录失败。在 Azure SQL 的 Entity Framework 6 中

Question

我已经为我的 SQL 数据库完成了 Azure AD 身份验证。为此，我按照以下步骤操作。

1. 我在门户中为 SQL 数据库设置了 Azure AD 管理员

2. 列表项

3. 获取身份验证 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. 是否进行了sql连接

       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. 数据库上下文类

    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. 最终连接数据库表

    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();
   

}

现在我在连接到表时遇到错误，

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

Answer 1

在您的步骤中，我没有看到您在 azure 广告中创建了应用程序注册，但您似乎正在使用 clientid 和 key 。你错过了一个步骤吗？以下是如何使用服务主体连接到 sql 数据库的完整示例: https://techcommunity.microsoft.com/t5/azure-sql-database/azure-ad-service-principal-authentication-to-sql-db-code-sample/ba-p/481467

问候，