AAD Token for Datatables Editor Connection String

Question

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.

Answer 1

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("Index")]
        public IActionResult Index()
        {
            return View();
        }

        [HttpGet, HttpPost]
        public ActionResult Table()
        {
            using 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);
        }
    }
}