AAD Token for Azure SQL authentication in Editor

mrsearing-all

I have followed this MS tutorial that uses an AAD access token to connect a user to an Azure SQL database and it is working as intended.

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"];
        }

However, for the Edtior connection, I have not been able to add the access token since it does not use the DbContext.

Is it possible to get this access token and add it to the connection that Editor is using?

[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);
        }

allan

Hi,

The Editor Database class has several overloads for its constructor including one which lets you pass in an existing database connection.

So what I think you should do is:

var conn = Database.GetDbConnection() as SqlConnection;
conn.AccessToken = accessor.HttpContext.Request.Headers["X-MS-TOKEN-AAD-ACCESS-TOKEN"];

var db = new Database("azure", conn);

and that should do the job...

Allan

mrsearing-all

Hi Allan,

Thanks for the quick reply.

I am getting the error "'Database' does not contain a definition for 'GetDbConnection'".

Here is the full controller:

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

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

        public TestController(IHttpContextAccessor accessor)
        {
            _accessor = accessor;
        }

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

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

            var db = new Database("azure", conn);
            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);
        }
    }
}

Maybe I am overlooking something simple?

allan

That error sounds like var conn = Database.GetDbConnection() is the line that is throwing the error? It might be that the Editor Database class and whatever you are using a Database are conflicting. What is Database on line 28 in the above?

Allan

mrsearing-all

I had to add in my database context above, like so:

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);
            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);
        }
    }
}

Which did result in an Editor error that 'System.Data.SqlClient' was not found, as I registered 'Microsoft.Data.SqlClient' in Program.cs.

However, I registered 'System.Data.SqlClient' as well and it is all working locally and in App Service as intended.

If you have a way to force 'Microsoft.Data.SqlClient' as the provider factory, that would be the only thing to make this solution perfect. It will not allow me to overload like this:

var db = new Database("azure", conn, "Microsoft.Data.SqlClient");

Allan, thanks for guidance and for DataTables!

allan

You can actually use the Adapter method to do that:

var db = new Database("azure", conn)
  .Adapter("Microsoft.Data.SqlClient");

Allan

mrsearing-all

Perfect! Thank you again!