2

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,

1 Answer 1

1

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',
}
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.