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.


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://

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 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).

drop_unmanaged_memsql_users: true # --drop-unmanaged-memsql-users
- 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
- ldap:// # --uris ldap://
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: "" # --kerberos-realm ""
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.

sdb-admin sync-ldap
--uris ldap://
--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.

drop_unmanaged_memsql_users: false
- Medical
schema: active-directory
show_detail: false
- ldap://
start_tls: false
ca_paths: []
user: CN=Some User,CN=Users,DC=memsql,DC=ldap,DC=testing
credentials: password
base: dc=memsql,dc=ldap,dc=testing
filter: (&(objectClass=*))
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
resource_pool: ""
failed_login_attempts_limit: 0
password_lock_time: 0
auth_method: kerberos
pam_auth_service: ""
kerberos_realm: ""
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,
dn: uid=adam,dc=example,dc=org
objectClass: posixAccount
uid: adam

...and an LDAP group entry:

# dbagrp,
dn: cn=dbagrp,dc=example,dc=org
objectClass: posixGroup
cn: dbagrp
memberUid: user

...a detail configuration will resemble:

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.

sdb-admin sync-users
--ldap-uri "ldap://"
--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-version 3
--failed-login-attempts 3
--password-lock-time 300
--authentication-method "PAM"
--authentication-service "pam_serice"
--user "root"
--password <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). The following command is used to create each new SingleStore user.

IDENTIFIED WITH authentication_pam AS 'pam_service'

Last modified: April 26, 2023

Was this article helpful?