Skip to content

Access management for AD users and groups

This page describes optional Microsoft SQL Server PolicySync settings used when principals are backed by Active Directory (or similar)—especially multiple database connections, direct permissions on principals, and principal naming (case and special characters). It does not replace the main MSSQL access setup; use it when your deployment needs the behaviors below.

You can apply these settings in either of these ways:

  • Self Managed (YAML / Data Plane): set variables in vars.connector.mssql.yml (see Configuration).
  • PrivaceraCloud: add custom PolicySync properties in the MSSQL application (see the PrivaceraCloud tab under Configuration).

Unless noted, flag-style YAML properties default to false when omitted. The Ranger service name defaults to mssql (see below).

Ranger service definition

PolicySync must use the Ranger service definition that matches your SQL Server version and deployment. By default the connector uses the mssql service definition.

To configure the connector for SQL Server 2019 and earlier (the privacera_mssql_19 service definition), update the following property in vars.connector.mssql.yml:

YAML
1
CONNECTOR_MSSQL_RANGER_SERVICE_NAME: "privacera_mssql_19"

For more on how this relates to native masking and unmasking scope, see Native Masking and Unmasking Types.

PrivaceraCloud equivalent :

Properties
1
ranger.policysync.connector.mssql.ranger.service.name=privacera_mssql_19

Omit this property when using the default mssql Ranger service definition.

Multiple database connections and apply permissions on principals

Use these two properties together when the connector must work across multiple databases and masking must be applied on principals.

Requirements for multi-database masking

When CONNECTOR_MSSQL_USE_MULT_DB_CONNECTION is true:

  • CONNECTOR_MSSQL_APPLY_PERMISSIONS_DIRECTLY_ON_PRINCIPALS must also be true.
  • CONNECTOR_MSSQL_MANAGE_USERS, CONNECTOR_MSSQL_MANAGE_GROUPS, and CONNECTOR_MSSQL_MANAGE_ROLES must be false (they default to true). PolicySync must not manage SQL users, groups, or roles in this mode.
YAML
1
2
CONNECTOR_MSSQL_USE_MULT_DB_CONNECTION: "true"
CONNECTOR_MSSQL_APPLY_PERMISSIONS_DIRECTLY_ON_PRINCIPALS: "true"
  • CONNECTOR_MSSQL_USE_MULT_DB_CONNECTION — Enables the connector to use multiple database connections. Default is false.
  • CONNECTOR_MSSQL_APPLY_PERMISSIONS_DIRECTLY_ON_PRINCIPALS — Applies permissions directly on principals instead of using Privacera’s role-based model (database roles aligned with principal names). Default is false.

PrivaceraCloud equivalents (replace 0 with your PolicySync connector index if needed):

Properties
1
2
ranger.policysync.connector.0.use.mult.db.connection=true
ranger.policysync.connector.0.apply.permissions.directly.on.principals=true

Disable Privacera management of users, groups, and roles

When multiple database connections are enabled, these properties must be false. By default they are true (PolicySync manages SQL users, groups, and roles); that behavior is not valid together with multi-database mode—the connector must not create or manage those principals in SQL Server when CONNECTOR_MSSQL_USE_MULT_DB_CONNECTION is true.

YAML
1
2
3
CONNECTOR_MSSQL_MANAGE_USERS: "false"
CONNECTOR_MSSQL_MANAGE_GROUPS: "false"
CONNECTOR_MSSQL_MANAGE_ROLES: "false"

PrivaceraCloud equivalents:

Properties
1
2
3
ranger.policysync.connector.0.manage.service.user=false
ranger.policysync.connector.0.manage.service.group=false
ranger.policysync.connector.0.manage.service.role=false

Persist case sensitivity for principal names

Set persist case sensitivity to true for users, groups, and roles so names keep the same casing as in Active Directory and SQL Server, avoiding mismatches when applying policies. This is recommended when using AD-linked principals together with multi-database or direct-on-principal masking (see above).

YAML
1
2
3
CONNECTOR_MSSQL_USER_NAME_PERSIST_CASE_SENSITIVITY: "true"
CONNECTOR_MSSQL_GROUP_NAME_PERSIST_CASE_SENSITIVITY: "true"
CONNECTOR_MSSQL_ROLE_NAME_PERSIST_CASE_SENSITIVITY: "true"

PrivaceraCloud equivalents:

Properties
1
2
3
ranger.policysync.connector.0.user.name.persist.case.sensitivity=true
ranger.policysync.connector.0.group.name.persist.case.sensitivity=true
ranger.policysync.connector.0.role.name.persist.case.sensitivity=true

Allow @, /, and . in user, group, and role names

By default, the connector uses a replace-from regex that treats many special characters as invalid for principal names and replaces them with _. The default pattern is roughly:

Text Only
1
[~`$&+:;=?@#|'<>.\\s^*()_%\\[\\]!\\-\\/\\\\{}]

To allow @, /, and . in names (for example, UPN-style users and group paths), use a narrower regex that omits those characters from the class, and set the replace-to string to _ for everything that still matches. Apply the same idea for users, groups, and roles.

YAML
1
2
3
4
5
6
CONNECTOR_MSSQL_USER_NAME_REPLACE_FROM_REGEX: "[~`$&+:;=?#|'<>\\s^*()_%\\[\\]!\\-\\\\{}]"
CONNECTOR_MSSQL_USER_NAME_REPLACE_TO_STRING: "_"
CONNECTOR_MSSQL_GROUP_NAME_REPLACE_FROM_REGEX: "[~`$&+:;=?#|'<>\\s^*()_%\\[\\]!\\-\\\\{}]"
CONNECTOR_MSSQL_GROUP_NAME_REPLACE_TO_STRING: "_"
CONNECTOR_MSSQL_ROLE_NAME_REPLACE_FROM_REGEX: "[~`$&+:;=?#|'<>\\s^*()_%\\[\\]!\\-\\\\{}]"
CONNECTOR_MSSQL_ROLE_NAME_REPLACE_TO_STRING: "_"

PrivaceraCloud equivalents (same character class as the YAML example—/, @, and . are not replaced):

Properties
1
2
3
4
5
6
ranger.policysync.connector.0.user.name.replace.from.regex=[~`$&+:;=?#|'<>\\s^*()_%\\[\\]!\\-\\\\{}]
ranger.policysync.connector.0.user.name.replace.to.string=_
ranger.policysync.connector.0.group.name.replace.from.regex=[~`$&+:;=?#|'<>\\s^*()_%\\[\\]!\\-\\\\{}]
ranger.policysync.connector.0.group.name.replace.to.string=_
ranger.policysync.connector.0.role.name.replace.from.regex=[~`$&+:;=?#|'<>\\s^*()_%\\[\\]!\\-\\\\{}]
ranger.policysync.connector.0.role.name.replace.to.string=_

Regex and YAML escaping

If your deployment needs different characters allowed or disallowed, adjust the character class only after validating the pattern in your environment. In vars.connector.mssql.yml, escape backslashes as needed inside double-quoted strings.

Load only masking ACLs

To load only masking-related ACLs and not access-control ACLs, set:

YAML
1
CONNECTOR_MSSQL_PERMISSION_LOADER_LOAD_ONLY_MASKING_ACLS: "true"

Default is false. Use this when you want PolicySync to synchronize masking policies without loading broader access-control ACLs.

PrivaceraCloud equivalent (custom property):

Properties
1
ranger.policysync.connector.0.permission.loader.load.only.masking.acls=true

Set to false to load access-control ACLs as well (default behavior).

Configuration

  1. SSH to the instance where Privacera Manager is installed.

  2. Open the connector variables file:

    If you have multiple connectors, replace instance1 with the appropriate connector instance name.

    Bash
    1
    vi ~/privacera/privacera-manager/config/custom-vars/connectors/mssql/instance1/vars.connector.mssql.yml

  3. Add or update only the properties you need. Example (enable only what applies):

    YAML
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
    # --- Ranger service: default is mssql. Uncomment only for SQL Server 2019 and earlier ---
# CONNECTOR_MSSQL_RANGER_SERVICE_NAME: "privacera_mssql_19"

# --- Multi-database: use.mult.db=true requires apply-on-principals=true (see Requirements for multi-database masking) ---
CONNECTOR_MSSQL_USE_MULT_DB_CONNECTION: "true"
CONNECTOR_MSSQL_APPLY_PERMISSIONS_DIRECTLY_ON_PRINCIPALS: "true"

# --- Required when multi-DB is true: manage_* must be false (defaults are true) ---
CONNECTOR_MSSQL_MANAGE_USERS: "false"
CONNECTOR_MSSQL_MANAGE_GROUPS: "false"
CONNECTOR_MSSQL_MANAGE_ROLES: "false"

# --- Persist case sensitivity (recommended for AD / SQL principal name alignment) ---
CONNECTOR_MSSQL_USER_NAME_PERSIST_CASE_SENSITIVITY: "true"
CONNECTOR_MSSQL_GROUP_NAME_PERSIST_CASE_SENSITIVITY: "true"
CONNECTOR_MSSQL_ROLE_NAME_PERSIST_CASE_SENSITIVITY: "true"

# --- Principal name sanitization: regex of chars to replace with _ (pattern omits @, /, .) ---
CONNECTOR_MSSQL_USER_NAME_REPLACE_FROM_REGEX: "[~`$&+:;=?#|'<>\\s^*()_%\\[\\]!\\-\\\\{}]"
CONNECTOR_MSSQL_USER_NAME_REPLACE_TO_STRING: "_"
CONNECTOR_MSSQL_GROUP_NAME_REPLACE_FROM_REGEX: "[~`$&+:;=?#|'<>\\s^*()_%\\[\\]!\\-\\\\{}]"
CONNECTOR_MSSQL_GROUP_NAME_REPLACE_TO_STRING: "_"
CONNECTOR_MSSQL_ROLE_NAME_REPLACE_FROM_REGEX: "[~`$&+:;=?#|'<>\\s^*()_%\\[\\]!\\-\\\\{}]"
CONNECTOR_MSSQL_ROLE_NAME_REPLACE_TO_STRING: "_"

# --- Optional: load only masking ACLs (omit or set false to load access-control ACLs too) ---
CONNECTOR_MSSQL_PERMISSION_LOADER_LOAD_ONLY_MASKING_ACLS: "true"

  4. Run the following commands to update your Privacera Manager platform instance:

    Step 1 - Setup which generates the helm charts. This step usually takes few minutes.

    Bash
    1
2
    cd ~/privacera/privacera-manager
./privacera-manager.sh setup
    Step 2 - Apply the Privacera Manager helm charts.
    Bash
    1
2
    cd ~/privacera/privacera-manager
./pm_with_helm.sh upgrade
    Step 3 - (Optional) Post-installation step which generates Plugin tar ball, updates Route 53 DNS and so on. This step is not required if you are updating only connector properties.

    Bash
    1
2
    cd ~/privacera/privacera-manager
./privacera-manager.sh post-install

  1. In the PrivaceraCloud portal, go to Settings → Applications.

  2. On Connected Applications, select MSSQL and open the application for edit.

  3. Go to Access Management → ADVANCED.

  4. Under Add New Custom Properties, add the PolicySync properties you need. Example (adjust true / false and omit lines you do not use):

    Multi-database masking

    If ranger.policysync.connector.0.use.mult.db.connection is true:

    • ranger.policysync.connector.0.apply.permissions.directly.on.principals must also be true.
    • manage.service.user, manage.service.group, and manage.service.role must be false.
    Properties
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
    # Ranger service: default mssql; uncomment only for SQL Server 2019 and earlier
# ranger.policysync.connector.mssql.ranger.service.name=privacera_mssql_19

# Multi-database: use.mult.db=true requires apply.permissions.directly.on.principals=true
ranger.policysync.connector.0.use.mult.db.connection=true
ranger.policysync.connector.0.apply.permissions.directly.on.principals=true

# Required when multi-DB is true: manage.service.* must be false (defaults are true)
ranger.policysync.connector.0.manage.service.user=false
ranger.policysync.connector.0.manage.service.group=false
ranger.policysync.connector.0.manage.service.role=false

# Persist case sensitivity for AD / SQL principal name alignment
ranger.policysync.connector.0.user.name.persist.case.sensitivity=true
ranger.policysync.connector.0.group.name.persist.case.sensitivity=true
ranger.policysync.connector.0.role.name.persist.case.sensitivity=true

# Principal name sanitization: chars matching regex replaced with _ (pattern omits @, /, .)
ranger.policysync.connector.0.user.name.replace.from.regex=[~`$&+:;=?#|'<>\\s^*()_%\\[\\]!\\-\\\\{}]
ranger.policysync.connector.0.user.name.replace.to.string=_
ranger.policysync.connector.0.group.name.replace.from.regex=[~`$&+:;=?#|'<>\\s^*()_%\\[\\]!\\-\\\\{}]
ranger.policysync.connector.0.group.name.replace.to.string=_
ranger.policysync.connector.0.role.name.replace.from.regex=[~`$&+:;=?#|'<>\\s^*()_%\\[\\]!\\-\\\\{}]
ranger.policysync.connector.0.role.name.replace.to.string=_

# Optional: load only masking ACLs
ranger.policysync.connector.0.permission.loader.load.only.masking.acls=true

  5. Click SAVE to apply the changes.