Duplicate user profiles

Carsten Lempert shared this problem 7 months ago
In Progress

Hi,

when a new user login in YF, two user profiles where created! I see in the DB also two entries. So whats wrong?


/wfCLwSBypjKMQAAAABJRU5ErkJggg==

Comments (32)

photo
1

Here the database entries:

photo
1

Hi Carsten,

sounds like it might be bug, but I will need some more information so I can try and replicate it over here.

Are the new users LDAP users?

If so, are you using LDAP groups? Are you mapping them to Yellowfin client orgs?

Are you using "User Name" or "Email Address" as your Logon ID?

Is there a new account created for the same user every time they log back in? (e.g. if they log in 4 times, then they will have 4 profiles)

Are you using Active Directory or some other LDAP server such as Apache?

Which build of 7.4 are you using (e.g. 20150515)?

thanks,

David

photo
1

Actually there's one more thing that could be helpful to me.

Could you please turn on DEBUG logging and then get a user to to log in, and if that creates a duplicate account then please send me the yellowfin debug log.

thanks,

David

photo
1

HI,

ok I had enable the debug mode. Yes they are all ldap user. What do you mean with " Are you mapping them to Yellowfin client orgs?"

We are using the build number 20180104 and Version 7.4. Our LoginID is the username.

No I see only duplicate user entries when they only login the first time. The LDAP is a lightweight Server.

photo
1

Hi today we had the Problem again with an other user. But the yellowfin.log grows to fast. The 9 files are new every 10 Seconds...so this doesn't work ;)

photo
1

So now i have the logs. The user is called Markus Reeb. I also see two entries in the Person table

photo
1

Hi, our Administrators says it is Active Directory and not lightweight.

photo
1

Hi Christian,

thanks for the debug logs, and I can clearly see that Yellowfin is creating 2 entries:


YF:2018-06-14 13:34:48:DEBUG (IpClassManager:debug) - IpClass INSERT SQL: 
INSERT INTO  IpClass (  EmailRight, EmailLeft, IpId, Password, CRC, PasswordExpired, StartDate, EndDate, AccessAttempts ) VALUES (N'provinzial.de',N'markus.reeb',14722,N'sCN1YCcYxgijNIOKi4ktunPrgBQ=',null,1,'20180614','99991231',0)
and then again 5 seconds later:


YF:2018-06-14 13:34:53:DEBUG (IpClassManager:debug) - IpClass INSERT SQL: 
INSERT INTO  IpClass (  EmailRight, EmailLeft, IpId, Password, CRC, PasswordExpired, StartDate, EndDate, AccessAttempts ) VALUES (N'provinzial.de',N'markus.reeb',14723,N'6u4urVCxuOThdgarbYgQXbg+1NM=',null,1,'20180614','99991231',0)

So far unfortunately I have not been able to replicate this issue. I have used the same build of 7.4 as you, and am using Active Directory.

Could you please send me a screenshot of your LDAP Configuration screen so I can make sure I am doing the same as you, here is mine:

/Kmh0HT5YnWkAAAAASUVORK5CYIIA


And don't worry about my question about client orgs because I can see from your debug logs that you aren't using them, the users are logging into the Default Org (also known as Primary Org).


thanks,

David

photo
1

Hi,

we have configured AccessFilter...is it possible that this entries from there?


Here are our config:

photo
1

Hi Carsten,

unfortunately that config screenshot didn't come through, could you please try attaching it instead of pasting it?

Regarding the Access Filter, who knows, it could be possible that it is somehow caught up with the duplication bug. I would certainly like to test it out.

Please tell me know some more about your Access Filter, which type is it?

/EFAOUDQi9HFCr0CCHkfDImFB5ewROzoKwTF07CECZQb4DQyxEFC30t0fV3Hu1Qd19bhK5toM4AoQcAAFA4IPQAAAAKB4QeAABA4YDQAwAAKBwQegAAAIUDQg8AAKBwQOgBAAAUDgg9AACAwgGhBwAAUDgg9AAAAAoHhB4AAEDhgNADAAAonOHh4f8PnhRO4bvfsH8AAAAASUVORK5CYIIA

Do you have "New User Auto Refresh" switched on?

Do you have "Append" or "Overwrite" new values configured?

Do you have "Refresh Schedule" turned on?

thanks,

David

photo
1

Hi,

I use "SQL Query" You can see my config in the screenshot.

photo
1

Hi Carsten,

OK, thanks for that information, however I have now set up my LDAP configuration like yours, and I have created an SQL Query Access Filter (I assumed your data source for the Access Filter was one of the Yellowfin user tables such as IpClass or Person, could you please confirm that?) and am using the same build of 7.4 as you but unfortunately can still not replicate the duplicate user issue.

I'm sorry but because I'm having no success in replicating this issue I have to ask more questions:

1) Are you doing anything extra with user creation other than the "vanilla" (i.e. plain) Yellowfin standard process? What I mean by that is, for example, some clients like to pre-populate their Yellowfin user tables with the new users before they log in (so they can configure the default settings beforehand such as default dashboard).

2) Are you using Yellowfin as an "off-the-shelf" application or have you integrated it or embedded it into another application? If so, could you please describe the setup.

3) Have you always had this issue or has it recently started? If so, what other changes have there been in your environment?

Another idea I have involves installing a fresh temporary test installation of Yellowfin and seeing if the LDAP issue exists there too. The reason for this is because occasionally (actually it doesn't happen often, but nevertheless it is still a possibility) one of the library files (in either Yellowfin or Java) might have become corrupt during upgrades, or the original download corrupted the installer. Hence if you did a fresh new download of Yellowfin and Java (and check the MD5 checksum to see they are not corrupted by the download) on your laptop or something, and configured it to connect to your LDAP server, it would be interesting to see if the duplication issue occurred there as well. What do you think of this idea? I can give you a temporary licence for your laptop or other test environment.

The last time we had to deal with an LDAP bug (about 4 years ago) that we couldn't reproduce, we were lucky because the client opened their LDAP connection to allow us in, so we connected remotely and stepped through the source code and were able to find out the cause of the issue. How do you feel about that? Do you think if the situation came to that, would you be allowed to open your connection to us?

Sorry for the length of this response but with these types of elusive bugs one has to think of different ways to deal with them.

regards,

David

photo
1

Hi David,

to your Points:

1. We are using the normal YF processes. So we don't Change anything.

2. We use YF in Standard.

3. In the past it works, but there we use the unsecure ldap. Now we use ssl. My opinion is that after this change we had Problems with duplicate users. We changed in the Java propoerties the ssl config:

photo
1

Hi Carsten,

that is interesting because the only thing in my configuration that was different than your setup was the fact that I am using unsecure LDAP whereas I noticed you are using SSL.

OK, I will have to set up an Active Directory server over here with SSL enabled. I'll let you know when I've successfully done that.

regards,

David

photo
1

Hi Carsten,

just keeping you posted...I have set up a VM with Windows Server 2012 and I enabled AD and that all worked fine (could connect to it over port 389), but then I carefully all the stops for setting it up for SSL and it doesn't seem to working. Can't connect to it via Yellowfin or JXplorer over 636. I've tried 3 times generating the root cert and CA, then generating the cert request and importing it into the java keystore (i.e. I've made 3 different sets of certificates, and in different ways, for example using openssl on Ubuntu, and also openssl on the actual AD server), but for some reason, no luck so far. However, I never give up! So will try again and then keep you posted.

regards,

David

photo
1

Hey David,

good luck ;)

photo
1

Hi Carsten,

I'm just keeping you updated again, I haven't forgotten about this ticket, last weekend I set up 3 more VMs and had more failures! I set one up as AD-DS and that didn't work so then I set another one up as AD-LDS but that didn't help either. Then I became aware that the VM's hostname wasn't unique on the network so Windows added a 0 to the hostname to make the netbios name, so I changed the hostname to a unique one and set up another instance of AD but that didn't help.

Also, I followed different documentations to setup LDAPS each time as well, once I followed Microsoft, then I found other different articles and followed them instead but all failed! Interestingly I noticed that all the different documentations had different steps, they were using different formats of certificates and keys and different softwares to create them or convert them, so I can see there is lots of room for variation in the task of setting up LDAPS. So I'd certainly be interested if you could recommend a guide that you used yourself and found to be useful!

Then during the week things have been very hectic so I didn't get another chance to try other things, however, now our work queue is looking much better so I expect I'll be able to look into this again next week.

Apologies again for the delay, I really didn't expect this to be so hard!

Thanks for your patience.

David

photo
1

Hi David,

no Problem. I will wait :)

photo
1

Hi Carsten,

thanks for your patience!

I think I'm getting closer, in my latest attempt I notice that the Local Security Authority Subsystem Service is listening on port 636 as well as 389:

/AAezV2J8Nnq5AAAAAElFTkSuQmCCAA==

and I can connect on 389, just not 636 yet, so it must be something to do with the client certificate.

Will keep you updated...

regards,

David

photo
1

Ok. Thanks.

photo
1

Hi Carsten,

I hadn't forgotten you! This week was the first time this year that things were a bit quieter than normal, so I took the opportunity to go back to mucking around with my LDAPS server.....and now as a result....I've got good news and bad news to tell you!

The good news is that I finally was able to get my windows 2012 server Active Directory working over SSL.

But unfortunately the bad news is that I still can't replicate the duplicate user issue that you are experiencing.

I made sure that I tested this in the same build of Yellowfin as yours (7.4 20180104), and I configured it the same way (UserID instead of Email login).

I have attached a 2 minute video showing my attempt at replicating the issue.

From reading back over all of this ticket I can see that the only thing I didn't configure the same as you was the Access Filter, so with that in mind, could you please show me the SQL code for your Access Filter so I can do a similar query over here.

thanks,

David

photo
1

Hi yes a long time ago. ;)

So we had this problem not for everybody.

This is my SQL for one accessfilter. In the example this guy is an "Abteilungsleiter" (Head of department).


SELECT 'EMAIL',MAIL,'Benutzerkennung FK', PERSONAL_NO FROM COCO_PERSON_V WHERE COCO_PERSON_V.FUNCTION IN ('Abteilungsleiter','Abteilungsleiterin')

photo
1

Hi Carsten,

Well I tried adding an Access Filter with SQL query but unfortunately that didn't help to replicate the duplication issue (I have attached a video of this attempt).

However, that sounds like we've got a big clue if the problem doesn't happen for everybody! If only we can work out what it is about the duplicated users that is different from the non-duplicated users.

Can you look in your LDAP tree and see if the duplicated users are configured differently than the non-duplicated users?

Also, if you could send me your IpClass table and the Person table, and also give me some examples of users who had the duplication problem and users who didn't, then that would be great!

regards,

David

photo
1

Hi David,

so I exported the table and for example we have some people duplicated, for example the person "Ingo Sell" (ID 15301

and ID 15302). The ldap tree is fine. You can see it also in the column "PrivateURL" in the person table.

photo
1

Hi I build an sql. And I see that some people are 4 times in it.

Here the SQL:


SELECT

Person.FullName,

Person.PrivateURL,

status,

COUNT(*)

FROM

Person

WHERE

Person.PrivateURL IS NOT NULL

GROUP BY

Person.FullName,

Person.PrivateURL,

status

HAVING

COUNT(*)>1

photo
1

Hi Carsten,

thanks for the tables and screenshots, I studied the users with 1 entry versus the users with > 1 entry and I couldn't see any pattern unfortunately.

Now I need to come up with a new idea on how to replicate this issue:

1) I have the same build of 7.4 as you (20180104)

2) I have authentication = Username like you

3) I have Active Directory over SSL as my LDAP server like you

4) I have set up an Access Filter of the type SQL Query like you

and yet I can't replicate the duplication issue!

I will let you know when I come up with any new ideas, and of course, if you have any ideas then please let me know.

regards,

David

photo
1

Hi,

ok. I will do that. ;)

Freundliche Grüße

Carsten Lempert

8103 IT-Servicemanagement

Westfälische Provinzial Versicherung Aktiengesellschaft

Postanschrift: 48131 Münster

Tel. +49 251 219-4989

Fax +49 251 219-754989

Mobil +49 174 337-4989

carsten.lempert@provinzial.de

www.provinzial-online.de

Rund um die Uhr für Sie da: www.meine-provinzial.de

Westfälische Provinzial Versicherung Aktiengesellschaft

Hausanschrift: Provinzial-Allee 1, 48159 Münster; Amtsgericht Münster HRB 6144

Vorstand: Dr. Wolfgang Breuer (Vorsitzender), Stefan Richter (stv. Vorsitzender), Frank Neuroth, Dr. Thomas Niemöller, Dr. Ulrich Scholten, Matthew Wilby

Vorsitzender des Aufsichtsrats: Matthias Löb

Von:        Support Queue <support@Yellowfin.bi>

An:        Carsten Lempert <carsten.lempert@provinzial.de>

Datum:        17.12.2018 07:18

Betreff:        New Comment in "Duplicate user profiles"

photo
1

Hi Carsten,

me again! This week has been a bit quiet so I've had time to read through the source code and to go back over and over through your original debug log files, I was hoping to find a bug in the code that in certain situations would cause a duplicate user to be created, but unfortunately I didn't. However, I did notice in the log files the following events:

this is where Markus Reeb first logs in

YF:2018-06-14 13:34:48: INFO (LogonAction:info) - Logon Action entered

and that leads to the LDAPAuthentication class being called which firstly verifies the user in your AD,


YF:2018-06-14 13:34:48:DEBUG (LDAPAuthentication:debug) - Searching using (cn=W088476) across DC=pnw,DC=loc

and then searches through the YF DB, and if it doesn't find him then creates a new user record for the YF DB. I noticed in the source code that when it finally completes the process it logs an entry of "Exiting LDAPAuthentication Plugin"

However, before that initial LDAPAuthentication Plugin has had a chance to exit, there is another login 5 seconds later:

YF:2018-06-14 13:34:53: INFO (LogonAction:info) - Logon Action entered
and at the point it is not obvious who has just tried to login, but about 100 lines later there is the following:

YF:2018-06-14 13:34:53:DEBUG (LDAPAuthentication:debug) - Searching using (cn=W088476) across DC=pnw,DC=loc
so it is Markus Reeb again!

And then again Yellowfin authenticates Markus against your AD, and then it searches the YF DB for him:

YF:2018-06-14 13:34:53:DEBUG (IpClassManager:debug) - IpClass SELECT SQL: 
SELECT EmailRight, EmailLeft, IpId, Password, CRC, PasswordExpired, StartDate, EndDate, AccessAttempts  FROM IpClass WHERE IpId > 0  AND EmailLeft = N'markus.reeb'   AND EmailRight = N'provinzial.de'   AND StartDate <= '20180614' ORDER BY EndDate DESC, StartDate DESC 
But it doesn't find him, so that's why it creates the duplicate entry for Markus:

YF:2018-06-14 13:34:53:DEBUG (LDAPAuthentication:debug) - User not found in database.. creating new..
So I am left with 2 questions here:

1) Did Markus really click the login button twice? (with 5 seconds in between the clicks)

2) Why didn't Yellowfin find the newly created user records for Markus after the 2nd login at 13:34:53? (because the new user records were already inserted at 13:34:48). I suspect the answer here is that the transaction wasn't committed by then (I say this because there had been no "Exiting LDAPAuthentication" message by this point in time)

Next week the developer who wrote the login code is returning to the office so I can ask him about my question no.2. So regarding my question no.1 I was wondering if maybe you could test if it is possible to create duplicate user entries by clicking the login button a 2nd time a few seconds after the 1st click (maybe with a test user?)

regards,

David

photo
1

Hello again Carsten,

one more question - are you using a stand-alone, "off-the-shelf" Yellowfin or is your Yellowfin integrated into your own application and uses Single Sign On?

regards,

David

photo
1

Hi Dave,

so we use SSO.

1. Markus cannot click twice, because of SSO.

2. I don't know why the user is not created at the time. I think there is something wrong in the yf function.

photo
1

Hi Carsten,

thanks for those answers, now I will wait until next week for the developer who wrote the login process to return to the office and then I will show him all I have discovered so far and see if he can think of anything.

regards,

David

photo
1

Ok. perfect.