Logins from Customer.PL with SQLServer Customer Backend...

English! place to talk about development, programming and coding
Post Reply
PedFastTech
Znuny newbie
Posts: 7
Joined: 06 Jun 2012, 00:28
Znuny Version: 3.1.1

Logins from Customer.PL with SQLServer Customer Backend...

Post by PedFastTech »

I have OTRS running successfully on Ubuntu on Amazon EC2.

I have successfully installed ODBC, connected to the customer backend database on a SQL server also on EC2, and can see, search for, and enter tickets for customers via the agent panels. Zoming in on a customer does not display the password; instead we get an empty input field.

What I cannot do is log in using Customer.pl -- every enter with known good passwords comes back with an invalid User ID or Password.

I have done a SQL Server Profiler, and have verified that OTRS asks for the customer row and gets one back from the database. I have verified that the Config.pm entries for Customer password point to the correct column. I am missing something simple and I cannot see it. I am hopeful that someone on this board will respond. :?

Here is the Config.pm Excerpt:

Code: Select all

$Self->{CustomerUser} = {
    Name => 'PFCustomer Datasource',
    Module => 'Kernel::System::CustomerUser::DB',
    Params => {
      # if you want to use an external database, add the required settings
      DSN => 'DBI:ODBC:mssqlcustomer',
      User => '[redacted]',
      Password => '[redacted]',
      Table => 'V_MYMBFS_PERSON',
      CaseSensitive => 1,
    },
    # customer unique id
    CustomerKey => 'sPemail',
    CustomerPw  => 'sPPassword',
    # customer #
    CustomerID => 'sPEmail',
    CustomerValid => 'valid_id',
    CustomerUserListFields => [ 'sPFirstName', 'sPLastName', 'sPEmail' ],
    CustomerUserSearchFields => [ 'sPemail', 'sPLastName', 'sPFirstName' ],
    CustomerUserSearchPrefix => '',
    CustomerUserSearchSuffix => '%',
    CustomerUserSearchListLimit => 250,
    CustomerUserPostMasterSearchFields => [ 'sPemail' ],
    CustomerUserNameFields => [ 'sPFirstName', 'sPLastName' ],
    CustomerUserEmailUniqCheck => 1,
    # 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
      [ 'UserFirstname',  'Firstname',   'SPFirstName', 1, 1, 'var', '', 0 ],
      [ 'UserLastname',   'Lastname',    'sPLastName',  1, 1, 'var', '', 0 ],
      [ 'UserLogin',      'Username',    'sPEmail',     1, 1, 'var', '', 0 ],
      [ 'UserPassword',   'Password',    'sPPassword',  1, 1, 'var', '', 0 ],
      [ 'UserEmail',      'Email',       'sPEmail',     1, 1, 'var', '', 0 ],
      [ 'UserCustomerID', 'MyMBFS Key',  'iPID',        1, 1, 'var', '', 0 ],
      [ 'UserPhone',      'Phone',       'sPPhone1',    1, 0, 'var', '', 0 ],
      [ 'UserFax',        'Fax',         'sPPhone2',    1, 0, 'var', '', 0 ],
      [ 'UserStreet',     'Street',      'sPAddr1',     1, 0, 'var', '', 0 ],
      [ 'UserZip',        'Zip',         'sPZipPostal', 1, 0, 'var', '', 0 ],
      [ 'UserCity',       'City',        'sPCity',      1, 0, 'var', '', 0 ],
      [ 'UserCountry',    'Country',     'sPCountry',   1, 0, 'var', '', 0 ],
      [ 'UserComment',    'Comment',     'sPReferrer',  1, 0, 'var', '', 0 ],
      [ 'ValidID',        'Valid',       'valid_id',    0, 1, 'int', '', 0 ],
    ],
};
Please tell me what I need to do. OTRS is reading a row from the SQL Server database and retrieving the password. Why is OTRS not using the password?

Does anyone know the answer?

Thanks
John.
jojo
Znuny guru
Posts: 15019
Joined: 26 Jan 2007, 14:50
Znuny Version: Git Master
Contact:

Re: Logins from Customer.PL with SQLServer Customer Backend.

Post by jojo »

you don't configured a customer Auth section in your config.pm
"Production": OTRS™ 8, OTRS™ 7, STORM powered by OTRS
"Testing": ((OTRS Community Edition)) and git Master

Never change Defaults.pm! :: Blog
Professional Services:: http://www.otrs.com :: enjoy@otrs.com
PedFastTech
Znuny newbie
Posts: 7
Joined: 06 Jun 2012, 00:28
Znuny Version: 3.1.1

Re: Logins from Customer.PL with SQLServer Customer Backend.

Post by PedFastTech »

jojo wrote:you don't configured a customer Auth section in your config.pm
Thanks, is there an example in the manual or somewhere else?

I thought I looked for that.

It is still reading the SQL Server table...

Thanks
John.
PedFastTech
Znuny newbie
Posts: 7
Joined: 06 Jun 2012, 00:28
Znuny Version: 3.1.1

Re: Logins from Customer.PL with SQLServer Customer Backend.

Post by PedFastTech »

Success! Since I have seen so many threads without a successful conclusion message, I thought that I would start a tradition here...

jojo was correct that I needed a CustomerAuth module in the Config.pm file. Here is what worked for me:

Code: Select all

    # This is the auth. module against the PFTech Database
    $Self->{'Customer::AuthModule'} = 'Kernel::System::CustomerAuth::DB';
    $Self->{'Customer::AuthModule::DB::Table'} = 'V_MYMBFS_PERSON';
    $Self->{'Customer::AuthModule::DB::CustomerKey'} = 'sPEmail';
    $Self->{'Customer::AuthModule::DB::CustomerPassword'} = 'sPPassword';
    $Self->{'Customer::AuthModule::DB::CryptType'} = 'plain';
    $Self->{'Customer::AuthModule::DB::DSN'} = 'DBI:ODBC:mssqlcustomer';
    $Self->{'Customer::AuthModule::DB::User'} = '[redacted]';
    $Self->{'Customer::AuthModule::DB::Password'} = '[redacted]';
The CryptType property is vitally important. The value "plain" is used to notate the application that the password in the database is in plain text and not a hash.

I'm up with this!

Thanks
John
Post Reply