场景
我有一个 Azure 函数应用，当将文件添加到 Blob 存储时将触发该应用。它将每天触发一次。
该应用将调用 SQL Azure 数据库中的存储进程，使用函数应用的托管服务标识进行连接。该存储的 proc 具有WITH EXECUTE AS OWNER子句，它会将添加的文件批量插入到数据库中。
注意：已创建托管标识的数据库用户，并被授予执行相关存储过程的权限。
问题所在
默认情况下，存储的 proc 调用将失败，并显示错误消息(下面的完整堆栈跟踪(：
无法作为数据库主体执行，因为主体"dbo"不存在，无法模拟此类型的主体，或者您没有权限。
如果我使用管理员用户 ID 登录到 SQL 管理工作室中的数据库，只需执行EXECUTE AS USER = '...'语句，然后很快重新触发函数应用，则来自函数应用的下一个存储过程调用将成功。
似乎我需要手动"刷新"数据库中的某些内容，以允许来自函数应用的存储进程调用能够成功模拟。
注意：托管服务标识通常在调用数据库时起作用。它似乎只是存在数据库用户模拟问题。
问题
如果我想使用托管服务标识调用存储的进程，如何解决此问题？
异常堆栈跟踪
Microsoft.Azure.WebJobs.Host.FunctionInvocationException: Exception while executing function: OnFileAddedToInbox
---> Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at Microsoft.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader(Boolean isInternal, Boolean forDescribeParameterEncryption)
at Microsoft.Data.SqlClient.SqlCommand.InternalEndExecuteNonQuery(IAsyncResult asyncResult, Boolean isInternal, String endMethod)
at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult)
at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location where exception was thrown ---
at AzureFunc.OnFileAddedToInbox.Run(CloudBlockBlob blob, String fileName, ILogger logger, CancellationToken cancellationToken)
at Microsoft.Azure.WebJobs.Host.Executors.VoidTaskMethodInvoker`2.InvokeAsync(TReflected instance, Object[] arguments) in C:projectsazure-webjobs-sdk-rqm4tsrcMicrosoft.Azure.WebJobs.HostExecutorsVoidTaskMethodInvoker.cs:line 20
at Microsoft.Azure.WebJobs.Host.Executors.FunctionInvoker`2.InvokeAsync(Object instance, Object[] arguments) in C:projectsazure-webjobs-sdk-rqm4tsrcMicrosoft.Azure.WebJobs.HostExecutorsFunctionInvoker.cs:line 52
at Microsoft.Azure.WebJobs.Host.Executors.FunctionExecutor.InvokeAsync(IFunctionInvoker invoker, ParameterHelper parameterHelper, CancellationTokenSource timeoutTokenSource, CancellationTokenSource functionCancellationTokenSource, Boolean throwOnTimeout, TimeSpan timerInterval, IFunctionInstance instance) in C:projectsazure-webjobs-sdk-rqm4tsrcMicrosoft.Azure.WebJobs.HostExecutorsFunctionExecutor.cs:line 585
at Microsoft.Azure.WebJobs.Host.Executors.FunctionExecutor.ExecuteWithWatchersAsync(IFunctionInstanceEx instance, ParameterHelper parameterHelper, ILogger logger, CancellationTokenSource functionCancellationTokenSource) in C:projectsazure-webjobs-sdk-rqm4tsrcMicrosoft.Azure.WebJobs.HostExecutorsFunctionExecutor.cs:line 532
at Microsoft.Azure.WebJobs.Host.Executors.FunctionExecutor.ExecuteWithLoggingAsync(IFunctionInstanceEx instance, ParameterHelper parameterHelper, IFunctionOutputDefinition outputDefinition, ILogger logger, CancellationTokenSource functionCancellationTokenSource) in C:projectsazure-webjobs-sdk-rqm4tsrcMicrosoft.Azure.WebJobs.HostExecutorsFunctionExecutor.cs:line 468
at Microsoft.Azure.WebJobs.Host.Executors.FunctionExecutor.ExecuteWithLoggingAsync(IFunctionInstanceEx instance, FunctionStartedMessage message, FunctionInstanceLogEntry instanceLogEntry, ParameterHelper parameterHelper, ILogger logger, CancellationToken cancellationToken) in C:projectsazure-webjobs-sdk-rqm4tsrcMicrosoft.Azure.WebJobs.HostExecutorsFunctionExecutor.cs:line 278
ClientConnectionId:712b8ea4-c8e9-4701-aed1-643b16b111d9
Error Number:15517,State:1,Class:16
ClientConnectionId before routing:d9545837-a804-4a42-8e84-12d5edac5f17
Routing Destination:***.database.windows.net,11069
--- End of inner exception stack trace ---
at Microsoft.Azure.WebJobs.Host.Executors.FunctionExecutor.ExecuteWithLoggingAsync(IFunctionInstanceEx instance, FunctionStartedMessage message, FunctionInstanceLogEntry instanceLogEntry, ParameterHelper parameterHelper, ILogger logger, CancellationToken cancellationToken) in C:projectsazure-webjobs-sdk-rqm4tsrcMicrosoft.Azure.WebJobs.HostExecutorsFunctionExecutor.cs:line 322
at Microsoft.Azure.WebJobs.Host.Executors.FunctionExecutor.TryExecuteAsyncCore(IFunctionInstanceEx functionInstance, CancellationToken cancellationToken) in C:projectsazure-webjobs-sdk-rqm4tsrcMicrosoft.Azure.WebJobs.HostExecutorsFunctionExecutor.cs:line 117
此处的问题是托管标识需要创建具有相同名称的用户才能通过 SQL 脚本访问数据库。
尝试跑步
CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;
并为用户提供适当的访问权限。
一个没有充分记录的标注是，如果删除并重新创建 Azure 函数，则需要再次运行脚本，因为它将具有不同的指纹。 Azure SQL 数据库是为数不多的资源之一，需要像这样为托管标识重新分配访问权限。