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!