# Using the LDAP Tool To periodically sync LDAP directory users and groups with SingleStore, run the LDAP tool using common Linux scheduling tools, such as by creating a `cron` job. You can also manually run `sdb-admin sync-ldap` on the Linux command line by specifying the required options or using a YAML configuration file. SingleStore recommends using a configuration file as it provides an easier way to input the necessary parameters. ## Prerequisites The SingleStore user who runs this tool must be authorized to obtain user and group information from the LDAP directory server. * A user can connect to the cluster using the `user` and `password` parameters. * If a user or password is not specified, Toolbox assumes the database "root" user and uses a secure, hashed password. If a non-root user is specified and a password is not set, Toolbox prompts for a password; an error is thrown if using the LDAP tool in non-interactive mode. * To keep your SingleStore user password secure, set the `MEMSQL_PASSWORD` environment variable in the Linux shell. ## Required Configuration Parameters If you choose to run the `sdb-admin sync-ldap` command manually without a configuration file, you must specify the following command-line options. `--auth-method` - Authentication method to authenticate new SingleStore users that are created from the LDAP sync. Supported authentication methods include Kerberos, PAM, SAML, and JWT. Only the authentication methods that are preconfigured in your cluster can be specified for this option. Depending on the authentication method used, you may need to include additional options and/or values. For example, `--kerberos-realm` must only be used with the Kerberos authentication method, whereas `--saml-user-domain-attribute` only applies to SAML authentication. `--bind-credentials` string - Credentials of the LDAP bind user to authenticate. To keep your bind credentials secure, use the `LDAP_BIND_CREDENTIALS` environment variable in the Linux shell. Alternatively, a configuration file may be used. `--bind-user` string - DN/username of the LDAP bind user to authenticate. For example, `cn=admin,dc=example,dc=org`. `--groups` string - User group(s) names to sync. Group names must be separated by commas. For example, `Medical,Engineering`. Only the groups listed may be granted or revoked. Note that this option must contain non-empty value(s) for the command to successfully run. `--schema` - LDAP Schema that specifies the structure of user and group entries. Currently, supported schemas include `active-directory`, `open-ldap`, and `unspecified`. `--search-base` string - LDAP Base DN that specifies the base of the subtree in which the search is to be constrained for a search object. For example, `dc=example,dc=org`. `--uris` string - URI(s) of the LDAP directory server(s). Multiple values are separated by commas. For example, `ldap://172.17.0.2`. In addition to the options listed above, the `sdb-admin sync-ldap` command supports several optional flags. For more information, refer to the [sdb-admin sync-ldap](https://docs.singlestore.com/db/v9.1/reference/singlestore-tools-reference/sdb-admin-commands/sync-ldap.md) reference. ## Using YAML Configuration File As an alternative to specifying options on the command-line, you can use a YAML configuration file and pass it to the `sdb-admin sync-ldap` command using the `--config-file` option. Below is a mapping of the configuration file fields with the appropriate command-line options, along with information about each field. Note that the configuration file is structured by logical entities (e.g., `ldap_client`, `bind`). ```yaml drop_unmanaged_memsql_users: true # --drop-unmanaged-memsql-users groups: - Medical # --groups Medical schema: active-directory # --schema active-directory show_detail: true # --show-detail ldap_client: # ldap_client is the config of the LDAP client uris: - ldap://52.59.219.12 # --uris ldap://52.59.219.12 start_tls: true # --start-tls ca_paths: ["../ldap-sync-config.yaml"] # --ca-paths "../ldap-sync-config.yaml" bind: # bind specifies how to bind to the LDAP server user: CN=Some User,CN=Users,DC=memsql,DC=ldap,DC=testing # --bind-user "CN=Some User,CN=Users,DC=memsql,DC=ldap,DC=testing" credentials: password # --bind-credentials password search: # search specifies the ldapsearch details base: dc=memsql,dc=ldap,dc=testing # --search-base "dc=memsql,dc=ldap,dc=testing" filter: (&(objectClass=*)) # --query-filter "(&(objectClass=*))" detail: # detail specifies the structure of the user and group ldap entries user_object_class: user # --user-object-class user group_object_class: group # --group-object-class group user_attribute: sAMAccountName # --user-attribute sAMAccountName group_attribute: sAMAccountName # --group-attribute sAMAccountName user_member_of_attribute: memberOf # --user-member-of-attribute memberOf group_members_attribute: member # --group-members-attribute member user_principal_name_attribute: userPrincipalName # --user-principal-name-attribute userPrincipalName sql_user: # sql_user specifies how new SQL users are created resource_pool: "pool" # --resource-pool pool failed_login_attempts_limit: 1 # --failed-login-attempts-limit 1 password_lock_time: 10 # --password-lock-time 10 auth_method: kerberos # --auth-method kerberos pam_auth_service: "" # --pam-auth-service "" kerberos_realm: "example.org" # --kerberos-realm "example.org" sql_client: # sql_client specifies user and password to the SQL user which runs SQL commands user: root password: null ``` ## Example 1 The following example covers all of the required options for the `sdb-admin sync-ldap` command. ```shell sdb-admin sync-ldap --uris ldap://52.59.219.12 --groups Medical --search-base dc=memsql,dc=ldap,dc=testing --auth-method kerberos --schema active-directory --bind-user "CN=Some User,CN=Users,DC=memsql,DC=ldap,DC=testing" --bind-credentials password ``` The following YAML-based configuration file is equivalent to the example with mandatory command-line options. ```yaml drop_unmanaged_memsql_users: false groups: - Medical schema: active-directory show_detail: false ldap_client: uris: - ldap://52.59.219.12 start_tls: false ca_paths: [] bind: user: CN=Some User,CN=Users,DC=memsql,DC=ldap,DC=testing credentials: password search: base: dc=memsql,dc=ldap,dc=testing filter: (&(objectClass=*)) detail: user_object_class: user group_object_class: group user_attribute: sAMAccountName group_attribute: sAMAccountName user_member_of_attribute: memberOf group_members_attribute: member user_principal_name_attribute: userPrincipalName sql_user: resource_pool: "" failed_login_attempts_limit: 0 password_lock_time: 0 auth_method: kerberos pam_auth_service: "" kerberos_realm: "" sql_client: user: root password: null ``` Note the use of the additional fields that may also be specified in the configuration file, such as `ca_paths` and the `detail` struct. For custom LDAP implementations, use either the `detail` struct in the config file or the appropriate options on the command line. The `detail` struct describes the structure of an LDAP entry. Either the `schema` field or the `--schema` option can be used to define the required details. Note that these details can also be overridden. However, you should only override the `detail` struct if the required schema is unsupported. For example, given an LDAP user entry: ``` # adam, example.org dn: uid=adam,dc=example,dc=org objectClass: posixAccount uid: adam ``` ...and an LDAP group entry: ``` # dbagrp, example.org dn: cn=dbagrp,dc=example,dc=org objectClass: posixGroup cn: dbagrp memberUid: user ``` ...a detail configuration will resemble: ``` ldap_client: search: detail: user_object_class: posixAccount group_object_class: posixGroup user_attribute: uid group_attribute: cn user_member_of_attribute: "" group_members_attribute: memberUid user_principal_name_attribute: "" ``` ## Example 2 You can configure a `cron` job to sync Active Directory (AD) users and groups with SingleStore every 5 minutes. The command in the `cron` job would resemble the following. ```shell sdb-admin sync-users --ldap-uri "ldap://www.Company1.com:389" --ldap-search-base "ou=Engineers,dc=Company1,dc=com" --ldap-bind-method "Simple" --ldap-bind-user john.smith --ldap-bind-credentials "qkwhe123jk23jhe" --ldap-result-attribute "samAccountName" --ldap-user-groups "DentalCSR,MedicalCSR" --ldap-start-tls --ldap-version 3 --failed-login-attempts 3 --password-lock-time 300 --authentication-method "PAM" --authentication-service "pam_serice" --user "root" --password ``` In the above example, users and groups in the DentalCSR and MedicalCSR AD user groups are synced from the LDAP server, which is identified by the LDAP server connection string (specified by `--ldap-uri`). The LDAP tool connects to the SingleStore cluster with the "root" user and the password specified. The user names synced will be returned in the `samAccountName` field (For more information, refer to [Processing LDAP Search Results](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/singlestore-user-management/ldap-user-sync/process-ldap-search-results.md)). The following command is used to create each new SingleStore user. ```sql CREATE USER user@% IDENTIFIED WITH authentication_pam AS 'pam_service' WITH FAILED_LOGIN_ATTEMPTS = 3 PASSWORD_LOCK_TIME = 300; ``` *** Modified at: April 26, 2023 Source: [/db/v9.1/user-and-cluster-administration/singlestore-user-management/ldap-user-sync/use-ldap-tool/](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/singlestore-user-management/ldap-user-sync/use-ldap-tool/) (An index of the documentation is available at /llms.txt)