use MSSQL db for customer

[andreaconsadori]

Hi,
i've a mssql database with all customer data, i can reach it via odbc connection,
i search in otrs manual but i've big problem configuring odbc connection from otrs to mssql

i've installed on centos server, and also i've downloaded
perl-DBD-ODBC-1.15-1.el5.rf.i386.rpm

can someone help me?

this is my odbc string
PROVIDER=SQLOLEDB;SERVER=SERVER1;DATABASE=DB_TEST;Uid=test;Pwd=test

[crythias]

Check out http://doc.otrs.org/2.4/en/html/x1801.html

Code: Select all

    $Self->{CustomerUser1} = {
        Name => 'Customer Database',
        Module => 'Kernel::System::CustomerUser::DB',
        Params => {
            # if you want to use an external database, add the
            # required settings
#            DSN => 'DBI:odbc:yourdsn',
#            DSN => 'DBI:mysql:database=customerdb;host=customerdbhost',
#            User => '',
#            Password => '',
            Table => 'customer_user',
        },
        # customer unique id
        CustomerKey = 'login',
        # customer #
        CustomerID = 'customer_id',
        CustomerValid = 'valid_id',
        CustomerUserListFields => ['first_name', 'last_name', 'email'],
        CustomerUserSearchFields => ['login', 'last_name', 'customer_id'],
        CustomerUserSearchPrefix => '',
        CustomerUserSearchSuffix => '*',
        CustomerUserSearchListLimit => 250,
        CustomerUserPostMasterSearchFields => ['email'],
        CustomerUserNameFields => ['salutation','first_name','last_name'],
        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 => 1,
#        # cache time to live in sec. - cache any database queries
#        CacheTTL => 0,
#        # just a read only source
#        ReadOnly => 1,
        Map => [

            # note: Login, Email and CustomerID needed!
            # var, frontend, storage, shown (1=always,2=lite), required, storage-type, http-link, readonly, http-link-target
            [ 'UserSalutation', 'Salutation', 'salutation',   1, 0, 'var', '', 0 ],
            [ 'UserFirstname',  'Firstname',  'first_name',   1, 1, 'var', '', 0 ],
            [ 'UserLastname',   'Lastname',   'last_name',    1, 1, 'var', '', 0 ],
            [ 'UserLogin',      'Username',   'login',        1, 1, 'var', '', 0 ],
            [ 'UserPassword',   'Password',   'pw',           0, 0, 'var', '', 0 ],
            [ 'UserEmail',      'Email',      'email',        1, 1, 'var', '', 0 ],
            [ 'UserCustomerID', 'CustomerID', 'customer_id',  0, 1, 'var', '', 0 ],
            [ 'UserPhone',      'Phone',      'phone',        1, 0, 'var', '', 0 ],
            [ 'UserFax',        'Fax',        'fax',          1, 0, 'var', '', 0 ],
            [ 'UserMobile',     'Mobile',     'mobile',       1, 0, 'var', '', 0 ],
            [ 'UserStreet',     'Street',     'street',       1, 0, 'var', '', 0 ],
            [ 'UserZip',        'Zip',        'zip',          1, 0, 'var', '', 0 ],
            [ 'UserCity',       'City',       'city',         1, 0, 'var', '', 0 ],
            [ 'UserCountry',    'Country',    'country',      1, 0, 'var', '', 0 ],
            [ 'UserComment',    'Comment',    'comments',     1, 0, 'var', '', 0 ],
            [ 'ValidID',        'Valid',      'valid_id',     0, 1, 'int', '', 0 ],
        ],
        # default selections
        Selections => {
            UserSalutation => {
                'Mr.' => 'Mr.',
                'Mrs.' => 'Mrs.',
            },
        },
    };

[andreaconsadori]

i just see this but i need a mssql connection example.

in a doich post i found this

Code: Select all

$Self->{CustomerUser} = {
Name => 'Kundendatenbank',
Module => 'Kernel::System::CustomerUser::DB',
Params => {
# if you want to use an external database, add the
# required settings
DSN => 'DBI:Sybase:',
DSN => 'DBI:Sybase:server=hier freetds-alias;database=datenbankname',
User => 'MSSQL-User',
Password => 'MSSQL-Passwort',
Type => 'mssql',
Table => 'Tabellen- oder Viewname',
}

tomorrow i'll try it.

i've installed freetds and configured correctly because with isql command i can connect to my db.

any other info to diagnose "cannot connect to cuostomer db" error ?

[FlavioB]

Ciao Andrea,

did you get it running with MSSQL as customer backend?
If yes, could you tell us how?

Grazie,
F.