> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Configure LDAP Authentication

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

<Note>
  **This feature is in <InternalLink path="cockroachdb-feature-availability">preview</InternalLink>** and subject to change. To share feedback and/or issues, contact [Support](https://support.cockroachlabs.com).
</Note>

CockroachDB supports authentication and authorization using systems compatible with the Lightweight Directory Access Protocol (LDAP), such as Active Directory and Microsoft Entra ID. This allows you to integrate your cluster with your organization's existing identity infrastructure for centralized user management and access control.

This page describes how to configure CockroachDB user authentication using LDAP. You can additionally configure CockroachDB to use the same directory service for user <InternalLink path="ldap-authorization">authorization</InternalLink> (role-based access control), which assigns CockroachDB roles to users based on their group memberships in the directory. For alternative SSO authentication methods, refer to <InternalLink path="sso-db-console">Single Sign-on (SSO) for DB Console</InternalLink> (OIDC).

## Overview

LDAP authentication in CockroachDB works with LDAP-compatible directory services, including Microsoft Entra ID, Active Directory, and OpenLDAP. Secure LDAPS connectivity over TLS is required.

While LDAP configuration is cluster-specific, each request to authenticate a user in CockroachDB is handled by the node that receives it. When LDAP is enabled, the node handles each authentication request using a "search and bind" approach:

1. Find the user record

* The node connects to the LDAP server using a dedicated directory access account.
* The node searches the directory for a record that matches the authenticating user, using configurable search criteria.

1. Authenticate the user

* If a matching record was found, the cluster attempts to verify the user's identity through another LDAP request, this time using the credentials (username and password) provided by that user.
* If this LDAP bind operation succeeds, the user is authenticated to the CockroachDB cluster.

1. Authorize the user (optional)

* If <InternalLink path="ldap-authorization">LDAP authorization</InternalLink> is also enabled, an additional request is sent to retrieve the groups to which the user is assigned, using configurable criteria.
* If group memberships are found, any existing CockroachDB roles that match these group names are assigned to the user.

These requests use a node's existing connection to the LDAP server, if one is open. Otherwise, the node establishes a new connection. The connection remains open for handling additional LDAP requests until it is closed by the LDAP server, based on its timeout setting.

Because CockroachDB maintains no more than one LDAP connection per node, for a cluster with `n` nodes, you can expect up to `n` concurrent LDAP connections.

<Note>
  LDAP authentication cannot be used for the `root` user or other <InternalLink path="security-reference/authorization#reserved-identities">reserved identities</InternalLink>. Credentials for `root` must be managed separately using password authentication to ensure continuous administrative access regardless of LDAP availability.
</Note>

## Configuration

### Prerequisites

* An LDAP-compatible directory service, such as Microsoft Entra ID or Active Directory.
* Network connectivity on port 636 for LDAPS.
* A service account (bind DN) with permissions to search the directory for basic information about users and groups. For example, in Microsoft Entra ID, a [service principal](https://learn.microsoft.com/entra/architecture/secure-service-accounts) with the Directory Readers role.
* The LDAP server's CA certificate, if using a custom CA not already trusted by the CockroachDB host.
* Verification that the attribute values that will become CockroachDB usernames meet the CockroachDB <InternalLink path="create-user#user-names">requirements for usernames</InternalLink>.

Before you begin, it may be useful to enable authentication logging, which can help you confirm successful configuration or troubleshoot issues. For details, refer to [Troubleshooting](#troubleshooting).

### Step 1: Enable redaction of sensitive cluster settings

For this integration, you will need to store LDAP bind credentials for the service account that enables the integration in the <InternalLink path="cluster-settings">cluster setting `server.host_based_authentication.configuration`</InternalLink>. You will also configure the mapping of external identities to CockroachDB SQL users with the <InternalLink path="cluster-settings">cluster setting `server.identity_map.configuration`</InternalLink>. In addition, for a custom CA configuration, you may need to store certificate and key details in the cluster settings specified in the optional [Step 3: Configure TLS](#step-3-configure-tls-optional).

It is highly recommended that you redact these settings, so that only authorized users, such as members of the `admin` role, can view them. To enable this redaction and learn about its permission scheme, refer to <InternalLink path="cluster-settings#sensitive-settings">Sensitive settings</InternalLink>.

### Step 2: Configure Host-Based Authentication (HBA)

To enable LDAP, you will need to update the <InternalLink path="security-reference/authentication#authentication-configuration">host-based authentication (HBA)</InternalLink> configuration specified in the <InternalLink path="cluster-settings">cluster setting `server.host_based_authentication.configuration`</InternalLink>.

Set the authentication method for all users and databases to `ldap` and include the LDAP-specific option parameters:

* `ldapserver`: LDAP server hostname
* `ldapport`: LDAP server port (typically 636 for LDAPS)
* `ldapbasedn`: Base DN for user searches
* `ldapbinddn`: Service account DN for directory searches
* `ldapbindpasswd`: Service account password
* `ldapsearchattribute`: Attribute to match against SQL usernames
* `ldapsearchfilter`: LDAP filter to restrict valid users

For example:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SET CLUSTER SETTING server.host_based_authentication.configuration = '
host    all    all    all    ldap    ldapserver=ldap.example.com
    ldapport=636
    "ldapbasedn=ou=users,dc=example,dc=com"
    "ldapbinddn=cn=readonly,dc=example,dc=com"
    ldapbindpasswd=readonly_password
    ldapsearchattribute=uid
    "ldapsearchfilter=(memberof=cn=cockroachdb_users,ou=groups,dc=example,dc=com)"';
```

If you also intend to configure LDAP Authorization, you will need to include an additional LDAP parameter, `ldapgrouplistfilter`. For details, refer to <InternalLink path="ldap-authorization#configuration">LDAP Authorization</InternalLink>.

### Step 3: Configure TLS (Optional)

If, for LDAPS, you are using a certificate signed by a custom Certificate Authority (CA) that is not in the system's trusted CA store, you will need to configure the CA certificate. This step is only necessary when using certificates signed by your organization's private CA or other untrusted CA.

**Set the custom CA certificate:**

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SET CLUSTER SETTING server.ldap_authentication.domain.custom_ca = '<PEM_ENCODED_CA_CERT>';
```

**Configure a client certificate for mTLS if required:**

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SET CLUSTER SETTING server.ldap_authentication.client.tls_certificate = '<PEM_ENCODED_CERT>';
SET CLUSTER SETTING server.ldap_authentication.client.tls_key = '<PEM_ENCODED_KEY>';
```

### Step 4: Configure user creation

CockroachDB supports two approaches for the creation of users who will authenticate via LDAP:

#### Option 1: Automatic user provisioning (recommended)

With automatic user provisioning, CockroachDB creates users automatically during their first successful LDAP authentication, whether connecting via a SQL client or the <InternalLink path="ui-overview">DB Console</InternalLink>. This eliminates the need for custom scripting to create user accounts.

When enabled:

* Users are created automatically upon successful LDAP authentication.
* All auto-provisioned users receive a `PROVISIONSRC` role option set to `ldap:{ldap_server}`.
* The `estimated_last_login_time` is tracked for auditing purposes.
* Auto-provisioned users cannot change their own passwords (managed via LDAP only).

For Active Directory deployments, the CockroachDB username will match the `sAMAccountName` field from the `user` object. Specify this field name with `ldapsearchattribute=sAMAccountName` in the HBA configuration. Ensure that the values in the field you are using for `ldapsearchattribute` meet the CockroachDB <InternalLink path="create-user#user-names">requirements for usernames</InternalLink>.

<Note>
  Before you enable automatic user provisioning, it is recommended that you enable <InternalLink path="ldap-authorization">LDAP authorization</InternalLink>. This ensures that upon initial login, new CockroachDB users are members of the intended CockroachDB roles, with the privileges they confer, according to users' group memberships in the directory. Otherwise, functionality may be limited for a new user until your alternative process applies roles or privileges.

  If you choose to manage CockroachDB role memberships and privileges directly, you could script the required <InternalLink path="grant">`GRANT`</InternalLink> commands to be <InternalLink path="cockroach-sql#general">executed</InternalLink> as needed.
</Note>

**To enable automatic user provisioning:**

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SET CLUSTER SETTING security.provisioning.ldap.enabled = true;
```

#### Option 2: Manual/scripted user creation

Alternatively, you can manage users by directly creating them before LDAP authentication is used. This approach provides explicit control over user creation.

To create a single user:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE ROLE username LOGIN;
```

To create users in bulk:

1. Export usernames from the directory server.

2. Produce a `.sql` file with a <InternalLink path="create-role">`CREATE ROLE`</InternalLink> statement per user, each on a separate line.

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   CREATE ROLE username1 LOGIN;
   CREATE ROLE username2 LOGIN;
   CREATE ROLE username3 LOGIN;
   ```

   If you are not also enabling LDAP Authorization to manage roles and privileges, you can also include one or more `GRANT` lines for each user. For example, `GRANT developer TO username1` or `GRANT SELECT ON DATABASE orders TO username2;`.

3. Run the SQL statements in the <InternalLink path="cockroach-sql#general">file</InternalLink>:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   cockroach sql --file=create_users.sql --host=<servername --port=<port --user=<user --database=<db --certs-dir=path/to/certs
   ```

To update users on an ongoing basis, you could script the required <InternalLink path="create-role">`CREATE ROLE`</InternalLink>, <InternalLink path="drop-role">`DROP ROLE`</InternalLink>, or <InternalLink path="grant">`GRANT`</InternalLink> commands to be <InternalLink path="cockroach-sql#general">executed</InternalLink> as needed. For example:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
cockroach sql --execute="DROP ROLE username1" --host=<servername --port=<port --user=<user --database=<db --certs-dir=path/to/certs
```

## Connect to a cluster using LDAP

### SQL shell connection with LDAP authentication

To connect using LDAP credentials, use your LDAP password:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
# Method 1: Password in environment variable
export PGPASSWORD='ldap_password'
cockroach sql --url "postgresql://username@host:26257" --certs-dir=certs

# Method 2: Password in connection string
cockroach sql --url "postgresql://username:ldap_password@host:26257" --certs-dir=certs
```

### DB Console connection with LDAP authentication

If LDAP authentication is configured, DB Console access will also use this configuration, allowing users to log in with their SQL username and LDAP password. During a login attempt, the system checks if LDAP authentication is configured for the user in the HBA configuration. If so, it validates the credentials against the LDAP server. If automatic user provisioning is enabled, users will be created automatically during their first successful login. If LDAP authentication fails or is not configured, the system falls back to password authentication.

<Tip>
  When <InternalLink path="ldap-authorization">LDAP authorization</InternalLink> is configured (with `ldapgrouplistfilter` in the HBA configuration), role-based access control is applied automatically upon login to both SQL clients and the DB Console. Users logging into the DB Console via LDAP will have their roles synchronized from LDAP groups, just as they are for SQL client connections.
</Tip>

## Manage auto-provisioned users

When automatic user provisioning is enabled, you can identify and manage auto-provisioned users using the following methods:

### View provisioned users

Auto-provisioned users can be identified by their `PROVISIONSRC` role option:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
-- View all auto-provisioned users (users with PROVISIONSRC role option)
SELECT * FROM [SHOW USERS] AS u
WHERE EXISTS (
  SELECT 1 FROM unnest(u.options) AS opt
  WHERE opt LIKE 'PROVISIONSRC=ldap:%'
);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
-- View all manually created users (users without PROVISIONSRC role option)
SELECT * FROM [SHOW USERS] AS u
WHERE NOT EXISTS (
  SELECT 1 FROM unnest(u.options) AS opt
  WHERE opt LIKE 'PROVISIONSRC=ldap:%'
);
```

### Last-login tracking for usage and dormancy

When `security.provisioning.ldap.enabled` is set to `true`, the `estimated_last_login_time` column in the `SHOW USERS` output is updated for both SQL client connections and DB Console authentication attempts. This allows administrators to track user activity across all connection methods and identify dormant accounts, even for users who successfully authenticate but lack privileges to access the DB Console.

The `estimated_last_login_time` column in the output of `SHOW USERS` tracks when users last authenticated. For example:

```
     username    |                options                | member_of | estimated_last_login_time
-----------------+---------------------------------------+-----------+----------------------------
  admin          | {}                                    | {}        | NULL
  root           | {}                                    | {admin}   | 2025-06-01 11:51:29.406216+00
  e.codd         | {PROVISIONSRC=ldap:example.com}       | {}        | 2025-08-04 19:18:00.201402+00
```

<Note>
  `estimated_last_login_time` is computed on a best-effort basis and may not capture every login event due to asynchronous updates.
</Note>

**To identify potentially dormant auto-provisioned users:**

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  SELECT u.username, u.estimated_last_login_time FROM [SHOW USERS]
  AS u
  WHERE EXISTS (
    SELECT 1 FROM unnest(u.options) AS opt
    WHERE opt LIKE 'PROVISIONSRC=ldap:%'
  ) AND (
    u.estimated_last_login_time IS NULL OR
    u.estimated_last_login_time < NOW() - INTERVAL '90 days'
  )
  ORDER BY u.estimated_last_login_time DESC NULLS LAST;
```

### Clean up users removed from Active Directory

Auto-provisioned users who have been removed or deactivated in Active Directory will not be automatically removed from CockroachDB. To identify and clean up these orphaned accounts:

**Step 1: Export auto-provisioned users from CockroachDB**

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
-- Export list of auto-provisioned usernames for comparison with Active Directory
SELECT u.username FROM [SHOW USERS] AS u
WHERE EXISTS (
  SELECT 1 FROM unnest(u.options) AS opt
  WHERE opt LIKE 'PROVISIONSRC=ldap:%'
);
```

**Step 2: Cross-reference with Active Directory**

Use your organization's directory tools to verify which of these users still exist in Active Directory. For example, you might use PowerShell with Active Directory cmdlets:

```powershell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
# Example PowerShell script to check if users exist in AD
$cockroachUsers = @("user1", "user2", "user3")  # Replace with actual usernames
$orphanedUsers = @()

foreach ($user in $cockroachUsers) {
    try {
        Get-ADUser -Identity $user -ErrorAction Stop | Out-Null
    } catch {
        $orphanedUsers += $user
        Write-Host "User not found in AD: $user"
    }
}
```

**Step 3: Remove orphaned users**

Before dropping users confirmed to no longer exist in Active Directory, check for any privileges that were granted directly to the user:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
-- Check for direct grants to the user (privileges inherited through roles won't block DROP USER)
SHOW GRANTS FOR username;
```

If any direct grants exist, revoke them before dropping the user. For users confirmed to no longer exist in Active Directory:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
-- Remove users that no longer exist in Active Directory
DROP USER username1, username2, username3;
```

<Note>
  Users cannot be dropped if they have direct privilege grants or own database objects. For complete requirements, refer to <InternalLink path="drop-user">`DROP USER`</InternalLink>. When using both automatic user provisioning and LDAP Authorization, consider granting privileges primarily through roles (mapped to AD groups) rather than directly to users to simplify cleanup operations.
</Note>

### Restrictions on auto-provisioned users

Users created through automatic provisioning have specific restrictions:

* **Password changes**: Auto-provisioned users cannot change their own passwords using `ALTER USER`, even if the cluster setting `sql.auth.change_own_password.enabled` is true.
* **PROVISIONSRC modification**: The `PROVISIONSRC` role option cannot be modified or removed once set.
* **Authentication method**: These users must authenticate through OIDC; password-based authentication is not available.

Attempting to change the password of an auto-provisioned user will result in an error:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER USER provisioned_user WITH PASSWORD 'newpassword';
```

```
ERROR: cannot alter PASSWORD/PROVISIONSRC option for provisioned user provisioned_user
```

## Troubleshooting

Enable <InternalLink path="logging#sessions">`SESSION` logging</InternalLink> to preserve data that will help troubleshoot LDAP issues.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SET CLUSTER SETTING server.auth_log.sql_sessions.enabled = true;
```

<Note>
  Once all functionality is configured and tested successfully, we recommend disabling session logging to conserve system resources.
</Note>

To view the logs, open `cockroach-session.log` from your <InternalLink path="configure-logs#logging-directory">logging directory</InternalLink>.

Potential issues to investigate may pertain to:

* Network connectivity to the LDAP server.
* Incorrect bind DN or password.
* Search filter not matching the intended users.
* TLS certificates.
* Missing or mismatched role names.

## Security considerations

1. Always keep a backup authentication method (like password) for administrative users.
2. Use LDAPS (LDAP over TLS) in production environments.
3. Use a restricted service account for directory searches.
4. Regularly audit LDAP group memberships.
5. Monitor authentication logs for unusual patterns.
6. **Auto-provisioning considerations**:
   * When enabling automatic user provisioning, ensure your LDAP search filters are restrictive to prevent unauthorized user creation.
   * Regularly review auto-provisioned users using the `SHOW USERS` command to identify accounts that may need deprovisioning.
   * If using LDAP Authorization, ensure all group roles are created before enabling auto-provisioning to maintain proper access control.
   * The `estimated_last_login_time` can help identify dormant accounts that may need manual removal.