I am having trouble getting my NodeJS application to connect to an Azure SQL Database using the Service Principal. However, when I try to do the same thing with a C# Snippet, it works fine. What I have noticed is that the tokens returned by the auth on both languages is a bit different, and if I take the correct token from C# and hard code it into NodeJS, my SQL Connection now succeeds.
I am first using ms-rest-azure to perform my authentication, and providing my clientId, tenantId and clientSecret. This returns back a valid credential, from which I am extracting the accessToken.
Then, I am using tedious to try to connect to the Azure SQL at *.database.windows.net and providing the accessToken value in the configuration.
I just get Login failed for user '<token-identified principal>'
What am I doing wrong in the ms-rest-azure login to give me a token that is rejected by Azure SQL? One thing I saw is that the working token has an audience of database.windows.net, where-as the one from ms-rest-azure is management.core.windows.net.
I've been stuck for a few days, if anybody has any clues here that would be awesome. The documentation on ms-rest-azure seems pretty non-existent and just gives you the runaround to Azure Sales pages.
const msRestAzure = require('ms-rest-azure');
const { reject } = require('async');
let clientSecret = "xxx";
let serverName = "xxx.database.windows.net";
let databaseName = "xxx";
let clientId = "xxx";
let tenantId = "xxx";
azureCredentials = msRestAzure.loginWithServicePrincipalSecret(clientId, clientSecret, tenantId, function(err, credentials) {
if (err) return console.log(err);
credentials.getToken((err, results) => {
if(err) return reject(err);
let accessToken = results.accessToken;
var Connection = require('tedious').Connection;
var Request = require('tedious').Request;
var config = {
server: serverName,
authentication: {
type: 'azure-active-directory-access-token',
options: {
token: accessToken
}
}
,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.on('debug', function(text) {
console.log(text);
}
);
function executeStatement() {
request = new Request("select * from Text", 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);
}
});
})