Auth0 Home Blog Docs

Custom Database with automatic migration -> resetting password for un-migrated users

auth0
password-reset
custom-database
user-migration

#1

Hi there,

we have set up a custom database and enabled the “import users to auth0 functionality”.

However we run into the issue of some users want to reset their password before they are migrated. The user can request a password change with no problems. He will receive an email and can set a new password. The logs show “success change password request”, followed by “success change password”. However the user isn’t created in auth0 and the password in the custom database hasn’t changed either.

Our login script retrieves a user like this:

{
  "user_id": "00001",
  "user_name": "fnord@user.com",
  "email": "fnord@user.com",
  "email_verified": "TRUE"
}

And thise one is from getUser:

{
  "user_id": "00001",
  "user_name": "fnord@user.com",
  "email": "fnord@user.com",
  "email_verified": "TRUE"
}

This is the log for the successful reset-request:

{
  "date": "2018-11-01T12:02:41.488Z",
  "type": "scp",
  "description": "You can now login to the application with the new password.",
  "connection": "migrationCon",
  "connection_id": "con_000001",
  "client_id": "UltraSecretClientId",
  "client_name": "ClientName",
  "ip": "111.111.111.111",
  "user_agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.100 Safari/537.36",
  "details": {
    "title": "Change Password",
    "email": "fnord@user.com",
    "body": {
      "_csrf": "scnrmfg",
      "ticket": "SomeTicketFoobar",
      "email": "fnord@user.com",
      "newPassword": "*****",
      "confirmNewPassword": "*****"
    },
    "query": {
      "email": "fnord@user.com",
      "username": null,
      "newPassword": null,
      "tenant": "my-tenant",
      "client_id": "UltraSecretClientId",
      "connection": "migrationCon",
      "resultUrl": "",
      "includeEmailInRedirect": true
    }
  },
  "user_id": "",
  "user_name": "fnord@user.com",
  "strategy": "auth0",
  "strategy_type": "database",
  "log_id": "00000000000000000000000000000000002"
}

And this is the following reset successful:

{
  "date": "2018-11-01T12:02:41.488Z",
  "type": "scp",
  "description": "You can now login to the application with the new password.",
  "connection": "migrationCon",
  "connection_id": "con_000001",
  "client_id": "UltraSecretClientId",
  "client_name": "ClientName",
  "ip": "111.111.111.111",
  "user_agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.100 Safari/537.36",
  "details": {
    "title": "Change Password",
    "email": "fnord@user.com",
    "body": {
      "_csrf": "scnrmfg",
      "ticket": "SomeTicket",
      "email": "fnord@user.com",
      "newPassword": "*****",
      "confirmNewPassword": "*****"
    },
    "query": {
      "email": "fnord@user.com",
      "username": null,
      "newPassword": null,
      "tenant": "my-tenant",
      "client_id": "UltraSecretClientId",
      "connection": "migrationCon",
      "resultUrl": "",
      "includeEmailInRedirect": true
    }
  },
  "user_id": "",
  "user_name": "fnord@user.com",
  "strategy": "auth0",
  "strategy_type": "database",
  "log_id": "00000000000000000000000000000000002"
}

Shouldn’t it be possible to migrate users on a password-reset from a custom database? Am I missing something?


#2

That’s correct, both login and password reset will trigger a user migration if you are set up for that. I suspect you’ll need to paste your custom DB scripts here so the experts can review them.

Auth0 will not change the user’s password in your custom DB (if you are importing your users into an Auth0 data store). When a user logs in or resets their password, those credentials are stored in a separate database that we (Auth0 clients) don’t have direct access to.

In your log entries above, there’s no need to hide the client ID. Client IDs are public (they form part of the URL).


#3

Here are the two scripts. Login:

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: 'DbUser',
    password: 'admin',
    server: 'db.database.org',
    options: {
      database: 'migration',
      encrypt: true,
      rowCollectionOnRequestCompletion: true
    }
  });

  var query = "update migrationTable set merged =CURRENT_TIMESTAMP " +
    "WHERE email = @email; " +
	  "SELECT Email, Email_Verified, Password, user_id " +
    "FROM migrationTable 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][3].value,
              email: rows[0][0].value,
              email_verified: rows[0][1].value
            });
          }
        });
      }
    });

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

And the one for getUser:

function getByEmail (name, 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: 'DbUser',
    password: 'admin',
    server: 'db.database.org',
    options: {
      database: 'migration',
      encrypt: true,
      rowCollectionOnRequestCompletion: true
    }
  });

  var query = "SELECT Email, Email_Verified, Password, user_id " +
    "FROM migrationTable 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(null,null);
      } else {
            callback(null, {
              user_id: rows[0][3].value,
              email: rows[0][0].value,
              email_verified: rows[0][1].value
            });
      }
    });

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

#5

Hey there @BlackDev, as we continue to investigate do you mind relaying what database you are currently using? Thank you in advance.


#6

Hey, thanks for the response :slight_smile:

We are using an MsSQL DB form Azure.