Active Directory users and Addressbook

[Mgr_Peter_Tuharsky_M]

Hi, Ralf. Please, what admin account does the ad_migration script use? There are several admin accounts for EGw (header, config, db etc…), I try all of them, but no one seems working.

[RalfBecker]

Any EGroupware user with rights to the admin app.

Ralf

[pvagner]

Hello,

I’m looking into the same thing.
In the CSV we do have entries like this where existing acl_account ID is used for another user after the migration

,10764,user.name1,S-1-5-21-239752359-961613262-1525784148-1676,1676
,10439,user.name2,S-1-5-21-239752359-961613262-1525784148-10764,10764

admin/admin-cli.php ends unexpectedly with sql error like this:

Invalid SQL: UPDATE egw_acl SET acl_account= CASE acl_account WHEN -10015 THEN -515 WHEN -10012 THEN -512 WHEN -10013 
... snip... 
WHEN 10754 THEN 1672 WHEN 10718 THEN 1673 WHEN 10211 THEN 1674 WHEN 10491 THEN 1675 WHEN 10764 THEN 1676 
... SNIP ...
WHEN 10439 THEN 10764 WHEN 10618 THEN 10765 
... SNIP ...
WHEN 10501 THEN 10689 END WHERE acl_account IN (-10015,-10012,-10013,
... SNIP ...
10211,10491,10764,10215,
... SNIP ...
10909,10439,10618,
... SNIP ...
10415,10501)
Duplicate entry 'addressbook-run-10764' for key 'egw_acl_appname_location_account' (1062)

We have too many instances of this to comfortably handle it manually.
Reordering rows in the CSV file is not helping with this.
Deleting corresponding rows from the egw_acl table for no longer active users reduces number of its occurences but we still need to find out how to overcome this for the active users.

This looks to me either like a mysql configuration issue or like a bug.
Can you share some tips please?

Greetings

Peter

[RalfBecker]

This is mostly caused by “zombi” entries in ACL from accounts not correctly deleted through EGroupware.

There are other admin-cli commands to clean the database of them, before attempting the AD migration:

admin/admin-cli.php --check-acl admin-account[@domain],admin-password

Deletes ACL entries of not longer existing accounts (make a database backup before! --> setup-cli.php).

admin/admin-cli.php --admin-cmd-check-cats admin-account[@domain],admin-password

Deletes categories of not longer existing accounts.

Apart from the existing command, you can only try to find out what is causing it and then either fix it yourself, or get us something implemented to deal with the problems in your database, before migration.

Ralf

[pvagner]

Thank you Ralph for the pointers… I managed to sort out issues related to duplicate keys.

What I did

• By repeatedly running the script I have identified which tables had bogus data leading to key duplicate issues. For us these three tables were affected: egw_acl, egw_preferences and egw_cal_user.
• I have looked at their unique keys in the sql dump output and dropped them until the script performed its job. I have used SQL queries like this

ALTER TABLE egw_acl DROP INDEX egw_acl_appname_location_account;
ALTER TABLE egw_preferences DROP INDEX egw_preferences_preference_owner_preference_app;
ALTER TABLE egw_cal_user DROP INDEX egw_cal_user_id_recur_date_type_id;

• Finally executed the script as discussed earlier in this topic
• After the script was done I have tried to add the unique keys back like this

ALTER TABLE egw_acl ADD UNIQUE KEY `egw_acl_appname_location_account` (`acl_appname`,`acl_location`,`acl_account`);
ALTER TABLE egw_preferences ADD UNIQUE KEY `egw_preferences_preference_owner_preference_app` (`preference_owner`,`preference_app`);
ALTER TABLE egw_cal_user ADD UNIQUE KEY `egw_cal_user_id_recur_date_type_id` (`cal_id`,`cal_recur_date`,`cal_user_type`,`cal_user_id`);

• It was not straightforward as I have received errors like this…

Duplicate entry '10886-calendar' for key 'egw_preferences_preference_owner_preference_app' (1062)

• Then with the data from the error message I have queried for concrete rows like this

SELECT * FROM egw_preferences WHERE preference_owner=10886 AND preference_app='calendar';

• Alternativelly to look at all the duplicates that are preventing adding back unique keys I queried…

SELECT 
    preference_owner, COUNT(preference_owner),
    preference_app, COUNT(preference_app)
FROM
    egw_preferences
GROUP BY 
    preference_owner, 
    preference_app 
HAVING 
       (COUNT(preference_owner) > 1) AND 
       (COUNT(preference_app) > 1);

• And that’s it all pretty much. Of course I deleted duplicate records and reenabled those unique keys.

So thank you again for the great advices.

I have got another question though.
In the AD config there is a user context setting. that controls which OU AD users are searched inside. Is there a way or a trick so we can either configure different search base for users and different for groups or is there a way to specify multiple search bases so they are queried all?
The second approach would also help with use case where we have multiple locations in our AD setup.

Thank you

Greetings

Peter

[RalfBecker]

I have got another question though.

In the AD config there is a user context setting. that controls which OU AD users are searched inside. Is there a way or a trick so we can either configure different search base for users and different for groups or is there a way to specify multiple search bases so they are queried all?

The second approach would also help with use case where we have multiple locations in our AD setup.

Currently not, though some customers use the following approach:

1. set a search-base including all OU’s you need to search
2. create users via AD, where you can choose where to create a user

Ralf

[Mgr_Peter_Tuharsky_M]

It seems that duplicates mostly come from users that are missing in the migration CSV file, probably because as the script renumbers the users that are present in the CSV, sometimes the new ID of some user collides with the old ID of the other user that was not in table, thus was not renumbered.
This is just for info, that it REALLY matters to have all users in the migration CSV, even those that are no longer users of EGW and probably will never be.