Important
This repository contains the connector and configuration code only. The implementer is responsible to acquire the connection details such as username, password, certificate, etc. You might even need to sign a contract or agreement with the supplier before implementing this connector. Please contact the client's application manager to coordinate the connector requirements.
- HelloID-Conn-Prov-Target-Blacklist-SQL
HelloID-Conn-Prov-Target-Blacklist-SQL is a target connector that writes user attribute values to an SQL database-based blacklist. These values can later be used to prevent reuse, for example of sAMAccountName, email, or userPrincipalName. The blacklist is used in combination with the uniqueness check feature of other connectors (e.g., Active Directory) to ensure attribute values remain unique across the organization.
This connector is designed to solve common identity management challenges:
-
Preventing attribute reuse: When an employee leaves the organization, their email address, username, or UPN is blocked from being immediately reassigned. This prevents confusion, misdirected emails, and security issues.
-
Organizational uniqueness enforcement: Even if your HR system doesn't track historical employees, the blacklist maintains a record of all previously used values, ensuring no two people (past or present) can have the same identifier.
-
Controlled value recycling: After a configurable retention period (e.g., 365 days), values can be made available for reuse, balancing security with practical namespace management.
-
Cross-system validation: Works seamlessly with HelloID's built-in connectors (like Active Directory) to validate uniqueness before account creation, preventing provisioning errors.
-
Temporary departures: When an employee returns after a leave of absence, their original values can be automatically restored if still within the retention period.
-
Multi-attribute validation: Supports checking multiple attributes simultaneously (email, UPN, proxy addresses) with cross-checking capabilities to catch conflicts across different attribute types.
The following features are available:
| Feature | Supported | Notes |
|---|---|---|
| Account Lifecycle | ✅ | Create, Update, Delete (soft-delete with retention period) |
| Permissions | ❌ | Not applicable for blacklist connector |
| Resources | ❌ | Not applicable for blacklist connector |
| Entitlement Import: Accounts | ❌ | Not applicable for blacklist connector |
| Entitlement Import: Permissions | ❌ | Not applicable for blacklist connector |
| Governance Reconciliation Resolutions | ❌ | Not applicable for blacklist connector |
URL of the icon used for the HelloID Provisioning target system.
https://raw.githubusercontent.com/Tools4everBV/HelloID-Conn-Prov-Target-Blacklist-SQL/refs/heads/main/Icon.png
- HelloID Provisioning agent (cloud or on-premises)
- Available MS SQL Server database (external server or local SQL Express instance)
- Database table created using the
createTableBlacklist.sqlscript - Database access rights for the agent's service account or SQL-authenticated account
- The client is responsible for populating the blacklist database with any previous data. HelloID will only manage and add the data for the persons handled by provisioning.
The following settings are required to connect to the SQL database.
| Setting | Description | Mandatory |
|---|---|---|
| Connection string | String value of the connection string used to connect to the SQL database | Yes |
| Table | String value of the table name in which the blacklist values reside | Yes |
| Username | String value of the username of the SQL user to use in the connection string | No |
| Password | String value of the password of the SQL user to use in the connection string | No |
| RetentionPeriod (days) | Critical setting: Number of days a deleted value remains blocked before it can be reused. Common values: 365 (1 year), 730 (2 years), or 999999 (permanent blocking). This protects against immediate reuse while allowing eventual recycling of namespace values. |
Yes |
The correlation configuration is not used or required in this connector.
The field mapping can be imported by using the fieldMapping.json file.
employeeIdis only mapped for the Create action- Attributes (Mail, SamAccountName, UserPrincipalName) are mapped for Create, Update, and Delete actions
- All fields use
StoreInAccountData: true
The account reference is populated with the employeeId property during the Create action.
Why employeeId is important: The employeeId serves as the unique identifier linking blacklist entries to specific individuals. This is critical for:
- Ownership tracking: Determines who "owns" each blocked value
- Automatic restoration: When a person is re-enabled, their previous values can be restored because the system knows which values belonged to them
- Conflict prevention: If a value is already in use by another employeeId (and within retention period), the system prevents reassignment
- Multi-value support: One person can have multiple blocked attributes (email, UPN, proxy addresses) all tied to their employeeId
- Audit trail: Provides clear history of which values were assigned to which employees and when
Note
This connector is designed to work in combination with the uniqueness check feature of other connectors (like Active Directory) to ensure attribute values remain unique across the organization.
- Soft-delete with retention: When a person is deleted, the
whenDeletedtimestamp is set. The value remains blocked for the configured retention period. - Automatic restore: If a person is re-enabled and their previous attribute value is still blocked, the Create action automatically restores it by clearing the
whenDeletedtimestamp. - Retention period configuration: Use
RetentionPeriod (days)to specify how long values remain blocked after deletion. Setting this to999999effectively makes the retention permanent. - Self-usage control: The
checkOnExternalSystemsAd.ps1script includes an$allowSelfUsageconfiguration. When set to$false, even a person's own existing values are treated as non-unique, forcing complete value regeneration. This is useful for migration scenarios or when implementing new naming conventions. - Multiple records handling: The Update action will issue a warning if multiple records with the same
attributeNameandattributeValueare found. - Cross-check validation: The
checkOnExternalSystemsAd.ps1script supportscrossCheckOnconfiguration to validate uniqueness across different attribute types (e.g., checking if an email address already exists as a proxy address). - keepInSyncWith functionality: When configured, non-unique status cascades across related fields automatically.
- Skip optimization: Once a field is marked non-unique, redundant database queries are automatically skipped.
- SQL query safety: All scripts use proper SQL escaping for single quotes to prevent SQL injection.
The following lifecycle actions are available:
| Action | Description |
|---|---|
| Create | Creates or restores blacklist records for each configured attribute. If a value already exists in the blacklist: (1) owned by the same person - clears whenDeleted to reactivate, (2) owned by another person but retention period expired - updates employeeId and clears whenDeleted to reassign, (3) owned by another person within retention period - throws error. If value doesn't exist, creates a new record with whenCreated timestamp. |
| Update | Maintains blacklist records for each configured attribute. Similar logic to Create: can create new records if missing, reactivate previously deleted values (clear whenDeleted), or reassign expired values to current person. Updates whenUpdated timestamp. Does not modify the attributeValue itself - only ownership and timestamps. |
| Delete | Soft-deletes blacklist records by setting whenDeleted and whenUpdated timestamps. Records remain in the database but are marked as deleted. After the configured retention period expires, these values become available for reuse by other persons. Does not physically remove rows from the database. |
Beyond the standard lifecycle scripts, this connector includes specialized scripts:
| Script | Purpose |
|---|---|
checkOnExternalSystemsAd.ps1 |
Uniqueness validation script - Configured in the HelloID built-in Active Directory connector to check if proposed values exist in the blacklist before account creation. Validates against retention period: values are non-unique if owned by another person and within retention period, but can be reused if retention period expired. Includes advanced features: (1) Self-usage control - configurable $allowSelfUsage to determine if persons can reuse their own values, (2) Cross-checking - validate if a value exists under different attribute names (e.g., email as both 'mail' and 'userPrincipalName'), (3) Field synchronization - keepInSyncWith automatically marks related fields as non-unique. Returns NonUniqueFields array to HelloID, preventing provisioning errors before AD account creation attempts. |
createTableBlacklist.sql |
Database setup script - Creates the required SQL table structure with proper column types (NVARCHAR, DATETIME2) and constraints. Must be executed in SQL Server Management Studio or similar tool before using the connector. Sets up the foundation for all blacklist operations. |
GenerateUniqueData/example.create.ps1 |
Legacy example script - Demonstrates how to generate unique values by querying the SQL blacklist database in older PowerShell v1 connectors. While this is legacy code, it can be adapted for scenarios requiring custom unique value generation (e.g., employee numbers, random identifiers). Not required for standard V2 connector operation. |
The uniqueness check script includes several configuration options that must be set before use:
Important
Retention Period Synchronization: The checkOnExternalSystemsAd.ps1 script requires access to the same database and retention period configuration as the main connector. Configure the script within the target connector (e.g., Active Directory) by passing the same connection settings and retentionPeriod value. The retention period must be consistent across both the blacklist connector configuration and the uniqueness check script to ensure accurate validation.
Warning
Initial Configuration Required: Before deploying to production, you must customize the following configurations in checkOnExternalSystemsAd.ps1:
$correlationAttribute- Must match your account structure (typicallyemployeeId)$allowSelfUsage- Set according to your business requirements$fieldsToCheck- Define which attributes to validate and their relationships
The example configuration is tailored for Active Directory. Adjust field names and cross-check logic for other target systems.
Important
Field Mapping Requirement: The accountFieldName specified in $correlationAttribute (typically employeeId) MUST be mapped in your HelloID field mapping configuration for ALL operations where the uniqueness check is used (create, update, etc.). If this field is not mapped or is empty, the uniqueness check cannot function correctly.
Common mistake: Mapping the field only for 'create' but using the uniqueness check for both 'create' and 'update'.
Solution: Ensure the correlation attribute field is mapped for all relevant operations in your HelloID field mapping configuration. The script will validate that the field exists and has a value before performing any uniqueness checks.
Correlation Attribute Configuration
$correlationAttribute = [PSCustomObject]@{
accountFieldName = "employeeId" # Property name in the account object from HelloID
systemFieldName = "employeeId" # Corresponding column name in the blacklist database
}This mapping identifies which attribute links persons between HelloID and the blacklist database. It's essential for:
- Determining value ownership (does this value belong to the current person or someone else?)
- Enabling self-usage checks
- Supporting automatic value restoration for returning employees
Allow Self-Usage Configuration
$allowSelfUsage = $true # Default: true (recommended)Controls whether a person can reuse values they already own:
$true(recommended): Person's existing values are treated as unique. They can keep their email, username, etc. without triggering non-unique warnings.$false(strict mode): Even the person's own values are treated as non-unique, forcing regeneration of all values. Use this for complete value refresh scenarios or migrations where all values must be regenerated.
Fields to Check Configuration
Configure which attributes to validate and how they relate to each other:
$fieldsToCheck = [PSCustomObject]@{
"userPrincipalName" = [PSCustomObject]@{
systemFieldName = 'userPrincipalName' # Database column to query
accountValue = $a.userPrincipalName # Value from account object
keepInSyncWith = @("mail", "proxyAddresses") # Related fields that share uniqueness status
crossCheckOn = @("mail") # Also check if value exists as different attribute type
}
# ... additional fields
}Configuration properties:
- systemFieldName: The
attributeNamevalue to search for in the blacklist database - accountValue: The actual value from the account object to validate
- keepInSyncWith: If this field is non-unique, automatically mark these related fields as non-unique too
- crossCheckOn: Also search for this value under different attribute names (e.g., check if email exists as both 'mail' and 'userPrincipalName')
The table includes the following columns:
| Column Name | Data Type | Description |
|---|---|---|
| employeeId | NVARCHAR(100) | Unique identifier for an employee (HelloID person) |
| attributeName | NVARCHAR(100) | Name of the attribute (e.g., Mail, SamAccountName, UserPrincipalName) |
| attributeValue | NVARCHAR(250) | Value of the attribute (e.g., john.doe@company.com) |
| whenCreated | DATETIME2(7) | Timestamp of when the record was originally created |
| whenUpdated | DATETIME2(7) | Timestamp of the last update (can be used to track last activity) |
| whenDeleted | DATETIME2(7) | Timestamp when the record was soft-deleted; NULL for active records |
Use the createTableBlacklist.sql script to create the required table structure in your database.
Tip
For more information on how to configure a HelloID PowerShell connector, please refer to our documentation pages.
The official HelloID documentation can be found at: https://docs.helloid.com/.