Azure App Service无法访问SQL Server-用户'NT AUTHORITY \ ANONYMOUS LOGON'登录失败 [英] Azure App Service can't access SQL Server - Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

问题描述

我在Azure中有一个应用程序服务，它作为我正在设计的系统的API.由于API负责直接访问数据库，因此我显然不希望在任何可能的地方存储包含凭据的连接字符串，因此我希望使用托管身份向App Service授予对数据库的访问权限(也托管在Azure上)

I have an app service in Azure operating as an API for a system I'm designing. As the API is responsible for accessing the database directly, I obviously don't want to be storing connection strings containing credentials anywhere if possible, so am looking to use Managed Identities to grant the App Service access to the database (also hosted on Azure).

在Azure门户中，我已在App Service的设置"部分中启用了系统分配的身份，然后通过SQL Server->访问控制->角色分配->为该服务赋予了SQL Server所有者的角色.添加.

Within the Azure portal, I've enabled System-Assigned Identity within the Settings section of the App Service, then given the service the role of owner of the SQL Server via SQL Server -> Access Control -> Role Assignments-> Add.

据我所知，Active Directory用户甚至不应该使用它，因为它们是用户分配的身份而不是系统分配的身份，并且需要进行更多设置(或将其凭据存储在连接字符串中).

As I understand it, Active Directory Users shouldn't even come into this as they are user-assigned identities rather than system-assigned identities, and take more setting up (or storing their credentials in the connection string).

至于代码，它几乎是此版本的副本>> https://github.com/medhatelmasry/JwtAuthentication ，唯一的区别是我添加了

As for the code, it's pretty much a carbon copy of this >> https://github.com/medhatelmasry/JwtAuthentication, the only differences being that I've added

services.BuildServiceProvider().GetService<ApplicationDbContext>().Database.Migrate();

到Startup.cs中ConfigureServices方法的末尾，并按照Microsoft的说明将以下内容添加到ApplicationDbContext的构造函数中:

to the end of the ConfigureServices method within Startup.cs, and added the below to the constructor of ApplicationDbContext as per Microsoft's instructions:

var conn = (System.Data.SqlClient.SqlConnection)Database.GetDbConnection();

conn.AccessToken = (new Microsoft.Azure.Services.AppAuthentication.AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/").Result;

但是，当尝试在Azure中运行此服务时，调用services.BuildServiceProvider().GetService<ApplicationDbContext>().Database.Migrate();时出现异常:

When attempting to run this service in Azure, however, I get an exception when calling services.BuildServiceProvider().GetService<ApplicationDbContext>().Database.Migrate();:

用户'NT AUTHORITY \ ANONYMOUS LOGON'登录失败

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

我已经尝试了StackOverflow，MSDN，Azure帮助，Pluralsight以及Google出现的任何随机论坛，但都没有找到任何答案.我只熬了整整一个星期，直到每天愚蠢的时候才试图修复连接字符串配置，却发现Azure正在更改我给它的连接字符串参数的名称，而没有说出任何关于它(在任何有关它的Microsoft文档中也没有).

I've tried StackOverflow, MSDN, Azure Help, Pluralsight and whatever random forums turn up from Google, and not managed to find an answer on any of them. I've only just got through a whole week of staying up until stupid o'clock every day trying to fix connection string configurations only to find Azure was changing the name of the connection string parameter that I was giving it and not saying a word about it (and nothing in any Microsoft documentation about it either).

Azure变得非常痛苦，我什至还没有开始向API添加端点，更不用说创建一个实际的应用程序来使用它了，这太可笑了.

Azure is becoming a serious pain in my ass, I haven't even started adding endpoints to the API yet, let alone creating an actual application to use it, this is ridiculous.

推荐答案

最终在这里找到了答案>>

Eventually found the answer here >> https://docs.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/tutorial-windows-vm-access-sql#create-a-contained-user-in-the-database-that-represents-the-vms-system-assigned-identity

App Service确实已设置为服务器的所有者，但没有在数据库上设置用户，因此我的问题通过登录进入得以解决通过SSMS并运行数据库:

The App Service was indeed set as an owner of the server, but hadn't had a user provisioned on the database, so my problem was resolved by logging into the database via SSMS and running:

CREATE USER [My App Service Name] FROM EXTERNAL PROVIDER

然后:

ALTER ROLE db_owner ADD MEMBER [My App Service Name]

但是，我在服务器的访问控制(IAM)页面上删除了App Service的所有权角色，并且仍然能够成功连接，不确定为什么会这样，但这可能只是我缺乏SQL用户知识部分.实际上，这很适合我，因为目前我的App Service在数据库本身(而不是整个服务器)上分配了具有db_owner角色的预配置SQL用户.

However, I removed the ownership role of the App Service on the server's Access Control (IAM) page, and am still able to connect successfully, not sure why that is but this is probably just a lack of SQL user knowledge on my part. It actually suits me as at the moment my App Service has a provisioned SQL user with db_owner role assigned on the database itself, but not on the overall server.

这篇关于Azure App Service无法访问SQL Server-用户'NT AUTHORITY \ ANONYMOUS LOGON'登录失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！