Active Directory users and Addressbook

Mgr_Peter_Tuharsky_M 2018-10-23 08:27:04 UTC #1

Hi,

we’ve been using EGroupware with LDAP backend. I can say it worked well, and even in Addressbook app, we could see Accounts addressbook, where all users have their contact informations read from domain.

Now we are trying to migrate to AD. The authentication service works well, however user cannot see any other users, even in Calendar (adding participants), nor in Addressbook or elsewhere. What could have gone wrong? We have explicitely set user context (ou=Accounts,ou=… etc ) in Setup - Config, because it is different from AD default.

We don’t use administrator user for AD.

Searching in Apache2 error logs, I see strange error:
PHP Warning: file_exists(): open_basedir restriction in effect. File(/usr/sbin/univention-directory-manager) is not within the allowed path(s): (/usr/share/egroupware:/var/lib/egroupware:/tmp:/usr/lib/ssl/certs:/usr/share/ca-certificates) in /usr/share/egroupware/api/src/Accounts/Univention.php on line 256, referer: https://10.2.2.5/egroupware/setup/index.php

I don’t know, whether this has anything to do with accounts problem. There is no such file as univention-directory-manager on the disk (Debian 9 installation) and I cannot find such file in package repository. EGw Setup - Installation Check dosen’t show any warning expect the PGSQL, that we don’t use anyhow.

RalfBecker 2018-10-23 14:55:48 UTC #2

Starting with the end first

This has nothing to to with AD, setup tries to check if you are on an Univention system (a Debian based Distribution www.univention.de). You can savely ignore this PHP Warning.

About the AD problem:
It is not a general AD problem, you should be able to see the accounts addressbook and select accounts eg. in calendar.

Have set both, authentication AND account repository, to ActiveDirectory?

As we use RID (last part of SID) as numeric user-id (account_id) in AD, you have to run a migration to adapt your existing EGroupware database to it:

github.com

EGroupware/egroupware/blob/master/doc/ad-migration.sh

#!/bin/bash
#
# Usage: ./ad-migration.sh < user.csv | bash
#
# STDIN is csv with: <account_lid>,<account_id>,<AD-user>,<SID>,<RID>
#
# <account_id> has to be negative number for groups (eg. -123 for account_id=123)!
#
# Following command should be used as startpoint for the list:
# mysql -B -e "select account_lid,CASE account_type WHEN 'u' THEN account_id ELSE -account_id END,account_lid FROM egw_accounts ORDER BY account_type!='u',account_lid" egroupware | sed 's/  /,/g'
# Change <AD-user> if not equal to <account_lid> and append ,,<RID> to each line.
#
# If migration to LDAP instead of AD use uidNumber (gidNumber for groups) as <RID>.
# <SID> is NOT used and can be empty.
#
# Change following 2 lines to an EGroupware user with admin rights and his password
#
ADMIN=sysop
PASSWD=PW

This file has been truncated. show original

Missing the migration step probably creates all sorts of unpredictable effects.

Ralf

Mgr_Peter_Tuharsky_M 2018-10-24 08:09:35 UTC #3

Thank You for Your reply, Ralf.

I’m not migrating now, I’m just testing a BLANK installation, in order to test integration with AD. I’m aware I must do the uid->rid conversion in further process.

I have account repository set to SQL now. I hope it was the preferred way before…?

If I set both authentication AND accounts to AD, the Setup tool immediately shines red in Main Menu point 3 - it demands me to set administrator account (eve if it was created in SQL mode before). Either I attempt to use existing AD account (equivalent with the SQL one), or to create new, it ends up with “error in group creation”. What is worse, even user logins stop working claiming that user account is expired.

Here are my settings:

Mgr_Peter_Tuharsky_M 2018-10-24 09:09:50 UTC #4

After attempt to create admin account, there are some errors in error.log:
errorlog.txt (20.8 KB)

I don’t understand some things:

/usr/share/egroupware/api/src/Accounts/Ads.php(1623): ldap_add(Resource id #7, ‘CN=Default,OU=A…’, Array), referer: https://10.2.2.5/egroupware/setup/admin_account.php

Why it attempts to create CN=Default, when there is no such thing in config? (Default group is set to be EGw-Default, as You can see). Why is it attempting to create it in context of Accounts?
And above all, why is it attempting to create ANYTHING? Is it mandatory to allow EGw to create objects in AD? Is it necessarry for config only, or is it necessarry even later for the normal operation?

Mgr_Peter_Tuharsky_M 2018-10-24 13:27:27 UTC #5

By the way, Ralf, maybe I misunderstand the script, however when I open the database in Workbench and look at the table egw_accounts, I see only SQL users (admin, demo1, 2, 3, Default group etc) there. Does the script (or other script) also convert EGw LDAP users to AD users?

RalfBecker 2018-10-24 16:13:14 UTC #6

The migration script does NOT move accounts from one account repository to an other.
That can be done, but this is part of Setup.

The migration script takes a csv file. You can use eg. the output of an SQL query or LDAP search to produce it.
And then manually fill the numeric IDs of the system you want to migrate to.

It’s important to note: you really need to list all your accounts AND groups in that CSV file, or delete them from EGroupware before you start the migration. Everything else will gibe you a lot of pain, because not migrated IDs might conflict with new IDs and generate SQL errors.

Anyway I can and will only give pointers for AD migration here. Buy a couple of hours support budget and I’m happy to assist in the migration process.

Ralf

Mgr_Peter_Tuharsky_M 2018-10-25 06:03:22 UTC #7

Hi, Ralf.
I know the migration of users itself is out of scope, and we have already done it. I’m asking about means to transform the EGroupware database records, so that the AD users would keep ownership etc of items that belonged to them in the times when the accounts were stored in LDAP.
Some time ago You said, that it should be done with admin-cli.php. Is it still the preferred way to go?

RalfBecker 2018-10-25 06:24:55 UTC #8

Not out of scope, but done by a different script.

Yes it is, the ad-migration.sh script writes admin-cli.php commands you have to execute.
All re-numbering have to be done in a single admin-cli.php command, to be able to perform cyclic changes (n becomes m and m become n).

Ralf

Mgr_Peter_Tuharsky_M 2018-10-25 16:07:32 UTC #9

Thank You, Ralf.

Btw, I still mess with the setup.
If only auth is set to AD, and accounts are in SQL, it works well (expect, no other users available as I said originally).

But when I set the AD for both auth and accounts storage, different story starts. Since the original (SQL-based) admin account is nonfunctional now, I go to step 3 - Create admin account. I use the same account name. It creates the admin in the accounts repository in AD, as specified in Configuration. It also creates groups Admin and Default there too (in the accounts pool). This is not nice, we have different pool for groups. The DN for groups should be customizable, as it is with LDAP.

But the worst thing is, that now the admin account and even AD users can login, but they (admin too) see blank Egroupware just with horizontal and vertical line, buttons on right top (+, print, logoff) and EGw logo on left. No applications, no home page, nothing. I checked in AD whether the domain users and the egw admin are members of group Default; yes, they are. So why they don’t see any apps?

I think the documentation is too sparse on the topics. For example, it is nowhere stated that switching to AD accounts disables local SQL accounts automatically; with LDAP I feel they still worked. I also miss clear statement that the master EGw AD Account must have write permission to create both users and accounts in AD (this is stated to be optional), because it MUST be able to create Admin and Default groups.

Once I get through, I feel I should write some documentation…

RalfBecker 2018-10-25 17:45:31 UTC #10

Because it is only authentication, no migration is required.

Sure that would help, other approach is to create these groups in AD directly.

This is because you have not done the migration!

ACL rights are stored in the SQL database with the your old LDAP uidNumbers, not the RID. That is were the migration is for.

No they don’t! There is only one active account repository in EGroupware, which is either SQL, or LDAP or AD. Never 2 together. Only thing you can do is authentication with multiple ones and automatic creation of authenticated users, if they don’t exist in the account repository.

My recommendation for all migrations is: get some support budget and do it together with us or at least plan it together with us.

Ralf

Mgr_Peter_Tuharsky_M 2018-10-26 06:55:50 UTC #11

Ralf, thank You for Your input.
I think we are still in misunderstating in that I don’t attempt to migrate from AD for NOW; that step I will take later. The problems I encounter are with fresh, NEW installation with NO LEGACY DATA.
I will try some things more today and give feedback of course. And if I succeed, I will make a summary for other users.

Mgr_Peter_Tuharsky_M 2018-10-26 08:09:07 UTC #12

For the record, I found some problem in Step3 - Create Admin Account.
Writing this because it might help others with similar problems.

Fresh EGw installation, setup page. Both AUTH and ACCOUNTS store are set to AD in the step 2 - Config.
I proceed to step 3 - Create admin account. The step ends up with an “Error creating admin user”. Checking the logs, seems that the process is not completed because it cannot set the password for the newly generated egw admin (file Accounts/Ads.php line 1447 - “Server is unwilling to perform”).

I checked that the account admin user (specified in step 2 - Config ) has full access to the OU (that is set in step 2 - Config) and descendant objects, and it DOES have Reset password privilege, so I see no obvious reason. The password is strong and meets the domain requirements, and the error log dosen’t indicate any problem with password itself. Might be an effect of some other security policy, I don’t know.

Because of the error, the egw admin account is created but disabled. Thus, login is impossible. I reset the password manually in on AD DC and enable the account.

The groups Admins and Default are also created, but are empty. So I manually add the admin account to them.

However, this dosen’t help so far - the admin can log in now, but has empty Egroupware with no apps and no options whatsoever.

Looking into admin_account.php, it seems for me (not being a PHP expert, though) that the process of creating admin user only proceeds to granting EGw application access after SUCCESSFULLY accomplishing all the previous steps.
If this is the case, it is not correct in my opinion - if the groups are created, the access should be granted anyway. If there is a problem with setting password to admin account, it should say it but it shouldn’t prevent from granting access to groups! And it shouldn’t prevent from adding the admin user to these groups either!

I think so because the problems with admin account can be quite easily resolved on the side of AD DC. But problems in application access require either tackling with EGw database at low level, or modifying the PHP code; neither of these are user-friendly steps.

RalfBecker 2018-10-26 09:50:15 UTC #13

That problem is caused by not having any ACL rights in EGroupware (for the RIDs used in your AD).

You can fix that, with the admin account creation in Setup, given it succeeds. You should in that case check the box to delete all existing ACL data and accounts.

Most of the time that means your password does not conform to the password requirements of your AD.

Creating an empty instance has the same problems as migrating one, the numerical account-ids in the database need to be adapted, so that eg. the ACL rights (incl. application run rights) can work again.

Ralf

Mgr_Peter_Tuharsky_M 2018-12-28 10:14:34 UTC #14

The ad-migration script says that it needs CSV file with:
account_lid,account_id,AD-user,SID,RID

Looking into EGroupware database, egw_accounts table, there seem to be only local users and groups: Admins, Default, NoGroup, anonymous, demo, demo2, demo3, admin.

I assume, that I must simply assign them corresponding AD users and groups (the AD-user,SID,RID fields) in the CSV.

But all other users and groups, that are until now stored in LDAP, are not present in egw_accounts table. I thus assume, that they do NOT have any account_lid, and that account_id is their uidNumber from LDAP…? Is that correct?

RalfBecker 2018-12-28 17:40:44 UTC #15

If you migrate from LDAP to AD, you only need to consider the LDAP users (matching your search query, if you set one). egw_accounts SQL table is not used for accounts stored in LDAP (not just authentication against LDAP!).

Ralf

Mgr_Peter_Tuharsky_M 2019-01-08 07:46:10 UTC #16

I understand, I just thought that EGW might use some internal accounts despite LDAP is set as account repository.

So, account_lid is completely unused with LDAP->AD migration, correct?

RalfBecker 2019-01-08 08:44:04 UTC #17

account_lid is used, if different, to rename the home directory in EGroupware.

Ralf

Mgr_Peter_Tuharsky_M 2019-01-10 10:53:20 UTC #18

I’m not sure I understand it correctly.

Doing LDAP -> AD migration using the ad-migration.sh should be sufficient for users and groups to gain access to whatever they have had before? Because it simply rewrites the old UIDs to new RIDs in EGw DB and everything is as before fo them?

Or, is there still necessity to go to 3. Create admin user, and only if that succeeds, then the AD integration starts working?

Because I have done the migration LDAP -> AD with script. I changed 2.Configuration in Setup so that accounts and auth now go the AD way. The admin user is already manually created and also entered in Setup 2.Configuration, so that the Setup dosen’t complain about that. The users now can login using AD accounts, but still they have no apps available.
And yes, I did the enumerate gidnumbers for groups with - in the CSV so I assume everything should be migrated correctly.

So this leaves me with two options: Either I don’t have AD groups working with EGW for some reason, or the 3.Create admin is still a necessity.

RalfBecker 2019-01-11 08:24:14 UTC #19

Hi Peter,

the Step 3. Create admin user is NOT necessary. The migration rewrites the ACL including the run rights, to existing users keep their rights.

You can check that be looking at egw_acl table, it should contain eg. run rights in the form of:
acl_appname: (internal) name of application, eg. "addressbook"
acl_location: “run” for run-rights
acl_account: account_id of user (negative for groups)
acl_rights: 1 for run-rights
So the acl_account column should contain RIDs from your AD, after the migration.

My recommendation with AD migrations is usually: by some support budget and lets plan and execute it together.

Ralf

Mgr_Peter_Tuharsky_M 2019-01-11 08:54:22 UTC #20

Yep, this is also my recommendation