Invalid login, please try again (external MSSQL database)

Invalid login, please try again (external MSSQL database)

by Danny H -
Number of replies: 4

Attempting to set up external MSSQL datbase to authenticate users (Moodle 3.6.4 (build 20190513) on CentOS 7 with PHP 7.2.18), appears almost successful.

Site Administration | Plugins | Authentication | Manage Authentication
"Test Settings" for "External database" passes (I assume, the list of field names is in green).

In 'auth/db/auth.php', under function user_login, the ELSE (Normal case: use external db for both usernames and passwords.), the correct query is set by this line of code:

$rs = $authdb->Execute("SELECT {$this->config->fieldpass} FROM {$this->config->table} WHERE {$this->config->fielduser} = '".$this->ext_addslashes($extusername)."'");

and that correct query returns the fieldname followed by a space, the value, and another space.  Then, $extpassword is set correctly.

Back to 'login/index.php':
$frm->username = trim(core_text::strtolower($frm->username));
is set to the correct username

However, under
    if ($frm and isset($frm->username)) {
the line
    if ($user) {
$user is set to null.

So, all attempts to login get: "Invalid login, please try again".

Please provide some troubleshooting ideas.  Thanks!

Average of ratings: -
In reply to Danny H

Re: Invalid login, please try again (external MSSQL database)

by Danny H -
So, no one has any idea?
Average of ratings: -
In reply to Danny H

Re: Invalid login, please try again (external MSSQL database)

by Danny H -
I've narrowed it down. The error is happening in 'auth/db/auth.php' in function 'user_login' in the line: $fromdb = $fields[strtolower($this->config->fieldpass)]; '$this->config->fieldpass' is correctly set to [Social Security Number] However, $fromdb gets an empty value Even though $fields['social security number'] gets the correct value from the database. Someone please shed some light on why this is happening?
Average of ratings: -
In reply to Danny H

Re: Re: Invalid login, please try again (external MSSQL database)

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

Well done on narrowing it down, I think you're most of the way there. From what you've said: the column containing the password in the external database has spaces in the column name, i.e. "Social Security Number". So in the External Database settings in Moodle the Password Field setting is "[Social Security Number]", i.e. how SQL Server lets columns with spaces in their names be addressed.

The problem is that the query in user_login() then becomes:

SELECT [Social Security Number] FROM the_user_table WHERE username = 'the_username'

which works but the column name returned by the database, and thus becoming the key in $fields  is "Social Security Number" not "[Social Security Number]". $fromdb searches for the latter but only the former is present so no match and the login fails.

One might suggest that Moodle should handle the password column having a space in it but there's no standard for this between different databases and spaces in column names should be avoided. If you can't change the name of the column you could add a database view that aliased the column name (e.g. CREATE VIEW user_view AS SELECT username, [Social Security Number] AS password, … FROM the user_table).

There's a forum for authentication issues which may help get a response in future.

Social Security Numbers sound like a bad candidate for the password field in security terms.

Average of ratings: Useful (1)
In reply to Leon Stringer

Re: Re: Re: Invalid login, please try again (external MSSQL database)

by Danny H -
I created a MariaDatabase and imported data from the MSSQL database (and set up on cron to delete users and add again each day). Then used that MariaDB as the authentication database.
Average of ratings: -