Problem with empty LDAP-SearchResults when using Active Directory

[FSeifer]

Hello oxzi,

I don't know if you even want to invest time into this but I thought id give it a shot.

In my company we recently decided to force users to authenticate prior to joining a BBB-Room. To this end we need to keep the LDAP user accounts up to date. I was dismayed to find out that BBB STILL does not contain this feature out of the box. So I started digging around a bit and stumble upon your plugin.

I was excited because it does exactly what I need it to do. Unfortunatly it isnt working quiet right.

After I successfully build the image and started the greenlight docker container, i saw that the LDAP synch was starting and it was looking for a valid user with an ldap-bbb-account. However instead of recieving this users LDAP-Attributes for syncing, it recieves an empty response...

level=error msg="Failed to query LDAP user" error="expected exactly one LDAP response, got 0" user="CN=First Last,OU=Users,OU=Migration,DC=company,DC=de"

Now I KNOW the LDAP-Connection with the configured BIND_DN, BASE_DN and Password is working because greenlight can query our LDAP for new users just fine.

I am not sure whats going wrong here and I am a bit desperate because the only other option i have would be to write a synch script myself.

If you have any idea, what I could do to get your plugin to work I would be very grateful indeed.

With kind regards. Florian

[oxzi]

Hello Florian,

Thanks for giving this little tool a chance and reaching out to me because it doesn't work as intended.

After I successfully build the image and started the greenlight docker container, i saw that the LDAP synch was starting and it was looking for a valid user with an ldap-bbb-account. However instead of recieving this users LDAP-Attributes for syncing, it recieves an empty response...

level=error msg="Failed to query LDAP user" error="expected exactly one LDAP response, got 0" user="CN=First Last,OU=Users,OU=Migration,DC=company,DC=de"

Now I KNOW the LDAP-Connection with the configured BIND_DN, BASE_DN and Password is working because greenlight can query our LDAP for new users just fine.

First, the good news: the LDAP connection itself is working fine.

This error appears after the connection was established and indicates that a user from the BBB Greenlight's PostgreSQL database cannot be found within the LDAP. https://github.com/oxzi/greenlight-ldap-sync/blob/da1b34ddddfb19231a62c450196deaa483428ba6/main.go#L64-L68

In more detail, this error appears during the LDAP search, resulting in an empty return. Those environment variables are from Greenlight's .env file. https://github.com/oxzi/greenlight-ldap-sync/blob/da1b34ddddfb19231a62c450196deaa483428ba6/ldap.go#L132-L138

I am a bit confused about the user field within the error message:

user="CN=First Last,OU=Users,OU=Migration,DC=company,DC=de"

The user should contain the PostgreSQL's social_uid field, which at least on my reference instance, is not an LDAP query, but the user's name. https://github.com/oxzi/greenlight-ldap-sync/blob/da1b34ddddfb19231a62c450196deaa483428ba6/db.go#L62-L69

To verify this, you could try the following query within the PostgreSQL:

greenlight_production=# select social_uid from users;

This brings me to the point of reproducibility. The following are my redacted config files, which you can take as an inspiration. However, without a censored form of your configs, it's hard for me to help you.

# docker-compose.yml

version: '3'

services:
  # [. . .]
  ldap-sync:
    build:
      context: ./greenlight-ldap-sync
    env_file: .env
    environment:
    # - SYNC_DEBUG=on   # You might wanna enable this.
      - SYNC_INTERVAL=30m
    restart: unless-stopped
    links:
      - db  # This is the PostgreSQL within another container, named db.
    network_mode: bridge
    logging:
      driver: "journald"

# .env

LDAP_SERVER=ldap.example.com  # CHANGED
LDAP_PORT=636
LDAP_METHOD=ssl
LDAP_UID=uid
LDAP_BASE=dc=example,dc=com  # CHANGED
LDAP_BIND_DN=cn=bbb,dc=example,dc=com  # CHANGED
LDAP_AUTH=simple
LDAP_PASSWORD=insecure  # CHANGED
LDAP_ROLE_FIELD=
LDAP_FILTER=
LDAP_ATTRIBUTE_MAPPING=name=displayName;uid=uid;

Please pay special attention to LDAP_FILTER and LDAP_UID, because they go directly into the query, as you can see in the code snippet above.

Further I would advise you to set the environment variable SYNC_DEBUG for debugging, which increases the log level, even if this probably makes no difference on your code path.

Please feel free to report back!

Best, Alvar

[FSeifer]

Hello Alvar,

thank you for replying on such short notice.

This is the redacted LDAP-Config Part of my .env file

Note that after your response I added the LDAP_FILTER value for uid. I thought setting LDAP_UID to samaccountname was sufficant.

LDAP_SERVER=ldap-server.fqdn.de
LDAP_PORT=636
LDAP_METHOD=ssl
LDAP_UID=sAMAccountName
LDAP_BASE=ou=Users,ou=Migration,dc=company,dc=de
LDAP_BIND_DN=cn=f.ad-read,ou=Funktionskonten,ou=company,dc=company,dc=de
LDAP_AUTH=simple
LDAP_PASSWORD=*************
LDAP_ROLE_FIELD=
LDAP_FILTER=
LDAP_ATTRIBUTE_MAPPING=uid=sAMAccountName;name=extensionAttribute8

I activated the debug log and the plugin seems to try to do what i want:

ldap-sync_1  | level=debug msg="Fetched users from SQL" amount=2
ldap-sync_1  | level=debug msg="Updated attribute map based on LDAP_ATTRIBUTE_MAPPING" key=uid values="[sAMAccountName dn]"
ldap-sync_1  | level=debug msg="Updated attribute map based on LDAP_ATTRIBUTE_MAPPING" key=name values="[extensionAttribute8 cn displayName]"
ldap-sync_1  | level=error msg="Failed to query LDAP user" error="expected exactly one LDAP response, got 0" user="CN=User Name,OU=Users,OU=Migration,DC=company,DC=de"

[FSeifer]

Hello Alva, I think I figured it out. Although the explanation leaves me with an even bigger problem.

It seems that, since I did not specify an LDAP_ATTRIBUTE_MAPPING for uid to samaccountname, all my Users were created with an internal uid, that you script does not find in our LDAP-Directory.

If I activate the MAPPING, the script runs fine for newly created users, the user is created from its LDAP-Entry and it is updated when the entry changes.

However, none of the old users, that were created before the change can now log into the server, because the login results in a different UID, so the DB-Object cant be found.

But thats not your problem to deal with. ;)

Thank you for your support and for pointing me in the right direction.

Kind Regards Florian

[oxzi]

Note that after your response I added the LDAP_FILTER value for uid. I thought setting LDAP_UID to samaccountname was sufficant.

You have meant LDAP_ATTRIBUTE_MAPPING instead of LDAP_FILTER, right? Please have a look at the Greenlight's LDAP documentation, especially the "LDAP_ATTRIBUTE_MAPPING" subsection. Without the new mapping uid=sAMAccountName, the LDAP dc (Distinguished Name) will be used.

I am not very familiar with the Active Directory, but sAMAccountName should contain the user's login name in its short form. However, as written in my previous post, your user within Greenlight's PostgreSQL database is identified by its LDAP Distinguished Name.

Now I'm seeing two possible solutions:

1. The project's code needs to be adjusted to work with LDAP DCs.
2. You recreate your PostgreSQL database based on this new settings.

Because I'm lazy, I'd go with the second choice. Based on your posted log, there are only two users in your Greenlight PostgreSQL database. After restarting Greenlight with the new .env configuration, you shouldn't be able to login anymore. Thus, feel free to drop and recreate the DB and give it another shot.

Best, Alvar

[oxzi]

Hi Florian,

seems like I was lust a little bit too late. I am very happy you the solution by yourself!

You might even be able to address the issue raised in your second post with the proposed solution in my other comment. Feel free to comment if you could solve it with this software and feel even freer to document your way of solving this issue for others to come.

Best, Alvar

[FSeifer]

Hi,

based on your answer, i have one more question. Do you know how the UID is generated?

My problem is that we are talking about a production environment with hundreds of users, rooms, recordings etc. So I cant very well go and delte the whole Database.

How are the uids generated, do you know? I was thinking if it was possible to predict a uid generated from a samaccountname and update my Database, so that the users can still log into their old accounts.

Greetings Florian

[oxzi]

based on your answer, i have one more question. Do you know how the UID is generated?

There might be the issue that there isn't just one username and/or UID. If you're talking about the LDAP authentication, the user entry within the PostgreSQL's users table must have the provider column set to the string ldap and than the value of the social_uid column will be checked if it equals the configured LDAP_UID within the LDAP server.

For more information, check out:

• Greenlight's LDAP documentation again - especially with an eye on LDAP_UID,
• and of course the code, e.g,:
  • https://github.com/bigbluebutton/greenlight/blob/c1573809d9583540107a4a04013158c2fd0aeebd/app/controllers/sessions_controller.rb#L134-L167 and
  • https://github.com/blindsidenetworks/bn-ldap-authentication/blob/0.1.4/lib/bn-ldap-authentication.rb

My problem is that we are talking about a production environment with hundreds of users, rooms, recordings etc. So I cant very well go and delte the whole Database.

Yeah.. I expected something like this.

How are the uids generated, do you know? I was thinking if it was possible to predict a uid generated from a samaccountname and update my Database, so that the users can still log into their old accounts.

Again, based on the documentation, the following mapping seems to be the relevant one:

Variable: nickname LDAP Attributes: uid, userid, sAMAccountName Greenlight User Attribute: username

Thus, maybe (!) the PostgreSQL's username column should already be equal to the AD's sAMAccountName and thus a simple SQL UPDATE to set the social_uid to username could be enough. However, as I don't have insides into your system, I can only guess at this point.

Best, Alvar

[FSeifer]

Hello again,

you, fine sir, just made my weekend!

it seems to be exactly as you suspected.

I checked the content of the column social_uid for an old and a new user and saw that they were different. The old entry contained a string akin to "CN=surname name OU=" etc. and the new user contained the sAMAccountName just like in the column "username" After changing the column value for the old users to samaccountname manually, these users could log into their accounts AND after 5 minutes the test value i added via AD-Console was synched to the DB-Object.

Solution

So heres the steps I took if someone is having similar troubles:

1. In the greenlight .env file LDAP config section: Make sure to not only set "LDAP_UID" to the attribute you want to use to identify users but ALSO Set LDAP_ATTRIBUTE_MAPPING=uid to the same LDAP-value.
   While the initial LDAP-Login will work without it, otherwise the synch script wont find that user in you LDAP-Directory to compare the datasets. Think of it kind of like a reverse-lookup entry.

   LDAP_UID=sAMAccountName
   LDAP_BASE=ou=Users,ou=Migration,dc=company,dc=de
   LDAP_BIND_DN=cn=f.ad-read,ou=Funktionskonten,ou=company,dc=company,dc=de
   LDAP_AUTH=simple
   LDAP_PASSWORD=*********
   LDAP_ROLE_FIELD=
   LDAP_FILTER=
   LDAP_ATTRIBUTE_MAPPING=uid=sAMAccountName

2. In case you already had users logged into your BBB-Server via LDAP without the mapping active, they wont be able to login anymore because the mapping has changed slightly. You need to update the user table of your postgresql database "greenlight_production" by replacing the 'social_id' with the value from 'username' For me the correct command looked something like this: docker exec greenlight-v2 psql "postgresql://postgres:<DB_PASSWORD>@db:5432/greenlight_production" -c "update users set social_uid = username where provider='ldap';"

After doing this your users should A) Be able to login into their accounts again, with no loss of Rooms, Recordings etc. B) Be updated regularly from you LDAP-Server in whatever interval you specified.

Big shout out to Mr. Alva for being perfectly nice and helpful on a moments notice on a friday!

Having averted a medium catastrophy with my production environment, im gonna go find a beer now.

Cheers and have a nice weekend!

Florian