Lesson Learned #274: Security token could not be authenticated or authorized in Node.js

[Jose_Manuel_Jurado]

Today, I worked on a service request where our customer is facing two main issues: 

Security token could not be authenticated or authorized. at ..\NodeJs\node_modules\tedious\lib\connection.js:2659:35
at processTicksAndRejections (node:internal/process/task_queues:96:5) {code: 'EFEDAUTH',isTransient: undefined}
and
RequestError: Requests can only be made in the LoggedIn state, not the SentLogin7Withfedauth state
at Connection.makeRequest (....\NodeJs\node_modules\tedious\lib\connection.js:2199:24)
at Connection.execSql (...\NodeJs\node_modules\tedious\lib\connection.js:1729:10)
at executeStatement (...\NodeJs\NodejsConsoleApp1\app.js:107:16)
at Connection.<anonymous> (...\NodeJs\NodejsConsoleApp1\app.js:78:5)
at Connection.emit (node:events:390:28) 
at Connection.emit (.....NodeJs\node_modules\tedious\lib\connection.js:1027:18)
at ...\NodeJs\node_modules\tedious\lib\connection.js:2660:22
at processTicksAndRejections (node:internal/process/task_queues:96:5) {
code: 'EINVALIDSTATE',number: undefined,state: undefined,class: undefined,serverName: undefined,procName: undefined,lineNumber: undefined
}

Debugging the code using Visual Studio 2019, I understood that the second error message Requests can only be made in the LoggedIn state, not the SentLogin7Withfedauth state could a consequence of the first one Security token could not be authenticated or authorized.

Trying to reproduce the issue I developed the following Node.Js code to review the situation:

/*'use strict';*/

var Connection = require('tedious').Connection;
var Request = require('tedious').Request;
const readline = require('readline');
const rl = readline.createInterface({
    input: process.stdin,
    output: process.stdout
});


var config = {
    server: "servername.database.windows.net", // or "localhost"
    options: { database: "databasename" },
    authentication: {
        type: "azure-active-directory-password",
        options: {
            userName: "username@domain.com",
            password: "Password!!",
            clientId: "8edefa3d-xxxx",
            tenantId: "7acc0f8a-xxxxx",
        }
    }
};

console.log('Hello world');
var connection = new Connection(config);

// Setup event handler when the connection is established. 
connection.on('connect', function (err) {
    if (err) {
        console.log('Error: ', err)
    }
    // If no error, then good to go...
    console.log('Hello world 2');
    executeStatement();
});

connection.connect();


rl.question('What is your name?', (name) => {
    console.log(`Hello ${name}!`);

    rl.close();
});



function executeStatement() {
    request = new Request("select 42, 'hello world'", function (err, rowCount) {
        if (err) {
            console.log(err);
        } else {
            console.log(rowCount + ' rows');
        }
    });

    request.on('row', function (columns) {
        columns.forEach(function (column) {
            console.log(column.value);
        });
    });

    connection.execSql(request);
}

I was able to reproduce the issue, and all points to that the library is not reaching the user specified within tenantId. Checking the source code in GitHub, I have found a parameter called domain, adding this parameter with the value of tenantId, I was able to connect using Azure Active Directory Password.

/*'use strict';*/

var Connection = require('tedious').Connection;
var Request = require('tedious').Request;
const readline = require('readline');
const rl = readline.createInterface({
    input: process.stdin,
    output: process.stdout
});


var config = {
    server: "servername.database.windows.net", // or "localhost"
    options: { database: "databasename" },
    authentication: {
        type: "azure-active-directory-password",
        options: {
            userName: "username@domain.com",
            password: "Password!!",
            domain: "7acc0f8a-xxxxx",
        }
    }
};

console.log('Hello world');
var connection = new Connection(config);

// Setup event handler when the connection is established. 
connection.on('connect', function (err) {
    if (err) {
        console.log('Error: ', err)
    }
    // If no error, then good to go...
    console.log('Hello world 2');
    executeStatement();
});

connection.connect();


rl.question('What is your name?', (name) => {
    console.log(`Hello ${name}!`);

    rl.close();
});



function executeStatement() {
    request = new Request("select 42, 'hello world'", function (err, rowCount) {
        if (err) {
            console.log(err);
        } else {
            console.log(rowCount + ' rows');
        }
    });

    request.on('row', function (columns) {
        columns.forEach(function (column) {
            console.log(column.value);
        });
    });

    connection.execSql(request);
}

Enjoy!