CODEWITHSUNDEEP
javascriptnode.jssql-serverazure-functionstedious
ochao
ochao

Reputation: 21

Connect javascript Azure function app with SQL server using Tedious and Managed Service Identity (MSI)

I am trying to connect my Azure function app to a managed SQL server using Tedious and Managed Service Identity (MSI). First I followed this links to build my functions : https://learn.microsoft.com/fr-fr/azure/azure-sql/database/connect-query-nodejs?tabs=windows https://medium.com/swlh/a-node-function-app-to-get-json-results-from-azure-synapse-analytics-e671dd6ae827

I added the name of the app function in the database. I developed the function using Tedious and I filled the auth value with "azure-active-directory-msi-app-service". Here is the sample of my code :

const { Connection, Request } = require('tedious');

module.exports = function (context, req) {

    // Create array to store the query results
    let result = [];
    let rowData = {};

    const config = {
        server: 'XXXXXX.public.XXXXXX.database.windows.net',
        Authentication: {
            type: 'azure-active-directory-msi-app-service',
        },
        options: {
            database: 'YYYYY',
            encrypt: true,
            port: 3342,
            connectTimeout: 120000
        }
    }

    // Create query to execute against the database
    const queryText = `SQL QUERY`;

    context.log('Query selected : ' + queryText);

    const connection = new Connection(config);

    // Create Request object
    const request = new Request(queryText, (err) => {
        connection.close();
        if (err) {
            // Error in executing query
            context.log.error(err);
            context.res.status = 500;
            context.res.body = 'Error executing the query';
        } else {
            context.res = {
                status: 200,
                isRaw: true,
                body: result,
                headers: {
                    'Content-Type': 'application/json'
                }
            }
        }
        // Inform Azure Function runtime that work is done
        context.done();
    });

    connection.connect();

    // Manipulate the results and create JSON
    request.on('row', function (columns) {
        rowData = {};
        columns.forEach(function (column) {
            rowData[column.metadata.colName] = column.value;
        });
        result.push(rowData);
    });

    // Attempt to connect and execute queries if connection goes through
    connection.on('connect', function (err) {
        if (err) {
            // Error in connecting
            context.log.error(err);
            context.res.status = 500;
            context.res.body = 'Error connecting to Azure SQL server';
            context.done();
        } else {
            // Connection succeeded
            context.log('Connection succeeded');
            connection.execSql(request);
        }
    });
}

I always get an authentification error :

2021-07-22T12:16:22.925 [Error] ConnectionError: Login failed for user ''.at ConnectionError
(C:\home\site\wwwroot\node_modules\tedious\lib\errors.js:13:12)at Parser.<anonymous> 
(C:\home\site\wwwroot\node_modules\tedious\lib\connection.js:1184:51)at Parser.emit (events.js:315:20)at Readable.<anonymous> 
(C:\home\site\wwwroot\node_modules\tedious\lib\token\token-stream-parser.js:27:14)at Readable.emit (events.js:315:20)at addChunk 
(C:\home\site\wwwroot\node_modules\readable-stream\lib\_stream_readable.js:298:12)at readableAddChunk 
(C:\home\site\wwwroot\node_modules\readable-stream\lib\_stream_readable.js:280:11)at Readable.push 
(C:\home\site\wwwroot\node_modules\readable-stream\lib\_stream_readable.js:241:10)at 
C:\home\site\wwwroot\node_modules\readable-stream\lib\internal\streams\from.js:49:29at Generator.next (<anonymous>) {code: 'ELOGIN'}

Can you please help me find where the problem comes from ? Is there an additional config I didn't do on SQL cloud ?

Thanks in advance.

Cheers,

Upvotes: 2

Views: 1669

Answers (1)

PerfectlyPanda
PerfectlyPanda

Reputation: 3511

According to the docs, the authentication property should not be capitalized. Since it's not finding the msi setting, it's defaulting back to user/password auth which you don't have set.

authentication: {
    type: 'azure-active-directory-msi-app-service',
}

Upvotes: 1

Related Questions