So you’re saying no applications should actually connect to the custom database, and only the Username-Password-Authentication database? When I do that, it definitely doesn’t grab users, just says wrong username/password. Though this is how the documentation is written.
If I actually turn that connection on, and the default one off, then it works.
Two connections:
Custom DB Settings, per documentation. Import Users to Auth0 is on, Disable signups is off.
Apps in custom connection turned off, per your comment, as well as no mention of doing it in documentation
The script templates needed some decent reworking as they don’t work as written when connecting to SQL (filed a support ticket for this one). Below are the scripts, both return fine during the tests.
//this example uses the "tedious" library
//more info here: http://pekim.github.io/tedious/index.html
const crypto = require('crypto');
const sqlserver = require('tedious');
const Connection = sqlserver.Connection;
const Request = sqlserver.Request;
const TYPES = sqlserver.TYPES;
const connection = new Connection({
userName: '[snip]',
password: '[snip]',
server: '[snip]',
options: {
database: '[snip]',
encrypt: true
}
});
const query = 'SELECT Id, Email, PasswordHash FROM [User] WHERE Email = @Email';
const delimiter = '*';
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));
});
// hash passwords using the old method
function generateSaltedPassword(password, salt) {
var sha512 = crypto.createHash("sha512");
sha512.update(salt+password, "utf8");
var result = sha512.digest("base64");
return salt + delimiter + result;
}
connection.on('connect', function (err) {
if (err) return callback(err);
const request = new Request(query, function (err, rowCount, rows) {
if (err || rowCount < 1) {
callback(err || new WrongUsernameOrPasswordError(email));
}
});
request.on('row', function(columns) {
var pwd = columns[2].value;
var salt = pwd.substr(0, pwd.indexOf(delimiter));
if (generateSaltedPassword(password, salt) !== pwd) {
return callback(new WrongUsernameOrPasswordError(email));
}
callback(null, {
user_id: columns[0].value,
email: columns[1].value
});
});
request.addParameter('Email', TYPES.VarChar, email);
connection.execSql(request);
});
}
function getByEmail(email, callback) {
const sqlserver = require('tedious@1.11.0');
const Connection = sqlserver.Connection;
const Request = sqlserver.Request;
const TYPES = sqlserver.TYPES;
const connection = new Connection({
userName: '[snip]',
password: '[snip]',
server: '[snip]',
options: {
database: '[snip]',
encrypt: true
}
connection.on('debug', function(text) {
// if you have connection issues, uncomment this to get more detailed info
//console.log(text);
}).on('errorMessage', function(text) {
// this will show any errors when connecting to the SQL database or with the SQL statements
console.log(JSON.stringify(text));
});
connection.on('connect', function(err) {
if (err) return callback(err);
var user = {};
const query =
'SELECT Id, Email from [User] ' +
'WHERE Email = @Email';
const getUserQuery = new Request(query, function (err, rowCount) {
if (err) return callback(err);
if (rowCount < 1) return callback();
callback(null, user);
});
getUserQuery.addParameter('Email', TYPES.VarChar, email);
getUserQuery.on('row', function (columns) {
user = {
user_id: columns[0].value,
email: columns[1].value
};
});
connection.execSql(getUserQuery);
});
}
The docs tell you what to do, but don’t tell you what not to do. Wouldn’t hurt to mention this.