用户'<令牌标识的主体>'登录失败.使用Azure Function应用程序和使用托管身份的Azure SQL服务时令牌已过期 [英] Login failed for user '<token-identified principal>'. Token is expired when using Azure Function app and Azure SQL Service using Managed Identity

查看：80
本文介绍了用户'<令牌标识的主体>'登录失败.使用Azure Function应用程序和使用托管身份的Azure SQL服务时令牌已过期的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在使用由服务总线触发的功能应用程序.它使用 Managed Identity 连接到 Azure SQL Server .

使用以下代码创建连接.

 新的SqlConnection(this.ConnectionString){//AzureServiceTokenProvider处理缓存令牌并在令牌过期之前刷新它AccessToken =新的AzureServiceTokenProvider().GetAccessTokenAsync("https://database.windows.net/").结果};

我每天都会收到几千个事件，而所有这些都无法连接到数据库.

我们日志中的堆栈跟踪为

  [{"parsedStack":[{"assembly":" System.Data.SqlClient，版本= 4.5.0.1，Culture =中性，PublicKeyToken = b03f5f7f11d50a3a"，方法":"System.Data.SqlClient.SqlInternalConnectionTds..ctor"，级别":0，行":0}，{程序集":"System.Data.SqlClient，版本= 4.5.0.1，区域性=中性，PublicKeyToken = b03f5f7f11d50a3a"，方法":"System.Data.SqlClient.SqlConnectionFactory.CreateConnection"，级别";:1，"line":0}，{程序集":"System.Data.SqlClient，版本= 4.5.0.1，区域性=中性，PublicKeyToken = b03f5f7f11d50a3a"，方法":"System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection"，级别";:2，"line":0}，{程序集":"System.Data.SqlClient，版本= 4.5.0.1，区域性=中性，PublicKeyToken = b03f5f7f11d50a3a"，方法":"System.Data.ProviderBase.DbConnectionPool.CreateObject"，级别";:3，"line":0}，{程序集":"System.Data.SqlClient，版本= 4.5.0.1，区域性=中性，PublicKeyToken = b03f5f7f11d50a3a"，方法":"System.Data.ProviderBase.DbConnectionPool.UserCreateRequest"，级别";:4，"line":0}，{程序集":"System.Data.SqlClient，版本= 4.5.0.1，区域性=中性，PublicKeyToken = b03f5f7f11d50a3a"，方法":"System.Data.ProviderBase.DbConnectionPool.TryGetConnection"，级别";:5，"line":0}，{程序集":"System.Data.SqlClient，版本= 4.5.0.1，区域性=中性，PublicKeyToken = b03f5f7f11d50a3a"，方法":"System.Data.ProviderBase.DbConnectionPool.TryGetConnection"，级别";:6，"line":0}，{程序集":"System.Data.SqlClient，版本= 4.5.0.1，区域性=中性，PublicKeyToken = b03f5f7f11d50a3a"，方法":"System.Data.ProviderBase.DbConnectionFactory.TryGetConnection"，级别";:7，"line":0}，{程序集":"System.Data.SqlClient，版本= 4.5.0.1，区域性=中性，PublicKeyToken = b03f5f7f11d50a3a"，方法":"System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal"，级别";:8，"line":0}，{程序集":"System.Data.SqlClient，版本= 4.5.0.1，区域性=中性，PublicKeyToken = b03f5f7f11d50a3a"，方法":"System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection"，级别";:9，"line":0}，{程序集":"System.Data.SqlClient，版本= 4.5.0.1，区域性=中性，PublicKeyToken = b03f5f7f11d50a3a"，方法":"System.Data.SqlClient.SqlConnection.TryOpen"，级别";:10，"line":0}，{"assembly":"System.Data.SqlClient，版本= 4.5.0.1，Culture =中性，PublicKeyToken = b03f5f7f11d50a3a"，方法":"System.Data.SqlClient.SqlConnection.Open"，级别";:11，"line":0}，{程序集":"Microsoft.EntityFrameworkCore.Relational，版本＝ 2.2.6.0，文化＝中性，PublicKeyToken ＝ adb9793829ddae60"，方法":"Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnection"，级别";:12，"line":0}，{程序集":"Microsoft.EntityFrameworkCore.Relational，版本＝ 2.2.6.0，文化＝中性，PublicKeyToken ＝ adb9793829ddae60"，方法":"Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open"，级别";:13，"line":0}，{程序集":"Microsoft.EntityFrameworkCore.Relational，版本＝ 2.2.6.0，文化＝中性，PublicKeyToken ＝ adb9793829ddae60"，方法":"Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1 + Enumerator.BufferlessMoveNext"，级别":14，行":0}，{程序集":"Microsoft.EntityFrameworkCore.SqlServer，版本＝ 2.2.6.0，文化＝中性，PublicKeyToken ＝ adb9793829ddae60"，方法":"Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute"，级别":15，行":0}，{程序集":"Microsoft.EntityFrameworkCore.Relational，版本＝ 2.2.6.0，文化＝中性，PublicKeyToken ＝ adb9793829ddae60"，方法":"Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1 + Enumerator.MoveNext"，"level":16，"line":0}，{程序集":"Microsoft.EntityFrameworkCore，版本＝ 2.2.6.0，文化＝中性，PublicKeyToken ＝ adb9793829ddae60"，方法":"Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider +< _TrackEntities> d__17".2.MoveNext"，"level":17，"line":0}，{汇编":"Microsoft.EntityFrameworkCore，版本＝ 2.2.6.0，文化＝中性，PublicKeyToken ＝ adb9793829ddae60"，方法":"Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider + ExceptionInterceptor`1 + EnumeratorExceptionInterceptor.MoveNext"，"level":18，"line":0}，{程序集":"System.Linq，版本= 4.2.1.0，区域性=中性，PublicKeyToken = b03f5f7f11d50a3a"，方法":"System.Linq.Enumerable + SelectEnumerableIterator`2.MoveNext"，级别";:19，"line":0}，{"assembly":"System.Linq，Version = 4.2.1.0，Culture = neutral，PublicKeyToken = b03f5f7f11d50a3a"，"method":"System.Linq.Enumerable.TryGetFirst"，"level":20，" line":0}，{"assembly":"MaskedProjectPNameIngestor，Version ＝ 1.0.0.0，Culture ＝ neutral，PublicKeyToken ＝ null"，"method":"MaskedProjectPNameIngestor.SRFunction.RetrieveCodeAsync"，"level":21，"line":182，"fileName":"D:\\ a \\ 1 \\ s \\ MaskedProject \\ PNameIngestion \\ MaskedProjectPNameIngestor \\ SRFunction.cs"，}，{"assembly":"MaskedProjectPNameIngestor，版本＝ 1.0.0.0，文化＝中性，PublicKeyToken ＝ null"，方法":"MaskedProjectPNameIngestor.SRFunction +< Run> d__5.MoveNext"，级别":22，行":121，文件名":"D:\\ a \\ 1 \\ s \\ MaskedProject \\ PNameIngestion \\ MaskedProjectPNameIngestor \\ SRFunction.cs"，}]，"outerId":"0"，"message":用户'<令牌识别的主体>'的登录失败.令牌已过期."，类型":"System.Data.SqlClient.SqlException"，"id":"64152618"}]

感谢任何帮助预先感谢

解决方案

您需要 CREATE USER< Azure_AD_principal_name>从EXTERNAL PROVIDER; 在Azure SQL Server中.您可以看到 

 使用系统；使用System.IO;使用System.Threading.Tasks;使用Microsoft.AspNetCore.Mvc;使用Microsoft.Azure.WebJobs；使用Microsoft.Azure.WebJobs.Extensions.Http;使用Microsoft.AspNetCore.Http;使用Microsoft.Extensions.Logging;使用Newtonsoft.Json;使用System.Data.SqlClient;使用Microsoft.Azure.Services.AppAuthentication;使用System.Diagnostics;命名空间fu​​nc_sqlcon{公共静态类Function1{[FunctionName("Function1"))]公共静态异步Task< IActionResult>跑步([HttpTrigger(AuthorizationLevel.Function，"get"，"post"，Route = null)] HttpRequest请求，ILogger日志){var str ="Server = tcp:*********.database.windows.net，1433;初始目录= ********;持久性安全信息= False; MultipleActiveResultSets = False;加密= True； TrustServerCertificate = False；连接超时= 30；；//Environment.GetEnvironmentVariable(sqldb_connection");字符串AccessToken;SqlConnection连接=新的SqlConnection(str);尝试{AccessToken =等待(新的AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/"，"e4c9ab4e-bd27-40d5-8459-230ba2a757fb");connection.AccessToken = AccessToken;}抓住(前例外){Trace.WriteLine(ex.ToString());扔;}connection.Open();返回新的OkObjectResult(AccessToken);}}}

I am using a function app which is service bus triggered. It connects to Azure SQL Server using Managed Identity.

The connection is created using the following code.

new SqlConnection(this.ConnectionString)
        {
            // AzureServiceTokenProvider handles caching the token and refreshing it before it expires
            AccessToken = new AzureServiceTokenProvider().GetAccessTokenAsync("https://database.windows.net/").Result
        };

I get a few thousand events every day, and all these are failing on connecting to the database.

The stack trace from our logs is

[{"parsedStack":[{"assembly":"System.Data.SqlClient, Version=4.5.0.1, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a","method":"System.Data.SqlClient.SqlInternalConnectionTds..ctor","level":0,"line":0},
{"assembly":"System.Data.SqlClient, Version=4.5.0.1, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a","method":"System.Data.SqlClient.SqlConnectionFactory.CreateConnection","level":1,"line":0},
{"assembly":"System.Data.SqlClient, Version=4.5.0.1, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a","method":"System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection","level":2,"line":0},
{"assembly":"System.Data.SqlClient, Version=4.5.0.1, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a","method":"System.Data.ProviderBase.DbConnectionPool.CreateObject","level":3,"line":0},
{"assembly":"System.Data.SqlClient, Version=4.5.0.1, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a","method":"System.Data.ProviderBase.DbConnectionPool.UserCreateRequest","level":4,"line":0},
{"assembly":"System.Data.SqlClient, Version=4.5.0.1, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a","method":"System.Data.ProviderBase.DbConnectionPool.TryGetConnection","level":5,"line":0},
{"assembly":"System.Data.SqlClient, Version=4.5.0.1, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a","method":"System.Data.ProviderBase.DbConnectionPool.TryGetConnection","level":6,"line":0},
{"assembly":"System.Data.SqlClient, Version=4.5.0.1, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a","method":"System.Data.ProviderBase.DbConnectionFactory.TryGetConnection","level":7,"line":0},
{"assembly":"System.Data.SqlClient, Version=4.5.0.1, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a","method":"System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal","level":8,"line":0},
{"assembly":"System.Data.SqlClient, Version=4.5.0.1, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a","method":"System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection","level":9,"line":0},
{"assembly":"System.Data.SqlClient, Version=4.5.0.1, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a","method":"System.Data.SqlClient.SqlConnection.TryOpen","level":10,"line":0},
{"assembly":"System.Data.SqlClient, Version=4.5.0.1, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a","method":"System.Data.SqlClient.SqlConnection.Open","level":11,"line":0},
{"assembly":"Microsoft.EntityFrameworkCore.Relational, Version=2.2.6.0, Culture=neutral, PublicKeyToken=adb9793829ddae60","method":"Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnection","level":12,"line":0},
{"assembly":"Microsoft.EntityFrameworkCore.Relational, Version=2.2.6.0, Culture=neutral, PublicKeyToken=adb9793829ddae60","method":"Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open","level":13,"line":0},
{"assembly":"Microsoft.EntityFrameworkCore.Relational, Version=2.2.6.0, Culture=neutral, PublicKeyToken=adb9793829ddae60","method":"Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1+Enumerator.BufferlessMoveNext","level":14,"line":0},
{"assembly":"Microsoft.EntityFrameworkCore.SqlServer, Version=2.2.6.0, Culture=neutral, PublicKeyToken=adb9793829ddae60","method":"Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute","level":15,"line":0},
{"assembly":"Microsoft.EntityFrameworkCore.Relational, Version=2.2.6.0, Culture=neutral, PublicKeyToken=adb9793829ddae60","method":"Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1+Enumerator.MoveNext","level":16,"line":0},
{"assembly":"Microsoft.EntityFrameworkCore, Version=2.2.6.0, Culture=neutral, PublicKeyToken=adb9793829ddae60","method":"Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider+<_TrackEntities>d__17`2.MoveNext","level":17,"line":0},
{"assembly":"Microsoft.EntityFrameworkCore, Version=2.2.6.0, Culture=neutral, PublicKeyToken=adb9793829ddae60","method":"Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider+ExceptionInterceptor`1+EnumeratorExceptionInterceptor.MoveNext","level":18,"line":0},
{"assembly":"System.Linq, Version=4.2.1.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a","method":"System.Linq.Enumerable+SelectEnumerableIterator`2.MoveNext","level":19,"line":0},
{"assembly":"System.Linq, Version=4.2.1.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a","method":"System.Linq.Enumerable.TryGetFirst","level":20,"line":0},
{"assembly":"MaskedProjectPNameIngestor, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null","method":"MaskedProjectPNameIngestor.SRFunction.RetrieveCodeAsync","level":21,"line":182,"fileName":"D:\\a\\1\\s\\MaskedProject\\PNameIngestion\\MaskedProjectPNameIngestor\\SRFunction.cs"},
{"assembly":"MaskedProjectPNameIngestor, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null","method":"MaskedProjectPNameIngestor.SRFunction+<Run>d__5.MoveNext","level":22,"line":121,"fileName":"D:\\a\\1\\s\\MaskedProject\\PNameIngestion\\MaskedProjectPNameIngestor\\SRFunction.cs"}],
"outerId":"0","message":"Login failed for user '<token-identified principal>'. Token is expired.","type":"System.Data.SqlClient.SqlException","id":"64152618"}]

Appreciate any help Thanks in advance

解决方案

You need CREATE USER <Azure_AD_principal_name> FROM EXTERNAL PROVIDER; in Azure SQL Server.You can see offical document.

And you also need to open Identity status On in portal.

My Result.

using System;
using System.IO;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using System.Data.SqlClient;
using Microsoft.Azure.Services.AppAuthentication;
using System.Diagnostics;

namespace func_sqlcon
{
    public static class Function1
    {
        [FunctionName("Function1")]
        public static async Task<IActionResult> Run(
            [HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req,
        ILogger log)
        {
            var str = "Server=tcp:*********.database.windows.net,1433;Initial Catalog=********;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
            //Environment.GetEnvironmentVariable("sqldb_connection");
            string AccessToken;
            SqlConnection connection = new SqlConnection(str);
            try
            {
                AccessToken = await (new AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/", "e4c9ab4e-bd27-40d5-8459-230ba2a757fb");
                connection.AccessToken = AccessToken;
            }
            catch (Exception ex)
            {
                Trace.WriteLine(ex.ToString());
                throw;
            }
            connection.Open();
            return new OkObjectResult(AccessToken);
        }
    }
}

这篇关于用户'&lt;令牌标识的主体&gt;'登录失败.使用Azure Function应用程序和使用托管身份的Azure SQL服务时令牌已过期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
C#/.NET最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆