Moodle 3.11配置SQL Server外部认证异常求助：连接成功但无法同步用户

Hey there, let's work through this SQL Server external auth problem you're hitting in Moodle 3.11. It's frustrating when the database connects but authentication fails—here are some targeted checks to fix this:

• Verify Field Mapping & User Lookup Query
  First, double-check the core configuration in Site Administration > Plugins > Authentication > Database. Ensure:

  • The User lookup query correctly uses the :username placeholder to fetch the matching user from your SQL Server table. For example:
    SELECT username, password FROM your_user_table WHERE username = :username
    If this query returns no results for a valid username, Moodle will throw the result:Null error. Also confirm your table/column names match exactly (SQL Server is case-sensitive depending on collation settings).
  • The Password field is set to the correct column in your SQL Server table that stores user passwords.
  • The Type of password hash matches how passwords are stored in SQL Server. If you're using plain text (only for testing!), select "Plain text"; if you're using MD5, SHA-256, etc., pick the corresponding option. Mismatched hash types will cause authentication to fail even if the password is correct.

• Enable Debugging for Detailed Errors
  Turn on Moodle's developer debugging to get more context about the input array:false message:

  1. Go to Site Administration > Development > Debugging
  2. Set Debug messages to "DEVELOPER: extra Moodle debug messages for developers"
  3. Save changes and attempt to log in again. You'll likely see more specific errors (like invalid query syntax, missing columns, or permission issues) that aren't shown in the default UI.

• Revert Custom auth.php Changes
  You mentioned modifying ./rootfolder/auth/db/auth.php to read user records—custom changes here might have broken the authentication flow. Try restoring the original version of this file (from a fresh Moodle 3.11 download) and test authentication again. If it works, you can reintroduce your user list logic carefully without altering the core authentication functions.

• Test with a Known Plain Text Password
  To rule out password hash issues, create a test user in your SQL Server table with a plain text password, then set the Type of password hash in Moodle to "Plain text". Attempt to log in with this test user—if it works, the problem is definitely with the hash type configuration.

• Check SQL Server Collation & Case Sensitivity
  If your SQL Server uses a case-sensitive collation, a username like JohnDoe in SQL Server won't match johndoe entered in Moodle's login form. You can adjust your lookup query to handle this, e.g.:
  SELECT username, password FROM your_user_table WHERE LOWER(username) = LOWER(:username)
  Or change the SQL Server table's collation to a case-insensitive one (like SQL_Latin1_General_CP1_CI_AS).

内容的提问来源于stack exchange，提问作者Ange Trainee