Lesson Learned #273:UsernamePasswordCredential: tenantId,clientId,username and password are required

[Jose_Manuel_Jurado]

Today, I worked on a service request that our customer got the following error message: UsernamePasswordCredential: tenantId, clientId, username and password are required parameters using Node.Js connecting Azure SQL DB with Tedious. Following, I would like to share my findings here.

The customer is using the following code: 

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"
    database:"DatabaseName",
    authentication: {
        type: "azure-active-directory-password",
        options: {
            username: "username@microsoft.com",
            password: "MyPassword",
            clientId: "x47b3ce9-xxxxx-xxxxx-xxxxxx",
            tenantId: "x2f988bf-xxxx-xxxxx-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();

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

Checking the source code here: tedious/connection.ts at master · tediousjs/tedious (github.com) in the function called UsernamePasswordCredential in the line number 3411. The parameter used is authentication.options.userName instead of authentication.options.username. 

switch (authentication.type) {
            case 'azure-active-directory-password':
              credentials = new UsernamePasswordCredential(
                authentication.options.tenantId ?? 'common',
                authentication.options.clientId,
                authentication.options.userName,
                authentication.options.password
              );
              break;

All points to that the value of the parameter using authentication.options.userName will be empty and for this reason we got this error message. Once we change in the connection string authentication.options.userName the error is bypassed.

var config = {
    server: "servername.database.windows.net", // or "localhost"
    database:"DatabaseName",
    authentication: {
        type: "azure-active-directory-password",
        options: {
            userName: "username@microsoft.com",
            password: "MyPassword",
            clientId: "x47b3ce9-xxxxx-xxxxx-xxxxxx",
            tenantId: "x2f988bf-xxxx-xxxxx-xxxxx"
        }
    }
};

Enjoy!