Support for MSSQL Azure Active Directory (AD) Token authorization

Andrewknackstedt commented 2 years ago

Problem

Need to use MSSQL Authorization token flow to connect to database. Requires ad-hoc connection string building with mssql/tedious.

Specifically this MSDN: https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?tabs=azure-powershell#azure-ad-token

Alternatives

Using a different solution than Prisma. (Or hacking through myself but the project is pretty complex)

Additional context

Our security model requires this type or authorization. We don't have an option to use standard user/password flows.

Yes, I do understand the connection pool implications of using such a flow.

borbea commented 2 years ago

We need the same feature. Is there at least some hack on how to perform this with the current Prisma version?

janpio commented 2 years ago

We have not looked into this further. Is there maybe a Node.js or Rust SQL Server client library based example for this we could look at (or any other language really)? That would be helpful to get an understanding how this actually works. (We know only limited things about SQL Server)

Andrewknackstedt commented 2 years ago

We can do it directly via the tedious npm package like so:

var { Connection, Request } = require("tedious");

async function getConnect() {

    var config = {
        server: "<sqlserver>.database.windows.net",
        authentication: {
            type: "azure-active-directory-access-token",
            options: {
                token: "<AAD-Access-Token>",
            },
        },
        options: {
            debug: {
                packet: true,
                data: true,
                payload: true,
                token: false,
                log: true,
            },
            database: "app-test",
            encrypt: true,
        },
    };

    var connection = new Connection(config);
    connection.connect();
    connection.on("connect", function (err) {
        if (err) {
            console.log(err);
        }
        executeStatement(connection);
    });

    connection.on("debug", function (text) {
        console.log(text);
    });
}
function executeStatement(connection) {
    request = new Request(`SELECT TOP (1000) 
  *
  FROM [app-test].[dbo].[app_users]`, function (err, rowCount) {
        if (err) {
            console.log(err);
        } else {
            console.log(rowCount + " rows");
        }
        connection.close();
    });

    request.on("row", function (columns) {
        columns.forEach(function (column) {
            if (column.value === null) {
                console.log("NULL");
            } else {
                console.log(column.value);
            }
        });
    });

    request.on("done", function (rowCount, more) {
        console.log(rowCount + " rows returned");
    });

    connection.execSql(request);
}

getConnect()
    .then(() => {
        console.log("run successfully");
    })
    .catch((err) => {
        console.log(err);
    });

oneton commented 2 years ago

If you like some additional background info: how to connect and retrieve the token (in NodeJS and some other languages) is also described in the docs for Azure app service

fa-bwilliams commented 1 year ago

This is considered a must have in many Azure SQL environments. In addition to or rather than type: "azure-active-directory-access-token", we also need other 'type' options that are more popular and easier to work with like azure-active-directory-default and azure-active-directory-msi-app-service. Here is the Tedious Documentation detailing their API support for the different AAD authentication types.