tedious: Azure AD with accessToken Connection lost - read ECONNRESET

Question

I am working on an App that is authenticating user using Azure AD, extracting his accessToken and then using this token to connect to the Azure SQL server using below setting.

But unfortunately, I am getting ESOCKET "Connection lost - read ECONNRESET" right away,

const config = {
          server: 'db-server-name.database.windows.net',
          authentication: {
            type: 'azure-active-directory-access-token',
            options: {
              token: cloudAccessToken
            }
          },
          options: {
            debug: {
              packet: true,
              data: true,
              payload: true,
              token: false,
              log: true
            },
            database: 'DBNAME',
            encrypt: true,
            packetSize: 8192,
            keepAlive:true,
            requestTimeout: 300000,
            connectionTimeout: 32000,
          }
        };

        const connection = new Connection(config);

        connection.on('connect', function (err) {
          if (err) {
            console.log(err);
          }
          executeStatement();
        });

        connection.on('debug', function (text) {
          console.log(text);
        });

        connection.on('error', function (err) {
          console.error(err); // --> this gets trigger with error ESOCKET right away
        });

Answer 1

You must have missed something. The code is provided to you below. For specific operations, you can refer to Jim's answer.

And I have test it, it's works for me. I think it useful to you. For more details, you can refer to this post.

Connecting to Azure SQL using Service Principal in NodeJS, but token is rejected

var msrestAzure = require("ms-rest-azure");
var { Connection, Request } = require("tedious");

let clientSecret = "xxx";
let serverName = "xxx.database.windows.net";
let databaseName = "xxx";
let clientId = "xxx";
let tenantId = "xxx";

async function getConnect() {
  // way for Azure Service Principal
  let databaseCredentials = await msrestAzure.loginWithServicePrincipalSecret(
    clientId,
    clientSecret,
    tenantId,
    {
      tokenAudience: "https://database.windows.net/",
    },
  );

  // getting access token
  let databaseAccessToken = await new Promise((resolve, reject) => {
    databaseCredentials.getToken((err, results) => {
      if (err) return reject(err);
      resolve(results.accessToken);
    });
  });
  var config = {
    server: serverName,
    authentication: {
      type: "azure-active-directory-access-token",
      options: {
        token: databaseAccessToken,
      },
    },
    options: {
      debug: {
        packet: true,
        data: true,
        payload: true,
        token: false,
        log: true,
      },
      database: databaseName,
      encrypt: true,
    },
  };

  var connection = new Connection(config);
  connection.connect();
  connection.on("connect", function (err) {
    if (err) {
      console.log(err);
    }
    executeStatement(connection);
  });

  connection.on("debug", function (text) {
    console.log(text);
  });
}
function executeStatement(connection) {
  request = new Request("select * from CSVTest", function (err, rowCount) {
    if (err) {
      console.log(err);
    } else {
      console.log(rowCount + " rows");
    }

    connection.close();
  });

  request.on("row", function (columns) {
    columns.forEach(function (column) {
      if (column.value === null) {
        console.log("NULL");
      } else {
        console.log(column.value);
      }
    });
  });

  request.on("done", function (rowCount, more) {
    console.log(rowCount + " rows returned");
  });

  connection.execSql(request);
}

getConnect()
  .then(() => {
    console.log("run successfully");
  })
  .catch((err) => {
    console.log(err);
});