Azure: Custom database 401 unauthorized

Hey Guys,

I’m trying to make a custom database login action script. I’m able to connect to my database but i’m getting an 401 unauthorized.

My script looks like this.

function login(email, password, callback) {
  //this example uses the "tedious" library
  //more info here: http://pekim.github.io/tedious/index.html
  var Connection = require('tedious@1.11.0').Connection;
  var Request = require('tedious@1.11.0').Request;
  var TYPES = require('tedious@1.11.0').TYPES;

  var connection = new Connection({
userName: 'myusername',
password: 'mypassword',
server: 'myazuredatabase.database.windows.net',
options: {
  database: 'dbname',
  encrypt: true,
  rowCollectionOnRequestCompletion: true
}
  });

  var query = "SELECT Id, Email, Password " +
"FROM dbo.Users WHERE Email = @Email";

  connection.on('debug', function (text) {
// Uncomment next line in order to enable debugging messages
// console.log(text);
  }).on('errorMessage', function (text) {
console.log(JSON.stringify(text, null, 2));
return callback(text);
  }).on('infoMessage', function (text) {
// Uncomment next line in order to enable information messages
// console.log(JSON.stringify(text, null, 2));
  });

  connection.on('connect', function (err) {
if (err) { return callback(err); }

var request = new Request(query, function (err, rowCount, rows) {
  if (err) {
    callback(new Error(err));
  } else if (rowCount < 1) {
    callback(new WrongUsernameOrPasswordError(email));
  } else {
    bcrypt.compare(password, rows[0][2].value, function (err, isValid) {
      if (err) { callback(new Error(err)); }
      else if (!isValid) { callback(new WrongUsernameOrPasswordError(email)); }
      else {
        callback(null, {
          user_id: rows[0][0].value,
          email: rows[0][1].value
        });
      }
    });
  }
});

request.addParameter('Email', TYPES.VarChar, email);
connection.execSql(request);
  });
}

Any idea what i’m doing wrong? My create script works fine (cause I see the users appear in my database)

function create(user, callback) {
  //this example uses the "tedious" library
  //more info here: http://pekim.github.io/tedious/index.html
  const bcrypt = require('bcrypt');
  const sqlserver = require('tedious@1.11.0');

  const Connection = sqlserver.Connection;
  const Request = sqlserver.Request;
  const TYPES = sqlserver.TYPES;

  const connection = new Connection({
    userName:  'myusername',
    password:  'mypassword',
    server:    'myazuredatabase.database.windows.net',
    options:  {
      database: 'mydb',
      encrypt: true
    }
  });

  const query = 'INSERT INTO dbo.Users(Email,Password) VALUES(@Email,@Password)';
  
  connection.on('debug', function(text) {
    console.log(text);
  }).on('errorMessage', function(text) {
    console.log(JSON.stringify(text, null, 2));
  }).on('infoMessage', function(text) {
    console.log(JSON.stringify(text, null, 2));
  });

  connection.on('connect', function (err) {
    if (err) return callback(err);

    const request = new Request(query, function (err, rows) {
      if (err) return callback(err);
      // console.log('rows: ' + rows);
      callback(null);
    });

    bcrypt.hash(user.password, 10, function(err, hash) {
      if (err) return callback(err);
      request.addParameter('Email', TYPES.VarChar, user.email);
      request.addParameter('Password', TYPES.VarChar, hash);
      connection.execSql(request);
    });
  });
}

I’m getting the following output in my console (using the real time webtask logs):

10:10:09 AM:

 State change: Connecting -&gt; SentPrelogin

10:10:10 AM:

 State change: SentPrelogin -&gt; SentTLSSSLNegotiation

10:10:10 AM:

 (node:7) [DEP0064] DeprecationWarning: tls.createSecurePair() is deprecated. Please use tls.Socket instead.

10:10:10 AM:

 TLS negotiated (ECDHE-RSA-AES256-GCM-SHA384, TLSv1/SSLv3)

10:10:10 AM:

 State change: SentTLSSSLNegotiation -&gt; SentLogin7WithStandardLogin

10:10:10 AM:

 { "number": 5701, "state": 2, "class": 0, "message": "Changed database context to 'dbname'.", "serverName": "servername", "procName": "", "lineNumber": 1, "name": "INFO", "event": "infoMessage" }

10:10:10 AM:

 { "number": 5703, "state": 1, "class": 0, "message": "Changed language setting to us_english.", "serverName": "servername", "procName": "", "lineNumber": 1, "name": "INFO", "event": "infoMessage" }

10:10:10 AM:

 Packet size changed from 4096 to 4096

10:10:10 AM:

 State change: SentLogin7WithStandardLogin -&gt; LoggedInSendingInitialSql

10:10:10 AM:

 { "number": 5703, "state": 1, "class": 0, "message": "Changed language setting to us_english.", "serverName": "servername", "procName": "", "lineNumber": 12, "name": "INFO", "event": "infoMessage" }

10:10:10 AM:

 State change: LoggedInSendingInitialSql -&gt; LoggedIn

10:10:10 AM:

 State change: LoggedIn -&gt; SentClientRequest

10:10:11 AM:

 State change: SentClientRequest -&gt; LoggedIn

10:10:11 AM:

 finished webtask request 1549444209738.322583 with HTTP 200 in 1270ms

Hey there!

Sorry for such huge delay in response! We’re doing our best in providing you with best developer support experience out there, but sometimes our bandwidth is not enough comparing to the number of incoming questions.

Wanted to reach out to know if you still require further assistance?