MSSQL - Panic No Data - No LDAP

[maciek]

Hi,

I try to connect OTRS 3.0.10 on Ubuntu to mssql 2008 Express (Win 2008).
CheckDB says: It looks OK!

When try to log in as root@localhost I get following message:
Panic, user authenticated but no user data can be found in OTRS DB!! Perhaps the user is invalid.

otrs.log:

Code: Select all

[Tue Sep 27 08:31:48 2011][Notice][Kernel::System::Auth::DB::Auth] User: root@localhost authentication ok (REMOTE_ADDR: 172.20.120.105).
[Tue Sep 27 08:31:48 2011][Error][Kernel::System::User::UserLookup][747] No User ID found for 'root@localhost'!
[Tue Sep 27 08:31:48 2011][Error][Kernel::System::User::UserLookup][747] No User ID found for 'root@localhost'!
[Tue Sep 27 08:31:48 2011][Notice][Kernel::System::User::GetUserData] Panic! No UserData for user: 'root@localhost'!!!

apache2/error.log

Code: Select all

[Tue Sep 27 08:31:48 2011] -e: Use of uninitialized value $ID in concatenation (.) or string at /opt/otrs//Kernel/System/User.pm line 747.
ERROR: OTRS-CGI-10 Perl: 5.10.1 OS: linux Time: Tue Sep 27 08:31:48 2011

 Message: No UserID found for 'root@localhost'!

 Traceback (693):
   Module: Kernel::System::User::UserLookup (v1.114.2.2) Line: 747
   Module: Kernel::System::Auth::Auth (v1.50.2.1) Line: 206
   Module: Kernel::System::Web::InterfaceAgent::Run (v1.58.2.1) Line: 201
   Module: ModPerl::ROOT::ModPerl::Registry::opt_otrs_bin_cgi_2dbin_index_2epl::handler (unknown version) Line: 46
   Module: (eval) (v1.89.2.1) Line: 204
   Module: ModPerl::RegistryCooker::run (v1.89.2.1) Line: 204
   Module: ModPerl::RegistryCooker::default_handler (v1.89.2.1) Line: 170
   Module: ModPerl::Registry::handler (v1.99) Line: 31

Customer User interface works fine.

No LDAP

Only modyfication in Config.pm is:

Code: Select all

    $Self->{'DatabaseHost'} = '172.20.120.16';
    $Self->{'Database'} = 'OTRS';
    $Self->{'DatabaseUser'} = 'otrs2';
    $Self->{'DatabasePw'} = 'root';

    $Self->{'DatabaseDSN'} = "DBI:ODBC:driver=MS;Server=172.20.120.16;database=OTRS;uid=otrs2;pwd=root;";
    $Self->{'Database::Type'} = 'mssql';

Best regards
Maciek

[diginin]

Try adding a user via the command line and logging in then with the new admin user

bin/otrs.AddUser.pl -f first name -l last name -p a password -g admin -e user@example.com a username

[maciek]

Hello,

Thank you for reply.

I have tested it yesterday. User was added and I managed to log in.

Log from yesterday

Code: Select all

[Tue Sep 27 14:33:18 2011][Notice][Kernel::System::User::UserAdd] User: 'test' ID: '6' created successfully (1)!
[Tue Sep 27 14:33:19 2011][Notice][Kernel::System::User::SetPassword] User: 'test' changed password successfully!
[Tue Sep 27 14:37:27 2011][Notice][Kernel::System::Auth::DB::Auth] User: test authentication ok (REMOTE_ADDR: 172.20.120.105).
[Tue Sep 27 14:37:27 2011][Notice][Kernel::System::User::GetUserData] Panic! No UserData for user id: '6'!!!
[Tue Sep 27 14:37:27 2011][Error][Kernel::System::AuthSession::DB::GetSessionIDData][161] [unixODBC][FreeTDS][SQL Server]Unexpected EOF from the server (SQL-01000), SQL: 'SELECT session_value FROM  sessions WHERE session_id = ?'
[Tue Sep 27 14:37:27 2011][Error][Kernel::System::AuthSession::DB::RemoveSessionID][245] [unixODBC][FreeTDS][SQL Server]Communication link failure (SQL-08S01), SQL: 'DELETE FROM sessions WHERE session_id = ?'
[Tue Sep 27 14:37:27 2011][Error][Kernel::System::AuthSession::DB::CreateSessionID][226] [unixODBC][FreeTDS][SQL Server]Communication link failure (SQL-08S01), SQL: 'INSERT INTO sessions  (session_id, session_value) VALUES (?, ?)'
[Tue Sep 27 14:37:27 2011][Error][Kernel::System::AuthSession::DB::_SyncToStorage][353] [unixODBC][FreeTDS][SQL Server]Communication link failure (SQL-08S01), SQL: 'UPDATE sessions SET  session_value = ? WHERE session_id = ?'
[Tue Sep 27 14:37:38 2011][Notice][Kernel::System::Auth::DB::Auth] User: test authentication ok (REMOTE_ADDR: 172.20.120.105).
[Tue Sep 27 14:37:38 2011][Notice][Kernel::System::User::GetUserData] Panic! No UserData for user id: '6'!!!
[Tue Sep 27 14:37:44 2011][Notice][Kernel::System::Package::RepositoryGet] No such package Support-1.2.6!
[Tue Sep 27 14:37:52 2011][Notice][Kernel::System::Package::RepositoryGet] No such package iPhoneHandle-1.0.3!
[Tue Sep 27 14:38:04 2011][Error][Kernel::System::CacheInternal::Set][123] Need Value!
[Tue Sep 27 14:38:04 2011][Error][Kernel::System::Lock::LockLookup][229] No Lock/LockID for lock found!
[Tue Sep 27 14:38:04 2011][Error][Kernel::System::CacheInternal::Set][123] Need Value!
[Tue Sep 27 14:38:04 2011][Error][Kernel::System::Lock::LockLookup][229] No Lock/LockID for lock found!
[Tue Sep 27 14:38:04 2011][Error][Kernel::System::CacheInternal::Set][123] Need Value!
[Tue Sep 27 14:38:04 2011][Error][Kernel::System::Lock::LockLookup][229] No Lock/LockID for lock found!
[Tue Sep 27 14:38:04 2011][Error][Kernel::System::Queue::GetAllCustomQueues][421] [unixODBC][FreeTDS][SQL Server]Write to the server failed (SQL-08S01), SQL: 'SELECT queue_id FROM personal_queues WHERE user_id = ?'
[Tue Sep 27 14:38:04 2011][Error][Kernel::System::State::StateGetStatesByType][419] [unixODBC][FreeTDS][SQL Server]Communication link failure (SQL-08S01), SQL: 'SELECT ts.id, ts.name, tst.name   FROM ticket_state ts, ticket_state_type tst WHERE  tst.id = ts.type_id AND  tst.name IN ('open' ) AND  ts.valid_id IN ( 1 )'
[Tue Sep 27 14:38:04 2011][Error][Kernel::System::State::StateGetStatesByType][419] [unixODBC][FreeTDS][SQL Server]Communication link failure (SQL-08S01), SQL: 'SELECT ts.id, ts.name, tst.name   FROM ticket_state ts, ticket_state_type tst WHERE  tst.id = ts.type_id AND  tst.name IN ('open' ) AND  ts.valid_id IN ( 1 )'
[Tue Sep 27 14:38:04 2011][Error][Kernel::System::State::StateGetStatesByType][419] [unixODBC][FreeTDS][SQL Server]Communication link failure (SQL-08S01), SQL: 'SELECT ts.id, ts.name, tst.name   FROM ticket_state ts, ticket_state_type tst WHERE  tst.id = ts.type_id AND  tst.name IN ('open' ) AND  ts.valid_id IN ( 1 )'
[Tue Sep 27 14:38:04 2011][Error][Kernel::System::State::StateGetStatesByType][419] [unixODBC][FreeTDS][SQL Server]Communication link failure (SQL-08S01), SQL: 'SELECT ts.id, ts.name, tst.name   FROM ticket_state ts, ticket_state_type tst WHERE  tst.id = ts.type_id AND  tst.name IN ('open' ) AND  ts.valid_id IN ( 1 )'
[Tue Sep 27 14:38:04 2011][Error][Kernel::System::State::StateGetStatesByType][419] [unixODBC][FreeTDS][SQL Server]Communication link failure (SQL-08S01), SQL: 'SELECT ts.id, ts.name, tst.name   FROM ticket_state ts, ticket_state_type tst WHERE  tst.id = ts.type_id AND  tst.name IN ('open' ) AND  ts.valid_id IN ( 1 )'
[Tue Sep 27 14:38:04 2011][Error][Kernel::System::State::StateGetStatesByType][419] [unixODBC][FreeTDS][SQL Server]Communication link failure (SQL-08S01), SQL: 'SELECT ts.id, ts.name, tst.name   FROM ticket_state ts, ticket_state_type tst WHERE  tst.id = ts.type_id AND  tst.name IN ('open' ) AND  ts.valid_id IN ( 1 )'
[Tue Sep 27 14:38:07 2011][Notice][Kernel::System::Auth::DB::Auth] User: test authentication ok (REMOTE_ADDR: 172.20.120.105).
[Tue Sep 27 14:38:07 2011][Notice][Kernel::System::User::GetUserData] Panic! No UserData for user id: '6'!!!
[Tue Sep 27 14:38:07 2011][Notice][Kernel::System::AuthSession::DB::RemoveSessionID] Removed SessionID 1099403bed0b5803472ea04f780c02636b.
[Tue Sep 27 14:38:08 2011][Error][Kernel::System::Lock::LockLookup][215] [unixODBC][FreeTDS][SQL Server]Communication link failure (SQL-08S01), SQL: 'SELECT id FROM ticket_lock_type WHERE name = ?'
[Tue Sep 27 14:38:08 2011][Error][Kernel::System::Lock::LockLookup][215] [unixODBC][FreeTDS][SQL Server]Communication link failure (SQL-08S01), SQL: 'SELECT id FROM ticket_lock_type WHERE name = ?'
[Tue Sep 27 14:38:08 2011][Error][Kernel::System::Lock::LockLookup][215] [unixODBC][FreeTDS][SQL Server]Communication link failure (SQL-08S01), SQL: 'SELECT id FROM ticket_lock_type WHERE name = ?'
[Tue Sep 27 14:38:31 2011][Error][Kernel::System::AuthSession::DB::_SyncToStorage][353] [unixODBC][FreeTDS][SQL Server]Communication link failure (SQL-08S01), SQL: 'UPDATE sessions SET  session_value = ? WHERE session_id = ?'
[Tue Sep 27 14:38:45 2011][Error][Kernel::System::Valid::ValidList][114] [unixODBC][FreeTDS][SQL Server]Write to the server failed (SQL-08S01), SQL: 'SELECT id, name FROM valid'
[Tue Sep 27 14:38:45 2011][Error][Kernel::System::CustomerUser::DB::CustomerUserDataGet][448] Need User!
[Tue Sep 27 14:38:45 2011][Error][Kernel::System::AuthSession::DB::_SyncToStorage][353] [unixODBC][FreeTDS][SQL Server]Communication link failure (SQL-08S01), SQL: 'UPDATE sessions SET  session_value = ? WHERE session_id = ?'
[Tue Sep 27 14:40:13 2011][Notice][Kernel::System::AuthSession::DB::RemoveSessionID] Removed SessionID 1069835ed395571d00b2d80329bddbb878.
[Tue Sep 27 14:40:18 2011][Notice][Kernel::System::Auth::DB::Auth] User: test authentication ok (REMOTE_ADDR: 172.20.120.105).
[Tue Sep 27 14:40:18 2011][Notice][Kernel::System::User::GetUserData] Panic! No UserData for user id: '6'!!!
[Tue Sep 27 15:46:30 2011][Notice][Kernel::System::Auth::DB::Auth] User: test authentication ok (REMOTE_ADDR: 172.20.120.105).
[Tue Sep 27 15:46:31 2011][Notice][Kernel::System::User::GetUserData] Panic! No UserData for user id: '6'!!!
[Tue Sep 27 15:46:33 2011][Notice][Kernel::System::AuthSession::DB::RemoveSessionID] Removed SessionID 1024c08237c40e06a4a939c7cba219f1c4.
[Tue Sep 27 15:46:42 2011][Error][Kernel::System::Ticket::TicketSearch][5130] [unixODBC][FreeTDS][SQL Server]Communication link failure (SQL-08S01), SQL: 'SELECT COUNT(DISTINCT(st.id)) FROM ticket st, queue sq  WHERE sq.id = st.queue_id AND st.ticket_state_id IN ( 6 )  AND st.ticket_lock_id IN (2) AND st.user_id IN (6) AND st.until_time <= 1317131142'
[Tue Sep 27 15:46:44 2011][Error][Kernel::System::AuthSession::DB::_SyncToStorage][353] , SQL: 'UPDATE sessions SET  session_value = ? WHERE session_id = ?'

But today i was unable to log in

Code: Select all

[Wed Sep 28 12:00:37 2011][Notice][Kernel::System::Auth::DB::Auth] User: test authentication ok (REMOTE_ADDR: 172.20.120.105).
[Wed Sep 28 12:00:37 2011][Error][Kernel::System::User::UserLookup][747] No UserID found for  'test'!
[Wed Sep 28 12:00:37 2011][Error][Kernel::System::User::UserLookup][747] No UserID found for  'test'!
[Wed Sep 28 12:00:37 2011][Notice][Kernel::System::User::GetUserData] Panic! No UserData for user: 'test'!!!
[Wed Sep 28 12:02:43 2011][Notice][Kernel::System::User::UserAdd] User: 'test1' ID: '7' created successfully (1)!
[Wed Sep 28 12:02:44 2011][Notice][Kernel::System::User::SetPassword] User: 'test1' changed password successfully!
[Wed Sep 28 12:03:09 2011][Notice][Kernel::System::Auth::DB::Auth] User: test1 authentication ok (REMOTE_ADDR: 172.20.120.105).
[Wed Sep 28 12:03:09 2011][Error][Kernel::System::User::UserLookup][747] No UserID found for  'test1'!
[Wed Sep 28 12:03:09 2011][Error][Kernel::System::User::UserLookup][747] No UserID found for  'test1'!
[Wed Sep 28 12:03:09 2011][Notice][Kernel::System::User::GetUserData] Panic! No UserData for user: 'test1'!!!

Nothing was changed

Best regards
Maciek

[maciek]

Hello,

I have solved problem temporarily.
I have changed sql driver to commercial one - http://www.easysoft.com/ and it works.
But this driver is too expensive.

Can you tell me what driver you use with mssql connection?
And how have you configured it?


Best regards
Maciek