使用AAD访问令牌连接到Azure SQL [英] Connecting to Azure SQL using AAD Access Token

问题描述

我正在尝试使用
Azure SQL数据库 >访问令牌通过C＃Web应用从 Azure活动目录（AAD）获取。我正在尝试启用一个方案，用户使用
AAD B2C 登录我的网络应用程序，并使用 JWT 根据Azure SQL数据库对用户进行身份验证。最终目标是我们可以使用SQL Server行级安全性（RLS）来确保用户只能看到他们有权查看的数据，并且在数据库中发生过滤
，而应用程序不必知道用户数据安全。

我已成功配置AAD和Azure SQL（按照此处显示的详细信息：
https： //azure.microsoft.com/en-us/documentation/articles/sql-database-aad-authentication/?cdn=disable)。我已确认可以使用带有" Active Directory通用身份验证"选项的
SQL Server Management Studio （SSMS）使用AAD凭据登录Azure SQL SSMS。我可以查询数据库并查看预期数据。

代码如下所示：

 
 var connection = new SqlConnection（" Data Source = [my database] ] .database.windows.net;初始目录= [我的初始目录];"）; 
 
 connection.AccessToken = jwtAccessToken; 
 
 await connection.OpenAsync（）; 
 
 

但是，当我尝试使用相同的凭据登录我的网络应用程序时，我获得了JWT并将JWT传递给我的SQL连接我收到一个错误：

< pre class ="prettyprint">"登录失败，使用' NT AUTHORITY \ ANONYMOUS LOGON '。

解决方案

Hello-

了解代码详细信息如何使用用户将是一件好事。 Web应用程序中的凭据。我建议离线并直接沟通。请使用    [e-mail]  
来与我们分享详细信息。感谢Mirek。

Mirek Sztajno，高级项目经理&SQL Server   

I am trying to connect to an Azure SQL database using an Access Token obtained from Azure Activity Directory (AAD) via a C# web app. I am trying to enable a scenario where users sign into my web app using AAD B2C and I use the JWT to authenticate the user against the Azure SQL database. The end-goal being that we can use SQL Server Row Level Security (RLS) to ensure users only see data that they’re entitled to see and the filtering happen in the database without applications having to be aware of user data security.

I have successfully configured AAD and Azure SQL (following the details shown here: https://azure.microsoft.com/en-us/documentation/articles/sql-database-aad-authentication/?cdn=disable). I have confirmed that I can sign in to Azure SQL using AAD credentials using SQL Server Management Studio (SSMS) with the "Active Directory Universal Authentication" option within SSMS. I can query the database and see the expected data.

The code looks like this:

var connection = new SqlConnection("Data Source=[my database].database.windows.net;Initial Catalog=[my initial catalog];");
connection.AccessToken = jwtAccessToken;
await connection.OpenAsync();

However, when I try to use the same credentials to sign into my web app, I obtain a JWT and pass the JWT to my SQL connection I receive an error:

"Login failed for use ‘NT AUTHORITY\ANONYMOUS LOGON’.

解决方案

Hello-
It would be good to know the code details how do you use the user credentials in the web app. I suggest to take this offline and communicate directly. Please use the  [e-mail] alias to share the details with us. Thanks Mirek.

Mirek Sztajno, Senior Program Manager  SQL Server team  

这篇关于使用AAD访问令牌连接到Azure SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！