Unable to authenticate users from SQLResolver for selfservice

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

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.Am Freitag, 7. April 2017 10:30:50 UTC+2 schrieb 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

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:testpwd@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:testpwd@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:

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.

Am Freitag, 7. April 2017 10:30:50 UTC+2 schrieb 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

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.Am Samstag, 8. April 2017 08:08:20 UTC+2 schrieb 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:testpwd@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:testpwd@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:

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.

Am Freitag, 7. April 2017 10:30:50 UTC+2 schrieb 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

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:

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.

Am Samstag, 8. April 2017 08:08:20 UTC+2 schrieb 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:testpwd@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:testpwd@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:

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.

Am Freitag, 7. April 2017 10:30:50 UTC+2 schrieb 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

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