Hallo,
leider komme ich nach stundenlangem Suchen bei meinem Problem nicht weiter und hoffe hier auf einen Tip. Programmierkenntnisse sind leider bei mir nur mangelhaft vorhanden.
Ich habe mir die OTRS Appliance 4 Patch Level 7 installiert (Debian) und möchte aus einer fernen MSSQL DB (im LAN) die Kundendaten auswählen können um die Stammdaten nicht doppelt erfassen zu müssen. Dabei geht es weniger darum, das sich der Kunde in otrs einloggen kann, sondern um keine doppelte Datenhaltung zu haben.
Dazu habe ich FreeTDS installiert und scheinbar auch erfolgreich getestet.
root@otrs:~# isql -v odbcname mssqlnutzer mssqlpasswort
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
Läuft scheinbar. Nun habe ich entsprechend verschiedener Howtos meine Config.pm angepasst.
#########################################
package Kernel::Config;
use strict;
use warnings;
use utf8;
sub Load {
my $Self = shift;
# ---------------------------------------------------- #
# database settings #
# ---------------------------------------------------- #
# The database host
$Self->{DatabaseHost} = '127.0.0.1';
# The database name
$Self->{Database} = 'otrs';
# The database user
$Self->{DatabaseUser} = 'otrs';
# The password of database user. You also can use bin/otrs.CryptPassword.pl
# for crypted passwords
$Self->{DatabasePw} = 'xxxxxxxxxxxxxxxx';
$Self->{SecureMode}=1;
$Self->{"Package::Timeout"}=120;
$Self->{'SystemID'}=52;
$Self->{'SessionName'}='OTRSAgentInterface';
### MARKER ###
# The database DSN for MySQL ==> more: "perldoc DBD::mysql"
$Self->{DatabaseDSN} = "DBI:Pg:dbname=$Self->{Database};host=$Self->{DatabaseHost};";
# The database DSN for PostgreSQL ==> more: "perldoc DBD::Pg"
# if you want to use a local socket connection
# $Self->{DatabaseDSN} = "DBI:Pg:dbname=$Self->{Database};";
# if you want to use a TCP/IP connection
# $Self->{DatabaseDSN} = "DBI:Pg:dbname=$Self->{Database};host=$Self->{DatabaseHost};";
# The database DSN for Microsoft SQL Server - only supported if OTRS is
# installed on Windows as well
# $Self->{DatabaseDSN} = "DBI:ODBC:driver={SQL Server};Database=$Self->{Database};Server=$Self->{DatabaseHost},1433";
# The database DSN for Oracle ==> more: "perldoc DBD::oracle"
# $Self->{DatabaseDSN} = "DBI:Oracle://$Self->{DatabaseHost}:1521/$Self->{Database}";
#
# $ENV{ORACLE_HOME} = '/path/to/your/oracle';
# $ENV{NLS_DATE_FORMAT} = 'YYYY-MM-DD HH24:MI:SS';
# $ENV{NLS_LANG} = 'AMERICAN_AMERICA.AL32UTF8';
# ---------------------------------------------------- #
# fs root directory
# ---------------------------------------------------- #
$Self->{Home} = '/opt/otrs';
# ---------------------------------------------------- #
# insert your own config settings "here" #
# config settings taken from Kernel/Config/Defaults.pm #
# ---------------------------------------------------- #
# $Self->{SessionUseCookie} = 0;
# $Self->{CheckMXRecord} = 0;
# ---------------------------------------------------- #
# ---------------------------------------------------- #
# data inserted by installer #
# ---------------------------------------------------- #
# $DIBI$
# ---------------------------------------------------- #
# CustomerUser (customer database backend and settings)
$Self->{CustomerUser1} = {
Name => 'csconcobradb',
Module => 'Kernel::System::CustomerUser::DB',
Params => {
# if you want to use an external database, add the required settings
DSN => 'DBI:odbc:odbcname',
Type => 'mssql', # only for ODBC connections
# DSN => 'DBI:mysql:database=customerdb;host=customerdbhost',
User => 'mssqlnutzer',
Password => 'mssqlpasswort',
Table => 'ADDRESSES',
# if your frontend is unicode and the charset of your
# customer database server is iso-8859-1, use these options.
SourceCharset => 'iso-8859-1',
DestCharset => 'utf-8',
# CaseSensitive will control if the SQL statements need LOWER()
# function calls to work case insensitively. Setting this to
# 1 will improve performance dramatically on large databases.
CaseSensitive => 0,
},
# customer unique id
CustomerKey => 'login',
# customer #
CustomerID => 'COMPANY1',
# CustomerValid => 'valid_id',
CustomerUserListFields => ['FIRSTNAME0', 'LASTNAME0', 'COMPANY1'],
CustomerUserSearchFields => ['FIRSTNAME0', 'LASTNAME0', 'COMPANY1'],
CustomerUserSearchPrefix => '*',
CustomerUserSearchSuffix => '*',
CustomerUserSearchListLimit => 250,
CustomerUserPostMasterSearchFields => ['EMAIL0'],
CustomerUserNameFields => ['FIRSTNAME0','LASTNAME0','COMPANY1'],
CustomerUserEmailUniqCheck => 1,
# # show not own tickets in customer panel, CompanyTickets
# CustomerUserExcludePrimaryCustomerID => 0,
# # generate auto logins
AutoLoginCreation => 0,
# AutoLoginCreationPrefix => 'auto',
# # admin can change customer preferences
AdminSetPreferences => 0,
# # cache time to live in sec. - cache any database queries
# CacheTTL => 0,
# # just a read only source
ReadOnly => 0,
Map => [
# note: Login, Email and CustomerID needed!
# var, frontend, storage, shown (1=always,2=lite), required, storage-type, http-link, readonly, http-link-target
[ 'UserTitle', 'Titel', 'TITLE0', 1, 0, 'var', '', 0 ],
[ 'UserFirstname', 'Vorname', 'FIRSTNAME0', 1, 0, 'var', '', 0 ],
[ 'UserLastname', 'Nachname', 'LASTNAME0', 1, 0, 'var', '', 0 ],
[ 'UserEmail', 'E-Mail', 'EMAIL0', 1, 0, 'var', '', 0 ],
[ 'UserCustomerID', 'CustomerID', 'COMPANY1', 0, 1,'var', '', 0 ],
[ 'UserPassword', 'Password', 'LASTNAME0', 1, 0, 'var', '', 0 ],
[ 'UserLogin', 'Login', 'LASTNAME0', 1, 0, 'var', '', 0 ],
[ 'Firma', 'Firma', 'COMPANY1', 1, 0, 'var', '', 0 ],
# [ 'CustomerID', 'CustomerID', 'ID', 0, 1, 'var', '', 0 ],
# [ 'UserCustomerIDs', 'CustomerIDs', 'customer_ids', 1, 0, 'var', '', 0 ],
[ 'UserPhone', 'Phone', 'PHONE0', 1, 0, 'var', '', 0 ],
[ 'UserFax', 'Fax', 'FAX0', 1, 0, 'var', '', 0 ],
[ 'UserMobile', 'Mobile', 'MOBILEPHONE0', 1, 0, 'var', '', 0 ],
[ 'UserStreet', 'Street', 'STREET0', 1, 0, 'var', '', 0 ],
[ 'UserZip', 'Zip', 'ZIP0', 1, 0, 'var', '', 0 ],
[ 'UserCity', 'City', 'CITY0', 1, 0, 'var', '', 0 ],
[ 'UserCountry', 'Country', 'STATE0', 1, 0, 'var', '', 0 ],
[ 'UserComment', 'Comment', 'NOTES0', 1, 0, 'var', '', 0 ],
[ 'ValidID', 'Valid', 'TEXT1', 0, 1, 'int', '', 0 ],
],
# default selections
Selections => {
UserTitle => {
'Mr.' => 'Mr.',
'Mrs.' => 'Mrs.',
},
},
};
# ---------------------------------------------------- #
# #
# end of your own config options!!! #
# #
# ---------------------------------------------------- #
# ---------------------------------------------------- #
}
# ---------------------------------------------------- #
# needed system stuff (don't edit this) #
# ---------------------------------------------------- #
use base qw(Kernel::Config::Defaults);
# -----------------------------------------------------#
1;
######################################
In der Kundenverwaltung ist leider im Dropdownfeld nur Database Backend zu sehen. Eine Suche mit * liefert auch nichts zurück. Das Kunden-Informationszentrum liefert "An error occurred! ... Error during AJAX communication. Status: error, Error: Internal Server Error".
Muss die Tabelle dbo.ADDRESSES heissen wie im MSSQL Managementstudio ersichtlich oder nur ADDRESSES? Die Felder in der dritten Spalte bei Maps (STREET0, PHONE0, MOBILEPHONE0, etc.) sind die Spalten aus der MSSQL. Habe ich das evtl. falsch verstanden hier?
Im Sysconfig ist auch bei LogLevel was eingetragen. Leider sehe ich weder in /var/log/syslog was noch in der evtuellen dort hinterlegten Datei /var/log/otrs.log.
Viellecht hat jemand einen Tip für micht. Ich komme hier nicht weiter.
LG teiln999