Security token could not be authenticated or authorized error ( Node JS connectivity with MS SQL Server using Tedious)

[Abhishek-Singh-Rana]

I am trying to connect to SQL Server via ActiveDirectoryPassword authentication mechanism and I am using 'Tedious' for that.

However whenever I run my project I run into the error:

Security token could not be authenticated or authorized

Below is my code sample which I derived after referring to https://tediousjs.github.io/tedious/api-connection.html and https://docs.microsoft.com/en-us/sql/connect/node-js/step-3-proof-of-concept-connecting-to-sql-using-node-js?view=sql-server-ver15:

`

var Connection = require('tedious').Connection;  

const config = {
server: 'server-name',    

options: {
    database: 'database-name.database.windows.net',
    encrypt: true,
    port: 1433        
        },
authentication: {
    type: "azure-active-directory-password",
    options: {
        userName: "myusername",
        password: "mypassword",
                
        }
    }
}
var connection = new Connection(config);  
connection.on("connect", err => {
    if (err) {                       
      console.error(err.message);
    } else {
        console.log("Connected"); 
    }
  });

connection.connect();
`

What can be the possible reason for this error?. I read on Tedious documentation that:

Best Practices Tedious supports an authentication type: azure-active-directory-password. This is the recommended method because Tedious will handle the process of retrieving the Azure token internally instead of requiring the user to manually retrieve the token as shown below.

Does this mean I need to do some setting at server side or something on the azure portal etc?

Thanks in advance.

[MichaelSun90]

Hi @Abhishek-Singh-Rana For the "Security token could not be authenticated or authorized" error, it should be caused that you are missing a tenantID from the connection config. If you do not provide a tenant Id here, a default id "common" will be used here, which, may do not work with your current server-side setup. If you search "azure-active-directory-password" from our GitHub doc, you should see an example that has tenantID in it.

You can provide you tenantId like this:

const config = {
    authentication: {
        options: {
            userName: 'userID', // update me
            password: '' ,// update me
            tenantId: '< tenantId >' ,// update me
        },
        type: 'azure-active-directory-password'
    },
    server: 'ServerName', // update me
    options: {
        database: 'DBName', //update me
        encrypt: true
}

[Abhishek-Singh-Rana]

Hello @MichaelSun90 ,

Thanks for your response. However, When I searched for 'tenantId' parameter it was mentioned that it's optional.

"authentication": { "type":'azure-active-directory-password', ..., "options": { "userName": value, "password": value, "tenantId": value (Optional) } }

Also, I am not sure where exactly can I find the 'tenantId' value. Is it somewhere on the azure portal under the server config details?. Thanks

[MichaelSun90]

Hi @Abhishek-Singh-Rana , it is optional, but if you do not provide tenantId, a default value will be used. We are making a change: #1411 for the doc to make this part more clear. You can find your tenantId from Azure portal. if you google tenantID and azure, you will find a lot of links that should help. Like this one: link.

[Abhishek-Singh-Rana]

Hi @MichaelSun90 , I tried passing the 'tenantId' as following:

var Connection = require('tedious').Connection;
const config = {    
    authentication: {        
        options: {
            userName: 'myusername',
            password: 'mypassword',
            tenantId: '3cbcd3-idsayXYZ0123456789-0d11484d',
        },
        type: 'azure-active-directory-password',        
    },
    server: 'server-name.database.windows.net',
    
    options: {
        database: 'database-name',
        encrypt: true,
        port: 1433
    }    
};

and ran into Security token could not be authenticated or authorized error again.

[Abhishek-Singh-Rana]

Installing tedious 12.3.0 version resolved my issue.

[MichaelSun90]

Hi @Abhishek-Singh-Rana , just wondering which version of tedious you were on before you change to 12.3? When you got it to work with 12.3, did you use the same configuration with the tenant Id or you do not have to provide the tenant id, and it works?

[Abhishek-Singh-Rana]

@MichaelSun90 , I was using '14.3.0' before. Tedious >= 13.2.0 now needs a client and tenant id for connection authentication "azure-active-directory-password".

With this version, issue is resolved irrespective of I use tenantid or not.

[MichaelSun90]

Hi @Abhishek-Singh-Rana , May I ask which version of SQL server you are trying to work with? From this, seems you may be facing the same issue that James has with #1408. We have to migrate to MSAL from ADAL after 12.3 for AAD-related authentication due to Microsoft is dropping support for the ADAL library. Seems, that MSAL may introduce some complexity to the authentication.

[dangerski]

I am having the same issue connecting to an SQL Server DB on Azure with tedious, where I don't have control of the DB to create an application or client, the 3rd party just created an AD account with access that works with other tools just not with Tedious.

I've looked at these other issues: https://github.com/tediousjs/tedious/issues/1408 https://github.com/tediousjs/tedious/issues/1415 https://github.com/tediousjs/tedious/issues/1417

and this comment: https://github.com/tediousjs/tedious/issues/1417#issuecomment-1069744097

However, with this connector below and using the same authentication (AAD - password) it doesn't require a client or tenant id.

https://github.com/microsoft/mssql-jdbc/blob/main/src/samples/azureactivedirectoryauthentication/src/main/java/AzureActiveDirectoryAuthentication.java

Is there a way to connect without the clientId or tenantId like these other connectors? Any idea why one requires the client and the other doesn't?

Thanks

[MichaelSun90]

Hi @dangerski, I checked with the team, and they have explained why we behavior differently with the Java driver. The Java driver is Microsoft-owned and has its own 1st-party appID/clientID embedded. Since Tedious isn't MS-owned, it doesn't have its own clientID (registered application) and users will need to provide one. For tenantID, that is sent from the server during the connection phase for the client to use. That's where the JDBC driver gets it. Hope this explain the different behavior between two drivers.

For tedious, we do have some other authentication type for AAD authentication. Maybe give them a try, see if any of those suite for your case. You probably read this page already but I will just leave a link to our doc for convenience purpose.

[9y4n]

I've faced the same issue whn using mssql:9.1.1 package Security token could not be authenticated or authorized error

after downgrading package to mssql:7.3.5 not seeing that I was able to establish connection

anything above > 7.3.5 is failing

CODE I USED

const sql = require('mssql');

const config = {
     server: '<SERVER>',
    port: 1433, 
    database: '<DB>',
    requestTimeout: 60000,
    connectionTimeout: 60000,
    pool: {
        min: 0,
        max: 10,    
        idleTimeoutMillis: 30000
    },
    authentication: {
        type: 'azure-active-directory-password',
        options: {
            userName: '<USERNAME>',
            password: '<PASSWORD', 
            
        }
    },
    options: {
        encrypt: true,
        trustServerCertificate: true
    }
}

console.log("Starting...");
connectAndQuery();

async function connectAndQuery() {
    try {
        var poolConnection = await sql.connect(config);

        console.log("Reading rows from the Table...");
        var resultSet = await poolConnection.request().query(`SELECT name, database_id, create_date  
        FROM sys.databases `);

        console.log(`${resultSet.recordset.length} rows returned.`);

        // close connection only when we're certain application is finished
        poolConnection.close();
    } catch (err) {
        console.error(err.message);
    }
}

[MichaelSun90]

Hi @9y4n , I think update mssql from 7.3.5 to 9.1.1 also update tedious versions underneath. The newer tedious version include a change that dropped hard coded client id support with on the tedious side. The current behavior on tedious side is enforce that user to provide client id and tenant Id in order to connect. This comment actually explain why we decide to make this behavior change. This commit has the change for remove the default clinetId support.