Connect() with a new Azure SQL Auth Token doesnt update the connection, causing Token Expired error

[cameronstubber]

I provide an authenticated Azure AD Token in the Connection Options, which is good for 60 minutes, to the Connect() function. I track the token value over time, and once it has been refreshed, I call Connect() with a new Connection Options containing the new token. The token does not get updated within node-mssql/tedious, and eventually the SQL Connection stops working and returns token-expired errors.

I'm sure this is probably just a lack of understanding on how to use the module to manage the connection pool with regards to Azure Tokens. Any help would be awesome.

The AzureLogin class is my own, it just tracks the TTL of the current token and asks for a new one and saves it if required.

Expected behaviour:

I expected that calling connect with a new token, would update the token value within the Connection Pool / Manager.

Actual behaviour:

The token value does not change, and I get a Token Expired error.

Configuration:

var dbConfig = {
    authentication: {
        type: 'azure-active-directory-access-token',
        options: {
            token: azureLogin.connect().accessToken
        }
    },
    server: serverAddress,
    database: databaseName,
    options: {
        trustServerCertificate: false,
        encrypt: true,
        port: 1433
    }
};

sql.connect(dbConfig).then(pool => {
    return pool.request()
        .query('SELECT * FROM TABLE');
}).then(result => {
    res.redirect('/');
}).catch(err => {
    console.error(err);
});

Software versions

• NodeJS: 12
• node-mssql: 6.2.3
• tedious: 8.3.1
• SQL Server: Azure SQL Service

[cameronstubber]

Sorry, I should mention that I know my Azure Token is refreshed as I log the value of it.

[blaw2422]

can you show what kind of context this is being ran in? Does all of the code you posted run everytime you hit an endpoint?

[dhensby]

Are you closing the pool before you try to connect again? it won't use the new config when you call connect if the pool is already connected

[funkydev]

@cameronstubber You need to establish a new connection after your app will receive a new token because tedious does not support injecting token generator. You could also try another authentication method, like using Service Principal Secret.

This more about tedious issue instead of mssql. You can find the related issue here: tediousjs/tedious#1144

[richard457]

@cameronstubber You need to establish a new connection after your app will receive a new token because tedious does not support injecting token generator. You could also try another authentication method, like using Service Principal Secret.

This more about tedious issue instead of mssql. You can find the related issue here: tediousjs/tedious#1144

A code sample is much appreciated.