removed users are still in the database

Guest ●

We are already thinking about removing the current database en replace with a new empty DB. Or is there an alternative way to clear out old users in DB ?

JeRoen

Reply URL

1

Mike Sheehan ●

Hi JeRoen,

Thanks for reaching out. If you only delete user data from the person table, you'll likely still see this error message because user data stored in the IpClass table as well. If you delete from both places I believe this should work.

Please attempt doing so and let me know how goes.

Regards,

Mike

Regards,

Mike

Reply URL

1

Guest ●

Hallo Mike,

Thanks for responding but we did indeed find the emailaddress (in parts) in the ipclass table and deleted that also but no change in the error. Users that did not login in the time the server was configured without LDAP can login and are being added to the users in the database (and I see them in the YF GUI). So the connection to LDAP seems to work correctly.

Any other table which might cause this. We remove the the records for that user from the events and events_archive tables also but I do not think those made any difference.

Regards,

JeRoen

Reply URL

1

Mike Sheehan ●

Hi JeRoen,

Thanks for your response. The removal aspect from the original post, got me going down what I think is the wrong track. Let's focus on the error message being seen. There are a couple possibilities here. From one previous case where this error message appeared, we discovered that the User ID was used for two users in LDAP. So please check your LDAP for any duplicates.

The other possibility is that Yellowfin still sees your "deleted" user in the Config DB, as these users aren't dropped from the database, which seems likely to be the case here. In order to fix this, you'll have to work a bit in the Configuration Database directly.

When running any queries directly against the Yellowfin Configuration Database it's possible to cause irrevocable data loss. This process isn't directly supported nor recommended unless otherwise advised to do so. Before running any queries against your Yellowfin Configuration Database, please make sure you have full, up-to-date backups of both your Yellowfin Configuration Database and your Yellowfin Installation folder. This way, if something goes wrong you can revert back to your current state.

You're going to want to restore this user and set them to an LDAP user.

First, find your user with the following query:

SELECT * FROM IpClass;

When a user is "deleted" from Yellowfin, the 'EndDate' value is set to the day prior to deletion. This effectively removes their access from Yellowfin. Take note of the 'IpId' of your user and issue:

UPDATE IpClass SET EndDate='9999-12-31' WHERE IpId=xxxx; 
UPDATE IpRltshp SET EndDate='9999-12-31' WHERE IpChild=xxxx;
UPDATE StaffMemberRole SET EndDate='9999-12-31' WHERE IpEmployee=xxxx;

where xxxx is the 'IpId' listed above for the user in question. You'll want to ensure that the password for this user account is the SAME as the one set in LDAP.

Now convert them to an LDAP User:

UPDATE Person SET PrivateKeyId='LDAP' WHERE IpPerson=xxxx;

If the password set for the Yellowfin user account is the same as that in LDAP, you can now log in as this user. This is the supported method in converting an existing user to LDAP. For users you've already deleted from the IpClass table, you should be able to just re-create the users then convert them to LDAP users via the steps listed above as well.

Please give this a go and let me know your results!

Thanks,

Mike

Regards,

Mike

Hi JeRoen,

Thanks for your response. The removal aspect from the original post, got me going down what I think is the wrong track. Let's focus on the error message being seen. There are a couple possibilities here. From one previous case where this error message appeared, we discovered that the User ID was used for two users in LDAP. So please check your LDAP for any duplicates.

The other possibility is that Yellowfin still sees your "deleted" user in the Config DB, as these users aren't dropped from the database, which seems likely to be the case here. In order to fix this, you'll have to work a bit in the Configuration Database directly.

When running any queries directly against the Yellowfin Configuration Database it's possible to cause irrevocable data loss. This process isn't directly supported nor recommended unless otherwise advised to do so. Before running any queries against your Yellowfin Configuration Database, please make sure you have full, up-to-date backups of both your Yellowfin Configuration Database and your Yellowfin Installation folder. This way, if something goes wrong you can revert back to your current state.

You're going to want to restore this user and set them to an LDAP user.

First, find your user with the following query:

SELECT * FROM IpClass;

When a user is "deleted" from Yellowfin, the 'EndDate' value is set to the day prior to deletion. This effectively removes their access from Yellowfin. Take note of the 'IpId' of your user and issue:

UPDATE IpClass SET EndDate='9999-12-31' WHERE IpId=xxxx; 
UPDATE IpRltshp SET EndDate='9999-12-31' WHERE IpChild=xxxx;
UPDATE StaffMemberRole SET EndDate='9999-12-31' WHERE IpEmployee=xxxx;

where xxxx is the 'IpId' listed above for the user in question. You'll want to ensure that the password for this user account is the SAME as the one set in LDAP.

Now convert them to an LDAP User:

UPDATE Person SET PrivateKeyId='LDAP' WHERE IpPerson=xxxx;

If the password set for the Yellowfin user account is the same as that in LDAP, you can now log in as this user. This is the supported method in converting an existing user to LDAP. For users you've already deleted from the IpClass table, you should be able to just re-create the users then convert them to LDAP users via the steps listed above as well.

Please give this a go and let me know your results!

Thanks,

Mike

Regards,

Mike

Reply URL

1

Guest ●

We finally opted for the option to reset the database and first after installation enable LDAP before users are logged in. This fixed the issue for now.

JeRoen

Reply URL

1

Mike Sheehan ●

Hi JeRoen,

Thanks for letting us know. Glad to hear this is sorted for now. This considered, I'll go ahead and close this case out, but if you a similar issue re-appears, please don't hesitate to reach back out in this ticket and I'll be happy to assist further.

Regards,

Mike

Regards,

Mike

Reply URL

removed users are still in the database

Replies (6)