应用程序无法使用托管身份连接到数据库
我有一项Azure应用服务，需要授予对SQL数据库的访问权限（我的帐户下的Azure Portal）。我想通过A 托管身份授予其访问权限，因此我已经打开了系统分配的托管身份。
在SQL Server的访问控制（IAM）下，我添加了应用程序服务作为贡献者。
，
Server=tcp:virtualscorecard.database.windows.net,1433;Initial Catalog=GolfScore; Persist Security Info=False; MultipleActiveResultSets=False; Encrypt=True; TrustServerCertificate=False; Connection Timeout=30; Authentication=Active Directory Managed Identity;
但是当我尝试连接时，我会在日志流中获得以下错误：
[错误] Microsoft.EntityFrameWorkCore.Query：发生异常 迭代询问上下文类型的查询结果 'golfscoreapi.dbcontexts.userprofilecontext'.microsoft.data.sqlclient.sqlexception （0x80131904）：用户登录失败''。 当前未配置该服务器以接受此令牌。
我在连接字符串中尝试了很多变化。我唯一可以工作的是，如果我在连接字符串中添加用户名和密码。但是托管身份应该使这一不必要。
我在做什么错？
更新1
我已经知道我需要将作为用户的应用服务添加到数据库中。为此，我必须首先分配Azure Active Directory Admin。但是，当我尝试时，我无法选择任何有意义的东西。与我相关的唯一条目，无法选择，因为“不允许Microsoft帐户成员”。
更新2：
我通过在Azure Active Directory中创建一个组并使自己成为其中的成员来工作。然后将该组设置为数据库上的Azure Active Directory管理员，使自己能够将我的应用服务（golfscoreapi）添加为数据库上的dbreader。
我唯一无法获得的是为什么我需要为特定的IP地址添加防火墙规则。这似乎是最佳的，因为（据我所知）IP地址可能会更改，例如，如果更改了应用程序服务计划。
I have an Azure App Service which I need to grant access to a SQL Database (also in Azure Portal under my account). I want to grant it access via a Managed Identity, so I have turned on system assigned Managed Identity.
Under Access control (IAM) for the SQL server, I have added the App Service as a contributer.
Server=tcp:virtualscorecard.database.windows.net,1433;Initial Catalog=GolfScore; Persist Security Info=False; MultipleActiveResultSets=False; Encrypt=True; TrustServerCertificate=False; Connection Timeout=30; Authentication=Active Directory Managed Identity;
But when I try to connect, I get the following error in the log stream:
[Error] Microsoft.EntityFrameworkCore.Query: An exception occurred
while iterating over the results of a query for context type
'GolfScoreAPI.DbContexts.UserProfileContext'.Microsoft.Data.SqlClient.SqlException
(0x80131904): Login failed for user ''.
The server is not currently configured to accept this token.
I have tried quite a lot of variations in the connection string. The only one that I have gotten to work, is if I add the username and password in the connection string. But Managed Identities should make this unnecessary.
What am I doing wrong?
Update 1
I have been made aware that I need to add my App Service as a user to the database. To do that, I must first assign an Azure Active Directory admin. But I cannot select anything meaningful when I try that. The only entry related to me, cannot be selected because "Microsoft Account members are not allowed."
Update 2:
I got it to work, by creating a group in Azure Active Directory and making myself an member of it. Then setting that group as Azure Active Directory admin on the database, making myself able to add my App Service (GolfScoreAPI) as a dbreader on the database.
The only thing I still don't get, is why I need to add a firewall rule for the specific IP address. This seems sub-optimal, as that IP address might change (to my knowledge), for instance if the App Service plan is changed.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
是的，重要的作品缺失了。您已经从应用程序服务启用了身份，而SQL Server呢？:)。您需要在SQL Server中添加 Azure Active Directory Admin 。参考： - https：/ 。
来到SQL Server下一步。
在SQL Server DB中运行以下命令。
从外部提供商创建用户[＆lt;＆lt; sidentity_name_of_appservice＆gt;＆gt;];
Alter角色DB_DDLADMIN/ DB_DATAREADER/ DB_DATAWRITER添加成员[＆lt; sidentity_name_of_appservice＆gt;＆gt;];
注： - 如果您使用Visual Studio进行开发，因此在运行本地实例以连接时到Azure SQL，请确保作为用户添加Visual Studio订阅电子邮件ID。
如果您正在使用Visual Studio，请确保
yes important piece is missing. You have enabled the Identity from the App service, what about the SQL server :). You need to add an Azure Active Directory admin in sql server. Reference :- https://learn.microsoft.com/en-us/azure/search/search-howto-managed-identities-sql
You have already done with Below step.
Coming to next step for SQL Server.
Run the Below Command in the sql server db.
CREATE USER [<<Identity_name_of_AppService>>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_ddladmin/ db_datareader/ db_datawriter ADD MEMBER [<<Identity_name_of_AppService>>];
Note:- If you are using Visual studio for development, so while running the Local instance to connect to the Azure SQL, make sure to add your Visual studio subscription Email id as a user.
If you are using the Visual Studio, then make sure