Integrating MSSQL with OTRS 3.1.4

[kool_kid]

Hi,

I have installed a working MSSQL Server 2008. My OTRS front end is running on Centos 5. I have installed Freetds to work as SQL Driver. The Connectivity to Database is proper and when I run CheckDB.pl it says

Code: Select all

Trying to connect to database
DSN: DBI:ODBC:MSSQL-OTRS
DatabaseUser: otrs

It looks Ok!

I have also integrated otrs with Active Directory. When I login with a proper account my login is authenticated but I dont get a session initiated rather the login screen comes back. I checked the messages.log and this is what I find.

Code: Select all

[Notice][Kernel::System::Auth::LDAP::Auth] User: Tom.Hanks (CN=Tom Hanks,OU=Department,OU=Local Office,DC=Red,DC=com) authentication ok (REMOTE_ADDR: 10.10.1.1).
Aug  8 12:01:06 Server1 OTRS-CGI-19[2881]: [Notice][Kernel::System::User::UserUpdate] User: 'Tom.Hanks' updated successfully (1)!
Aug  8 12:01:06 Server1 OTRS-CGI-19[2881]: [Notice][Kernel::System::User::SetPassword] User: 'Tom.Hanks' changed password successfully!
Aug  8 12:01:06 Server1 OTRS-CGI-19[2881]: [Error][Kernel::System::Group::GroupGroupMemberList][Line:850]: [unixODBC][FreeTDS][SQL Server]Read from SQL server failed. (SQL-08S01), SQL: 'SELECT g.id, g.name, gu.permission_key, gu.permission_value, gu.user_id         FROM groups g, group_user gu, users u         WHERE g.valid_id IN (1)             AND g.id = gu.group_id             AND gu.permission_value = 1             AND gu.permission_key IN ( 'ro','rw' )             AND gu.user_id = u.id             AND u.valid_id IN ( 1 )             AND gu.user_id = 3'
Aug  8 12:01:06 Server1 OTRS-CGI-19[2881]: [Error][Kernel::System::AuthSession::DB::GetAllSessionIDs][Line:288]:     Unable to fetch information about the error, SQL: 'SELECT session_id FROM sessions'
Aug  8 12:01:06 Server1 OTRS-CGI-19[2881]: [Error][Kernel::System::AuthSession::DB::CreateSessionID][Line:226]:     Unable to fetch information about the error, SQL: 'INSERT INTO sessions  (session_id, session_value) VALUES (?, ?)'

I don't know why that error of "Read from SQL server failed" as this user is db owner. I can create and delete tables in Database when logged in through SQL Management Studio.

[CSL]

What happens if you try to run that command from SQL management studio?

SELECT g.id, g.name, gu.permission_key, gu.permission_value, gu.user_id FROM groups g, group_user gu, users u WHERE g.valid_id IN (1) AND g.id = gu.group_id AND gu.permission_value = 1 AND gu.permission_key IN ( 'ro','rw' ) AND gu.user_id = u.id AND u.valid_id IN ( 1 ) AND gu.user_id = 3

Is the OTRS schema present and correct within the database?

[kool_kid]

I ditched frontend on linux and backend on windows and moved to IIS + MSSQL which works very fine in few mins (if you follow right config doc ).

Thanks for reply anyways.