Import customer user certificate into customer user database

Moderator: crythias

Post Reply
pfipf
Znuny advanced
Posts: 123
Joined: 14 Jul 2017, 09:30
Znuny Version: 6 Free
Real Name: Günter
Company: KIT

Import customer user certificate into customer user database

Post by pfipf »

Hello,
I know OTRS (5s) is able to read customer user certificates directly from active directory but I need to have the certificates in our customer user database backend. I imported the customer certificate data as I received it from active directory without converting it, but OTRS is not able to read it.
I believe I have to convert the data somehow.
Which kind of data does OTRS need for recognizing it as certificate?

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

Re: Import customer user certificate into customer user database

Post by jojo »

Please show you Config.pm part for the customer database.
"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
pfipf
Znuny advanced
Posts: 123
Joined: 14 Jul 2017, 09:30
Znuny Version: 6 Free
Real Name: Günter
Company: KIT

Re: Import customer user certificate into customer user database

Post by pfipf »

Here it is:

$Self->{CustomerUser1} = {
Name => 'SCC Database Backend',
Module => 'Kernel::System::CustomerUser::DB',
Params => {
# if you want to use an external database, add the
# required settings
DSN => 'DBI:mysql:database=testDB;host=localhost',
User => 'someuser',
Password => 'somepass',
Table => 'sometable',
ForeignDB => 1, # set this to 1 if your table does not have create_time, create_by, change_time and change_by fields

# CaseSensitive defines if the data storage of your DBMS is case sensitive and will be
# preconfigured within the database driver by default.
# If the collation of your data storage differs from the default settings,
# you can set the current behavior ( either 1 = CaseSensitive or 0 = CaseINSensitive )
# to fit your environment.
#
# CaseSensitive => 0,

# SearchCaseSensitive will control if the searches within the data storage are performed
# case sensitively (if possible) or not. Change this option to 1, if you want to search case sensitive.
# This can improve the performance dramatically on large databases.
SearchCaseSensitive => 0,
},

# customer unique id
CustomerKey => 'samaccountname',
#CustomerKey => 'login',

# customer #
CustomerID => 'kundennummer',
#CustomerID => 'customer_id',
CustomerValid => 'gueltig',
#CustomerValid => 'valid_id',

# The last field must always be the email address so that a valid
# email address like "John Doe" <john.doe@domain.com> can be constructed from the fields.
CustomerUserListFields => [ 'samaccountname', 'vorname', 'nachname', 'email' ],

# CustomerUserListFields => ['login', 'first_name', 'last_name', 'customer_id', 'email'],
CustomerUserSearchFields => [ 'samaccountname', 'vorname', 'nachname', 'email' ],
#CustomerUserSearchFields => [ 'login', 'first_name', 'last_name', 'customer_id' ],
CustomerUserSearchPrefix => '*',
CustomerUserSearchSuffix => '*',
CustomerUserSearchListLimit => 250,
CustomerUserPostMasterSearchFields => ['email'],
CustomerUserNameFields => [ 'vorname', 'nachname' ],
#CustomerUserNameFields => [ 'title', 'first_name', 'last_name' ],
#Checks email address is unique;default=1
CustomerUserEmailUniqCheck => 0,

# # show now own tickets in customer panel, CompanyTickets
# CustomerUserExcludePrimaryCustomerID => 0,
# # generate auto logins
# AutoLoginCreation => 0,
# # generate auto login prefix
# AutoLoginCreationPrefix => 'auto',
# # admin can change customer preferences
# AdminSetPreferences => 1,
# use customer company support (reference to company, See CustomerCompany settings)
CustomerCompanySupport => 1,
# cache time to live in sec. - cache any database queries
CacheTTL => 60 * 60 * 24,
# # 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, link class(es)
#[ 'UserTitle', Translatable('Title or salutation'), 'title', 1, 0, 'var', '', 0 ],
[ 'UserFirstname', Translatable('Firstname'), 'vorname', 1, 1, 'var', '', 0 ],
#[ 'UserFirstname', Translatable('Firstname'), 'first_name', 1, 1, 'var', '', 0 ],
[ 'UserLastname', Translatable('Lastname'), 'nachname', 1, 1, 'var', '', 0 ],
#[ 'UserLastname', Translatable('Lastname'), 'last_name', 1, 1, 'var', '', 0 ],
[ 'UserLogin', Translatable('Username'), 'samaccountname', 1, 1, 'var', '', 0 ],
#[ 'UserLogin', Translatable('Username'), 'login', 1, 1, 'var', '', 0 ],
#[ 'UserPassword', Translatable('Password'), 'pw', 0, 0, 'var', '', 0 ],
[ 'UserEmail', Translatable('Email'), 'email', 1, 1, 'var', '', 0 ],
# [ 'UserEmail', Translatable('Email'), 'email', 1, 1, 'var', '[% Env("CGIHandle") %]?Action=AgentTicketCompose;ResponseID=1;TicketID=[% Data.TicketID | uri %];ArticleID=[% Data.ArticleID | uri %]', 0, '', 'AsPopup OTRSPopup_TicketAction' ],
[ 'UserCustomerID', Translatable('CustomerID'), 'kundennummer', 1, 1, 'var', '', 0 ],
# [ 'UserCustomerIDs', Translatable('CustomerIDs'), 'customer_ids', 1, 0, 'var', '', 0 ],
[ 'UserPhone', Translatable('Phone'), 'telefon', 1, 0, 'var', '', 0 ],
[ 'Organisationseinheit', 'OE', 'orgkuerzel', 1, 0, 'var', '', 0 ],
[ 'ITB', 'IT Beauftragter', 'itb', 1, 0, 'var', '', 0 ],
#[ 'UserPhone', Translatable('Phone'), 'phone', 1, 0, 'var', '', 0 ],
#[ 'UserFax', Translatable('Fax'), 'fax', 1, 0, 'var', '', 0 ],
#[ 'UserMobile', Translatable('Mobile'), 'mobile', 1, 0, 'var', '', 0 ],
#[ 'UserStreet', Translatable('Street'), 'street', 1, 0, 'var', '', 0 ],
#[ 'UserZip', Translatable('Zip'), 'zip', 1, 0, 'var', '', 0 ],
#[ 'UserCity', Translatable('City'), 'city', 1, 0, 'var', '', 0 ],
#[ 'UserCountry', Translatable('Country'), 'country', 1, 0, 'var', '', 0 ],
#[ 'UserComment', Translatable('Comment'), 'comments', 1, 0, 'var', '', 0 ],
[ 'ValidID', Translatable('Valid'), 'gueltig', 0, 1, 'int', '', 0 ],
[ 'UserSMIMECertificate', 'SMIMECertificate', 'usercertificate', 0, 0, 'var', '', 0 ],
],
pfipf
Znuny advanced
Posts: 123
Joined: 14 Jul 2017, 09:30
Znuny Version: 6 Free
Real Name: Günter
Company: KIT

Re: Import customer user certificate into customer user database

Post by pfipf »

I got the solution myself.
Certificate data can be imported from active directory directly into the database and read by OTRS.
Important: the data type of the column in the database must be of type "Binary".
I used a VARBINARY for this and it worked.
pfipf
Znuny advanced
Posts: 123
Joined: 14 Jul 2017, 09:30
Znuny Version: 6 Free
Real Name: Günter
Company: KIT

Re: Import customer user certificate into customer user database

Post by pfipf »

Hello,

I've another problem with this.
As I have mapped the customer certificate in Config.pm like this

[ 'UserSMIMECertificate', 'SMIMECertificate', 'usercertificate', 0, 0, 'var', '', 0 ]

login into the customer interface fails with "internal server error".
Apache log says

"Malformed UTF-8 character (fatal) at /opt/otrs//Kernel/System/DB/mysql.pm line 125.\n"

Probably the storage-type is not correct. In an old chat (viewtopic.php?t=10295#p40154) there is a hint for setting storage-type "bin" by http-link.

Does anybody know how this works?

Any hints are welcome.
Thanks.
pfipf
Znuny advanced
Posts: 123
Joined: 14 Jul 2017, 09:30
Znuny Version: 6 Free
Real Name: Günter
Company: KIT

[SOLVED]Re: Import customer user certificate into customer user database

Post by pfipf »

I got a solution for all these issues.
- convert the binary data from active directory to pem format
- put pem formatted data into a VARCHAR column in the database
Post Reply