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.
Import customer user certificate into customer user database
Moderator: crythias
Re: Import customer user certificate into customer user database
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
"Testing": ((OTRS Community Edition)) and git Master
Never change Defaults.pm! :: Blog
Professional Services:: http://www.otrs.com :: enjoy@otrs.com
-
- 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
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 ],
],
$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 ],
],
-
- 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
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.
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.
-
- 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
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.
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.
-
- 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
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
- convert the binary data from active directory to pem format
- put pem formatted data into a VARCHAR column in the database