AAD Token for Datatables Editor Connection String

huangapple go评论57阅读模式
英文:

AAD Token for Datatables Editor Connection String

问题

我已经成功按照这个教程使用访问令牌来让用户连接到 Azure SQL 数据库。

public DBCtx(DbContextOptions<DBCtx> options, IHttpContextAccessor accessor) : base(options)
{
    var conn = Database.GetDbConnection() as SqlConnection;
    conn.AccessToken = accessor.HttpContext.Request.Headers["X-MS-TOKEN-AAD-ACCESS-TOKEN"];
}

这在应用服务中正常工作。我现在的问题是如何在 Datatables Editor 的连接字符串中使用 AAD 访问令牌。

[HttpGet, HttpPost]
public ActionResult Table()
{
    string dbConnection = _configuration.GetConnectionString("AzureSQL");

    var db = new Database("azure", dbConnection, "Microsoft.Data.SqlClient");
    var response = new Editor(db, "Test")
        .Model<Test>()
        .Field(new Field("FullName"))
        .Field(new Field("Updated")
            .Set(Field.SetType.Both)
            .SetValue(@DateTime.UtcNow))
        .Field(new Field("EntryUser")
            .Set(Field.SetType.Both)
            .SetValue(@User.Identity.Name))
        .Process(Request)
        .Data();

    return Json(response);
}

连接字符串对于两者都是相同的:

server=tcp:<db-server-name>.database.windows.net;database=<db-name>

然而,我无法弄清楚如何添加访问令牌。

如果有任何链接或示例,将不胜感激。

英文:

I have successfully followed this tutorial to use an access token for users connecting to an Azure SQL database.

public DBCtx(DbContextOptions<DBCtx> options, IHttpContextAccessor accessor) : base(options)
	{
        var conn = Database.GetDbConnection() as SqlConnection;
        conn.AccessToken = accessor.HttpContext.Request.Headers["X-MS-TOKEN-AAD-ACCESS-TOKEN"];
    }

This works in App Service as intended. My issue now is using the AAD access token for the connection string for Datatables Editor.

[HttpGet, HttpPost]
    public ActionResult Table()
    {
        string dbConnection = _configuration.GetConnectionString("AzureSQL");

        var db = new Database("azure", dbConnection, "Microsoft.Data.SqlClient");
        var response = new Editor(db, "Test")
            .Model<Test>()
            .Field(new Field("FullName"))
            .Field(new Field("Updated")
                .Set(Field.SetType.Both)
                .SetValue(@DateTime.UtcNow))
            .Field(new Field("EntryUser")
                .Set(Field.SetType.Both)
                .SetValue(@User.Identity.Name))
            .Process(Request)
            .Data();

        return Json(response);
    }

The connection string is the same for both

server=tcp:<db-server-name>.database.windows.net;database=<db-name>

However, I am unable to figure out how to add the access token.

Any links or examples are greatly appreciated.

答案1

得分: 0

以下是代码的翻译部分:

由于我知道至少还有其他人会遇到这个问题,这里是来自DataTables的答案,得到了Allan的帮助:

    使用 DataTables;
    使用 Microsoft.AspNetCore.Http;
    使用 Microsoft.AspNetCore.Mvc;
    使用 Microsoft.Data.SqlClient;
    使用 Microsoft.EntityFrameworkCore;
    使用 System;
    使用 WebApplication1.Models;

    namespace WebApplication1.Controllers
    {
        public class TestController : Controller
        {
            private readonly IHttpContextAccessor _accessor;
            private readonly DBCtx _context;

            public TestController(IHttpContextAccessor accessor, DBCtx context)
            {
                _accessor = accessor;
                _context = context;
            }

            [ActionName("Index")]
            public IActionResult Index()
            {
                return View();
            }

            [HttpGet, HttpPost]
            public ActionResult Table()
            {
                使用 var conn = _context.Database.GetDbConnection() as SqlConnection;
                conn.AccessToken = _accessor.HttpContext.Request.Headers["X-MS-TOKEN-AAD-ACCESS-TOKEN"];

                var db = new Database("azure", conn)
                    .Adapter("Microsoft.Data.SqlClient");
                var response = new Editor(db, "Test")
                    .Model<Test>()
                    .Field(new Field("FullName"))
                    .Field(new Field("Updated")
                        .Set(Field.SetType.Both)
                        .SetValue(DateTime.UtcNow))
                    .Field(new Field("EntryUser")
                        .Set(Field.SetType.Both)
                        .SetValue(User.Identity.Name))
                    .Process(Request)
                    .Data();

                return Json(response);
            }
        }
    }

请注意,我已经保留了代码中的注释,以保持原始代码的完整性。

英文:

Since I know at least one other person will run into this issue, here is the answer with help from Allan at DataTables:

using DataTables;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System;
using WebApplication1.Models;

namespace WebApplication1.Controllers
{
    public class TestController : Controller
    {
        private readonly IHttpContextAccessor _accessor;
        private readonly DBCtx _context;

        public TestController(IHttpContextAccessor accessor, DBCtx context)
        {
            _accessor = accessor;
            _context = context;
        }

        [ActionName(&quot;Index&quot;)]
        public IActionResult Index()
        {
            return View();
        }

        [HttpGet, HttpPost]
        public ActionResult Table()
        {
            using var conn = _context.Database.GetDbConnection() as SqlConnection;
            conn.AccessToken = _accessor.HttpContext.Request.Headers[&quot;X-MS-TOKEN-AAD-ACCESS-TOKEN&quot;];

            var db = new Database(&quot;azure&quot;, conn)
                .Adapter(&quot;Microsoft.Data.SqlClient&quot;);
            var response = new Editor(db, &quot;Test&quot;)
                .Model&lt;Test&gt;()
                .Field(new Field(&quot;FullName&quot;))
                .Field(new Field(&quot;Updated&quot;)
                    .Set(Field.SetType.Both)
                    .SetValue(@DateTime.UtcNow))
                .Field(new Field(&quot;EntryUser&quot;)
                    .Set(Field.SetType.Both)
                    .SetValue(@User.Identity.Name))
                .Process(Request)
                .Data();

            return Json(response);
        }
    }
}

huangapple
  • 本文由 发表于 2023年7月14日 09:02:34
  • 转载请务必保留本文链接:https://go.coder-hub.com/76684106.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定