Custom Database Auto User Migration Simplest flow not working

[marcus2]

I’m encountering issues with Automatic User Migration using a custom database connection in Auth0. I’ve modified the Login/GetUser scripts under my custom database connection and tested them out. They work perfectly when tested manually using the “Try Connection” feature in the Auth0 dashboard. However, when I attempt to log in through my React application using the Auth0 Lock screen, the login fails with the error “Wrong Email or Password” despite using the correct credentials. The real-time webtask logs indicate the script run ok and return the profile, and Auth0 logs do not indicate any specific errors during these login attempts. The scripts work correctly when fetching and validating users from my custom database, when using “Try Connection.”, the user is not added to auth0 database, read in other posts that worked for some. I’ve ensured that “Import users to Auth0” is enabled and that the Username-Password-Authentication database is set as the default and enabled for custom database migration. Despite this, the login through the React application consistently fails but the logs shows the script run and return the correct profile. Any assistance or insights on what might be causing this issue or any additional configurations needed would be greatly appreciated!
I have only one database.

some screens:

my login script: (with logs as you can see)

function login(email, password, callback) {
  const crypto = require('crypto');
  const sqlserver = require('tedious@1.11.0');

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

  const connection = new Connection({
    userName: 'xxxx',
    password: 'xxxx',
    server: 'xxxxxx',
    options: {
      database: 'xxxxx',
      encrypt: true // for Windows Azure
    }
  });

  function fixedTimeComparison(a, b) {
    var mismatch = (a.length === b.length ? 0 : 1);
    if (mismatch) {
      b = a;
    }

    for (var i = 0, il = a.length; i < il; ++i) {
      const ac = a.charCodeAt(i);
      const bc = b.charCodeAt(i);
      mismatch += (ac === bc ? 0 : 1);
    }

    return (mismatch === 0);
  }

  function validatePassword(password, originalHash, callback) {
    if (!originalHash) {
      return callback(new Error('Original hash is null or undefined'));
    }

    const iterations = 1000;
    const hashBytes = Buffer.from(originalHash, 'base64');
    const salt = hashBytes.slice(1, 17); // 16 bytes for salt
    const hash = hashBytes.slice(17); // remaining bytes for hash

    crypto.pbkdf2(password, salt, iterations, hash.length, 'sha1', function(err, hashed) {
      if (err) return callback(err);

      const hashedBase64 = Buffer.from(hashed, 'binary').toString('base64');
      const isValid = fixedTimeComparison(hash.toString('base64'), hashedBase64);

      return callback(null, isValid);
    });
  }

  connection.on('debug', function(text) {
    console.log(text);
  }).on('errorMessage', function(text) {
    console.log(JSON.stringify(text));
  });

  connection.on('connect', function(err) {
    console.log('Connected to database');
    if (err) return callback(err);
    getMembershipUser(email, function(err, user) {
      if (err) {
        console.log('Error fetching user:', err);
        return callback(err);
      }
      if (!user || !user.profile) {
        console.log('User not found or missing profile');
        return callback(new WrongUsernameOrPasswordError(email));
      }
      console.log('User fetched:', user);
      validatePassword(password, user.password, function(err, isValid) {
        if (err) {
          console.log('Error validating password:', err);
          return callback(err);
        }
        if (!isValid) {
          console.log('Invalid password');
          return callback(new WrongUsernameOrPasswordError(email));
        }
        console.log('Password validated successfully');
        console.log('Returning profile:', user.profile);
        callback(null, user.profile);
      });
    });
  });

  function getMembershipUser(usernameOrEmail, done) {
    var user = null;
    const query =
      'SELECT webpages_Membership.UserId, Email, email as UserName, Password from webpages_Membership ' +
      'INNER JOIN UserProfile ON UserProfile.UserId = webpages_Membership.UserId ' +
      'WHERE UserProfile.email = @Username';

    const getMembershipQuery = new Request(query, function(err, rowCount) {
      if (err || rowCount < 1) return done(err);

      done(null, user);
    });

    getMembershipQuery.addParameter('Username', TYPES.VarChar, usernameOrEmail);

    getMembershipQuery.on('row', function(fields) {
      console.log('Query result fields:', fields);
      user = {
        profile: {
          user_id: fields.find(field => field.metadata.colName === 'UserId').value,
          nickname: fields.find(field => field.metadata.colName === 'UserName').value,
          email: fields.find(field => field.metadata.colName === 'Email').value,
        },
        password: fields.find(field => field.metadata.colName === 'Password').value
      };
    });

    connection.execSql(getMembershipQuery);
  }
}

[marcus2]

solved, the user_id was been returned as integer, needs to be string.