missing otrs.system_user in mysql db

[marguin]

i just installed OTRS 3.0.5 fresh, mysql db...trying to configure to authenticate against LDAP (not AD). I think i have all the specified stuff in the Config.pm, but i am getting the following error in my httpd error_log:

[Fri Feb 11 16:35:56 2011] -e: DBD::mysql::st execute failed: Table 'otrs.system_user' doesn't exist at /opt/otrs//Kernel/System/DB.pm line 553.
ERROR: OTRS-CGI-10 Perl: 5.8.8 OS: linux Time: Fri Feb 11 16:35:56 2011

Message: Table 'otrs.system_user' doesn't exist, SQL: 'SELECT id, login, title, first_name, last_name, pw, valid_id, create_time, change_time FROM system_user WHERE id = ?'

Traceback (12076):
Module: Kernel::System::User::GetUserData (v1.111) Line: 202
Module: Kernel::System::Auth::Auth (v1.49) Line: 220
Module: Kernel::System::Web::InterfaceAgent::Run (v1.58) Line: 201
Module: ModPerl::ROOT::ModPerl::Registry::opt_otrs_bin_cgi_2dbin_index_2epl::handler (unknown version) Line: 45
Module: (eval) (v1.89) Line: 204
Module: ModPerl::RegistryCooker::run (v1.89) Line: 204
Module: ModPerl::RegistryCooker::default_handler (v1.89) Line: 170
Module: ModPerl::Registry::handler (v1.99) Line: 31

[Fri Feb 11 16:35:56 2011] -e: DBD::mysql::st execute failed: Table 'otrs.system_user' doesn't exist at /opt/otrs//Kernel/System/DB.pm line 553.
ERROR: OTRS-CGI-10 Perl: 5.8.8 OS: linux Time: Fri Feb 11 16:35:56 2011

Message: Table 'otrs.system_user' doesn't exist, SQL: 'SELECT id, login, title, first_name, last_name, pw, valid_id, create_time, change_time FROM system_user WHERE id = ?'

Traceback (12076):
Module: Kernel::System::User::GetUserData (v1.111) Line: 202
Module: Kernel::System::User::SetPreferences (v1.111) Line: 937
Module: Kernel::System::Auth::Auth (v1.49) Line: 226
Module: Kernel::System::Web::InterfaceAgent::Run (v1.58) Line: 201
Module: ModPerl::ROOT::ModPerl::Registry::opt_otrs_bin_cgi_2dbin_index_2epl::handler (unknown version) Line: 45
Module: (eval) (v1.89) Line: 204
Module: ModPerl::RegistryCooker::run (v1.89) Line: 204
Module: ModPerl::RegistryCooker::default_handler (v1.89) Line: 170
Module: ModPerl::Registry::handler (v1.99) Line: 31


fresh install, could not imagine there could be a table missing. so i logged in to mysql to verify and infact i do not have this table. I have been looking for the schema for this table so that i can create it but cannot find any mention of creating this table in anything i have seen so far.

Is this table not supposed to be created automatically upon install of the app?

[jojo]

the table wa renamed in 2.3

so perhaps your Config.pm was not updated?

[marguin]

is it safe to assume it was renamed to 'user' ?

[kool_kid]

Yes its renamed to "users". I had the same issue and resolved by changing the table name to users.

Regards
Kk

[choenig]

Hi,

I have the same error message. There is no parameter or object named system_user either in Config.pm or DB.pm.
I've installed OTRS 3.1.10 (no update) on CentOS 5.1.

Perhaps someone can help? Config follows:



# ---------------------------------------------------- #
# ldap agent auth #
# ---------------------------------------------------- #

# This is an example configuration for an LDAP auth. backend.
# (take care that Net::LDAP is installed!)
$Self->{'AuthModule'} = 'Kernel::System::Auth::LDAP';
$Self->{'AuthModule::LDAP::Host'} = '192.168.000.000';
$Self->{'AuthModule::LDAP::BaseDN'} = 'dc=corporate,dc=|||||||||||||||||||||||||||||||,dc=de';
$Self->{'AuthModule::LDAP::UID'} = 'uid';
# Check if the user is allowed to auth in a posixGroup
# (e. g. user needs to be in a group xyz to use otrs)
$Self->{'AuthModule::LDAP::GroupDN'} = 'cn=OTRS_GRP,ou=Sicherheitsgruppen,dc=corporate,dc=|||||||||||||||||||||||||||||||,dc=de';
$Self->{'AuthModule::LDAP::AccessAttr'} = 'member';
# for ldap posixGroups objectclass (just uid)
$Self->{'AuthModule::LDAP::UserAttr'} = 'UID';
# for non ldap posixGroups objectclass (with full user dn)
#$Self->{'AuthModule::LDAP::UserAttr'} = 'DN';

# The following is valid but would only be necessary if the
# anonymous user do NOT have permission to read from the LDAP tree
$Self->{'AuthModule::LDAP::SearchUserDN'} = 'otrs-user';
$Self->{'AuthModule::LDAP::SearchUserPw'} = '---------------';

# in case you want to add always one filter to each ldap query, use
# this option. e. g. AlwaysFilter => '(mail=*)' or AlwaysFilter => '(objectclass=user)'
# $Self->{'AuthModule::LDAP::AlwaysFilter'} = '';

# in case you want to add a suffix to each login name, then
# you can use this option. e. g. user just want to use user but
# in your ldap directory exists user@domain.
#$Self->{'AuthModule::LDAP::UserSuffix'} = '@domain.com';

# Net::LDAP new params (if needed - for more info see perldoc Net::LDAP)
$Self->{'AuthModule::LDAP::Params'} = {
port => 389,
timeout => 120,
async => 0,
version => 3,
};

# UserSyncLDAPMap
# (map if agent should create/synced from LDAP to DB after login)
$Self->{UserSyncLDAPMap} = {
# DB -> LDAP
UserFirstname => 'givenName',
UserLastname => 'sn',
UserEmail => 'mail',
};
# Now sync data with OTRS DB
$Self->{'AuthSyncModule'} = 'Kernel::System::Auth::Sync::LDAP';
$Self->{'AuthSyncModule::LDAP::Host'} = '192.168.000.000';
$Self->{'AuthSyncModule::LDAP::BaseDN'} = 'dc=corporate,dc=|||||||||||||||||||||||||||||||,dc=de';
$Self->{'AuthSyncModule::LDAP::UID'} = 'uid';
$Self->{'AuthSyncModule::LDAP::SearchUserDN'} = 'otrs-user';
$Self->{'AuthSyncModule::LDAP::SearchUserPw'} = '---------------';


# UserSyncLDAPGroups
# (If "LDAP" was selected="selected" for AuthModule, you can specify # initial user groups for first login.)
$Self->{UserSyncLDAPGroups} = [
'agents','users',
];

# UserTable
$Self->{DatabaseUserTable} = 'users';
$Self->{DatabaseUserTableUserID} = 'id';
$Self->{DatabaseUserTableUserPW} = 'pw';
$Self->{DatabaseUserTableUser} = 'login';


# ---------------------------------------------------- #
# ldap customer auth #
# ---------------------------------------------------- #

#Enable LDAP authentication for Customers / Users
$Self->{'Customer::AuthModule'} = 'Kernel::System::CustomerAuth::LDAP';
$Self->{'Customer::AuthModule::LDAP::Host'} = '192.168.000.000';
$Self->{'Customer::AuthModule::LDAP::BaseDN'} = 'dc=corporate,dc=|||||||||||||||||||||||||||||||,dc=de';
$Self->{'Customer::AuthModule::LDAP::UID'} = 'sAMAccountName';
# Credentials to log into AD
$Self->{'AuthModule::LDAP::SearchUserDN'} = 'cn=otrs-ldap,cn=Users,dc=corporate,dc=|||||||||||||||||||||||||||||||,dc=de';
$Self->{'AuthModule::LDAP::SearchUserPw'} = '---------------';

#CustomerUser
#(customer user database backend and settings)
$Self->{CustomerUser} = {
Name => 'LDAP Datenquelle',
Module => 'Kernel::System::CustomerUser::LDAP',
Params => {
Host => '192.168.000.000',
BaseDN => 'cn=CRM,dc=corporate,dc=|||||||||||||||||||||||||||||||,dc=de',
SSCOPE => 'sub',
UserDN =>'cn=otrs-ldap',
UserPw => '---------------',
},
# customer unique id
CustomerKey => 'sAMAccountName',
# customer #
CustomerID => 'mail',
CustomerUserListFields => ['sAMAccountName', 'cn', 'mail'],
CustomerUserSearchFields => ['sAMAccountName', 'cn', 'mail'],
CustomerUserSearchPrefix => '',
CustomerUserSearchSuffix => '*',
CustomerUserSearchListLimit => 250,
CustomerUserPostMasterSearchFields => ['mail'],
CustomerUserNameFields => ['givenname', 'sn'],
Map => [
# note: Login, Email and CustomerID needed!
# var, frontend, storage, shown, required, storage-type
#[ 'UserSalutation', 'Title', 'title', 1, 0, 'var' ],
[ 'UserFirstname', 'Firstname', 'givenname', 1, 1, 'var' ],
[ 'UserLastname', 'Lastname', 'sn', 1, 1, 'var' ],
[ 'UserLogin', 'Login', 'sAMAccountName', 1, 1, 'var' ],
[ 'UserEmail', 'Email', 'mail', 1, 1, 'var' ],
[ 'UserCustomerID', 'CustomerID', 'mail', 0, 1, 'var' ],
[ 'UserPhone', 'Phone', 'telephonenumber', 1, 0, 'var' ],
#[ 'UserAddress', 'Address', 'postaladdress', 1, 0, 'var' ],
#[ 'UserComment', 'Comment', 'description', 1, 0, 'var' ],
],
};



Greetings
Christian

[jojo]

is this a fresh install or an upgrade?

[choenig]

Hi,

it's a fresh installation. I've tried a little bit in the meantime...
I created a new table system_users and copied the entries of users: CREATE TABLE system_user LIKE users; INSERT INTO system_user SELECT * FROM users;

Now I tried to log in and got the message: "Anmeldung fehlgeschlagen! Benutzername oder Passwort falsch.", but in the httpd.log there was NO Error.

Ok, seems my user has been added from LDAP to system_users, so I made a look, but there was no user entry in both tables system_users or users.

Getting bored ...

Greetings

[jojo]

there is no table like this. so it seems you are usig some old code. please try again with a fresh installation

[choenig]

Hi,

made a new installation of CentOS 6.3 and OTRS 3.1.10. and applied my LDAP-Configuration.
No previous error is written to httpd.log, but the logon failed with "username/password wrong"
and the logfile contains following:


[Mon Oct 01 10:49:08 2012] [notice] SELinux policy enabled; httpd running as context system_u:system_r:httpd_t:s0
[Mon Oct 01 10:49:08 2012] [notice] suEXEC mechanism enabled (wrapper: /usr/sbin/suexec)
[Mon Oct 01 10:49:09 2012] [notice] Digest: generating secret for digest authentication ...
[Mon Oct 01 10:49:09 2012] [notice] Digest: done
[Mon Oct 01 10:49:09 2012] [notice] Apache/2.2.15 (Unix) DAV/2 mod_perl/2.0.4 Perl/v5.10.1 configured -- resuming normal operations
ERROR: OTRS-CGI-99 Perl: 5.10.1 OS: linux Time: Mon Oct 1 10:49:43 2012

Message: No UserID found for '<LDAP UserID>'!

Traceback (1691):
Module: Kernel::System::User::UserLookup (v1.121) Line: 797
Module: Kernel::System::Auth::Auth (v1.56) Line: 244
Module: Kernel::System::Web::InterfaceAgent::Run (v1.64) Line: 204
Module: ModPerl::ROOT::ModPerl::Registry::opt_otrs_bin_cgi_2dbin_index_2epl::handler (unknown version) Line: 46
Module: (eval) (v1.90) Line: 204
Module: ModPerl::RegistryCooker::run (v1.90) Line: 204
Module: ModPerl::RegistryCooker::default_handler (v1.90) Line: 170
Module: ModPerl::Registry::handler (v1.99) Line: 31



Do I have to add the user manually into OTRS?
Why isn't the system synchronizing his database with LDAP?

best regards
Christian

[jojo]

if you did not added a Sync part to Config.pm you need to add the user manually first. Please have an look on the ldap examples in the forum

[choenig]

Hi,

I've added a synchronization part:

# UserSyncLDAPMap
# UserSyncLDAPMap (map if user should create/synced from LDAP to DB after login)
$Self->{UserSyncLDAPMap} = {
#DB -> LDAP
UserFirstname => 'givenName',
UserLastname => 'sn',
UserEmail => 'mail',
};

# Now sync data with OTRS DB
$Self->{'AuthSyncModule'} = 'Kernel::System::Auth::Sync::LDAP';
$Self->{'AuthSyncModule::LDAP::Host'} = '192.168.51.7';
$Self->{'AuthSyncModule::LDAP::BaseDN'} = 'cn=Administration,dc=corporate,dc=+++++++,dc=de';
$Self->{'AuthSyncModule::LDAP::UID'} = 'uid';
$Self->{'AuthSyncModule::LDAP::SearchUserDN'} = 'cn=########,cn=Users,dc=corporate,dc=+++++++,dc=de';
$Self->{'AuthSyncModule::LDAP::SearchUserPw'} = '++++++;


# UserSyncLDAPGroups(If "LDAP" was selected="selected" for AuthModule, you can specify # initial user groups for first login.)
$Self->{UserSyncLDAPGroups} = [
'users',
];

# UserTable
$Self->{DatabaseUserTable} = 'users';
$Self->{DatabaseUserTableUserID} = 'id';
$Self->{DatabaseUserTableUserPW} = 'pw';
$Self->{DatabaseUserTableUser} = 'login';

Now, LDAP finds the user, but the synchronization fails:

Oct 1 15:30:55 CentOS-OTRS OTRS-CGI-99[2088]: [Notice][Kernel::System::Auth::LDAP::Auth] User: +++++++ (CN=+++++++++++,OU=Administration,DC=corporate,DC=+++++++,DC=de) authentication ok (REMOTE_ADDR: 192.168.1.46).
Oct 1 15:30:55 CentOS-OTRS OTRS-CGI-99[2088]: [Error][Kernel::System::Auth::Sync::LDAP::Sync][Line:178]: Search failed! (cn=Administration,dc=corporate,dc=+++++++,dc=de) filter='(sAMAccountname=++++++)' 0000208D: NameErr: DSID-0310020A, problem 2001 (NO_OBJECT), data 0, best match of:#012#011'DC=corporate,DC=+++++++,DC=de'#012#000

Any ideas?

greetings

[jojo]

this does not look like a 3.1. sync part. Have a look in Defaults.pm and copy whole block including Bind user etc

[choenig]

ok, so far. Copied parts from default.pm and added local informations:

# --------------------------------------------------- #
# authentication sync settings #
# (enable agent data sync. after succsessful #
# authentication) #
# --------------------------------------------------- #
# This is an example configuration for an LDAP auth sync. backend.
# (take care that Net::LDAP is installed!)
$Self->{AuthSyncModule} = 'Kernel::System::Auth::Sync::LDAP';
$Self->{'AuthSyncModule::LDAP::Host'} = '192.168.000.000';
$Self->{'AuthSyncModule::LDAP::BaseDN'} = 'cn=Administration,dc=corporate,dc=+++++++,dc=de';
$Self->{'AuthSyncModule::LDAP::UID'} = 'uid';

# The following is valid but would only be necessary if the
# anonymous user do NOT have permission to read from the LDAP tree
$Self->{'AuthSyncModule::LDAP::SearchUserDN'} = 'cn=+++++++,cn=Users,dc=corporate,dc=+++++++,dc=de';
$Self->{'AuthSyncModule::LDAP::SearchUserPw'} = '+++++++;

# UserSyncLDAPGroups(If "LDAP" was selected="selected" for AuthModule, you can specify # initial user groups for first login.)
$Self->{UserSyncLDAPGroups} = [
'users',
];

# in case you want to add always one filter to each ldap query, use
# this option. e. g. AlwaysFilter => '(mail=*)' or AlwaysFilter => '(objectclass=user)'
# $Self->{'AuthSyncModule::LDAP::AlwaysFilter'} = '';

# AuthSyncModule::LDAP::UserSyncMap
# (map if agent should create/synced from LDAP to DB after successful login)
$Self->{'AuthSyncModule::LDAP::UserSyncMap'} = {
# DB -> LDAP
UserFirstname => 'givenName',
UserLastname => 'sn',
UserEmail => 'mail',
};


Website says: Panic, user authenticated but no user data can be found in OTRS DB!! Perhaps the user is invalid. and messages.log
contains the synchronisation error as postet.

Greetings

[jojo]

$Self->{'AuthSyncModule::LDAP::UID'} = 'uid'; should be samaccount instead of uid

[choenig]

tried with both, sAMAccountname and UID.
Same error like every time...

[jojo]

please show full Config.pm

[choenig]

see attachment below...

Thanks

[choenig]

Hi,

problem is solved - following parameters were missing:

$Self->{'AuthSyncModule::LDAP::Params'} = {
port => 389,
timeout => 120,
async => 0,
version => 3,
};

Seems that our W2008R2 domain needs this to work.