Auth0 Home Blog Docs

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