Unable to authenticate users from SQLResolver for selfservice

[Robert Roos]

Hi,

I've managed to setup PrivacyIDEA sucessfully. As an admin I'm able to also enroll tokens for user.

But now I would like to enable the selfservice workflow to let users enroll tokens by themselves.

I've set the otppin:userstore setting to allow authentication for users from a SQLresolver. 

But unfortunately these users aren't able to logon to the portal.

They get an error message stating: Authentication Failed. Wrong Credentials.

I tried to logon with:

username + password

username@realmname + password

username (select realm via pulldown menu) + password

But none of these options worked.

Does anybody knows a solution for this?

Regards,

Robert

[Cornelius Kölbel]

You need to have the correct user mapping. Map the password field.

As there is no default way to store passwords in SQL database, you can imagine that not all ways to store passwords are supported.

[Robert Roos]

Hi Cornelius:-)

I've tried to store the password with several algorithms in the database even cleartext but that didn't help. 

I noticed the following lines in the log:

[2017-04-08 05:53:26,524][31092][140499540760448][INFO][privacyidea.lib.user:329] User u'UserName' from realm u'UserNamenet' tries to authenticate

[2017-04-08 05:53:26,529][31092][140499540760448][INFO][privacyidea.lib.resolvers.SQLIdResolver:570] using the connect string mysql://test:tes...@192.168.x.x:3306/testdb

[2017-04-08 05:53:26,578][31092][140499540760448][INFO][privacyidea.lib.resolvers.SQLIdResolver:570] using the connect string mysql://test:tes...@192.168.x.x:3306/testdb

[2017-04-08 05:53:26,644][31092][140499540760448][INFO][privacyidea.lib.user:342] user User(login=u'UserName', realm=u'UserNamenet', resolver=u'UserNamenet') failed to authenticate.

[2017-04-08 05:53:26,655][31092][140499540760448][ERROR][privacyidea.lib.auditmodules.sqlaudit:233] exception DataError('(pymysql.err.DataError) (1406, u"Data too long for column \'user\' at row 1")',)

[2017-04-08 05:53:26,655][31092][140499540760448][ERROR][privacyidea.lib.auditmodules.sqlaudit:234] DATA: {'info': 'Wrong credentials', 'success': False, 'privacyidea_server': '192.168.x.x', 'client_user_agent': 'firefox', 'client': '192.168.x.x', 'user': u'UserName@UserNamenet', 'action_detail': '', 'action': 'POST /auth'}

Op vrijdag 7 april 2017 19:39:42 UTC+2 schreef Cornelius Kölbel:

[Cornelius Kölbel]

Take a look at the script privacyidea-create-userdb.

It creates a sqlresolver and you can add users in the webUI.

You may use this to understand how the SQLResolver in regards to passwords works.

[Robert Roos]

Hi Cornelius,

This script seems to be very straightforward. As I mentioned previously I've created a correct mapping between the database columns and the fields in PrivacyIDEA.

Passwords are currently stored in MD5 format within the database, will be upgraded later to SSHA512. Might that be the reason that the integration doesn't work right now?

Ps. I ran the privacyidea-create-userdb but I encountered errors. It did create a resolver but when I try to add a user it says 'Session' object has no attribute '_model_changes' (I'm using version 2.18):

 self.transaction.commit()
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 392, in commit
    self._prepare_impl()
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 361, in _prepare_impl
    self.session.dispatch.before_commit(self.session)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/event/attr.py", line 218, in __call__
    fn(*args, **kw)
  File "/usr/lib/python2.7/dist-packages/flask_sqlalchemy/__init__.py", line 162, in session_signal_before_commit
    d = session._model_changes
AttributeError: 'Session' object has no attribute '_model_changes'

Op woensdag 12 april 2017 00:43:29 UTC+2 schreef Cornelius Kölbel:

[Cornelius Kölbel]

We fixed an issue with model_changes in the current within master.
Wait for 2.19 or install from the ppa dev repo.
Kind regards
Cornelius