Invalid datetime format for column 'auth_id' ?!

Help
1

Hi!

After installing LibreNMS I am really happy about this great software! For me the next step is to try to change the authentication to OpenLDAP. In the librenms-DB, my users table has already the following items (from working SQL authentication):

select * from users;
±--------±----------±--------±---------±-------------------------------------------------------------±---------±-------------------±------±------±------------------±--------------------±--------------------±-------------------------------------------------------------+
| user_id | auth_type | auth_id | username | password | realname | email | descr | level | can_modify_passwd | created_at | updated_at | remember_token |
±--------±----------±--------±---------±-------------------------------------------------------------±---------±-------------------±------±------±------------------±--------------------±--------------------±-------------------------------------------------------------+
| 1 | mysql | 1 | librenms | $123456… | | [email protected] | | 10 | 1 | 2018-12-30 20:49:11 | 2019-01-05 14:54:40 | 123456… |
| 2 | mysql | 2 | Gast | 123456… | | | | 1 | 1 | 2019-01-05 18:38:16 | 2019-01-05 19:29:43 | 123456… |
±--------±----------±--------±---------±-------------------------------------------------------------±---------±-------------------±------±------±------------------±--------------------±--------------------±-------------------------------------------------------------+

Now with LDAP in WebUI, the following error occures:

Unhandled MySQL Error [22007]

SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: ‘****’ for column ‘auth_id’ at row 1

Check your log for more details. (librenms.log)

librenms.log:

Next Illuminate\Database\QueryException: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: ‘*****’ for column
‘auth_id’ at row 1 (SQL: insert into users (descr, realname, email, can_modify_passwd, username, level, auth_type, auth_id, updated_at, created_at) values (, , , 0, *****, 10, ldap, *****, 2019-01-06 12:48:00, 2019-01-06 12:48:00)) in /opt
/librenms/vendor/laravel/framework/src/Illuminate/Database/Connection.php:647

./validate.php

Component Version
LibreNMS 1.47
DB Schema 275
PHP 7.2.12
MySQL 5.5.60-MariaDB
RRDTool 1.4.8
SNMP NET-SNMP 5.7.2

====================================

[OK] Composer Version: 1.8.0
[OK] Dependencies up-to-date.
[OK] Database connection successful
[OK] Database schema correct

auth_test.php -l shows all users:
/opt/librenms/scripts/auth_test.php -l
Authentication Method: ldap
Users: (0), 123 (0), 456 (0), 789 (0), …

Version 1.47 - Sun Dec 30 2018 15:29:16 GMT+0100
DB Schema #275
Web Server nginx/1.12.2
PHP 7.2.12
MySQL 5.5.60-MariaDB
RRDtool 1.4.8

Downloaded as virtual Image.

Has anyone an idea why LibreNMS wants to insert a timestamp in column ‘auth_id’ by LDAP auth.?

Best Regards

Christopher

2

Seems you obscured some data in the SQL query, which is fine, but do It determining what is there. For example, the auth id field, what kind of is trying to insert? An string?

3

The setting auth_ldap_uid_attribute needs to be a numeric value. What have you set it to?

4

That´s it.
In my LDAP-scheme $config[‘auth_ldap_uid_attribute’] is ‘uid’: a string. But if I place now $config[‘auth_ldap_uid_attribute’] = ‘uidNumber’; as it would be right; I get “Invalid syntax” in WebUI; no error in log.

/opt/librenms/scripts/auth_test.php -l shows now the right numeric value behind the right username like 123 (right number ):
Authentication Method: ldap
Users: (0), 123 (710), 456 (565), 789 (…

Perhaps it has something to do with the group …
uid is the username like 123
uidNumber is 710

$config[‘auth_ldap_uid_attribute’] = ‘uidNumber’;
[…]
$config[‘auth_ldap_prefix’] = ‘uid=’;
$config[‘auth_ldap_suffix’] = ‘,ou=Group1,ou=Group2,dc=DC,…’;
$config[‘auth_ldap_groups’][‘Group1’][‘level’] = 10;
$config[‘auth_ldap_groupmemberattr’] = ‘member’;
$config[‘auth_ldap_emailattr’] = ‘mail’;
$config[‘auth_ldap_attr’][‘uid’] = ‘uid’;

Sorry, I´m new in this.

Regards

5

Got it.
$config[‘auth_ldap_groupmemberattr’] should be ‘memberuid’;

Authenticating works now.

Thank you for the hints