Skip to main content

Microsoft SQL Server Connector

EmpowerID Microsoft SQL connector allows organizations to bring the user data (user accounts, profiles, and roles) in their SQL server to EmpowerID, where it can be managed and synchronized with data in any connected back-end user directories. When EmpowerID inventories an SQL server, it creates an account in the EmpowerID Identity Warehouse for each SQL user, a group for each SQL profile, and an EmpowerID Business Role for each SQL role. Once connected, you can manage this data from EmpowerID in the following ways:

  • Provision new users
  • Edit user attributes
  • Delete users

Step 1 – Create a Microsoft SQL account store in EmpowerID​

  1. Navigate to Admin > Applications and Directories on the EmpowerID navbar and select Account Stores and Systems.
  2. Click the Actions tab and select Create Account Store.
  3. Under System Types, search for SQL.
  4. Click Microsoft Sql Server to select the type and then click Submit.
  5. On the Create MSSql Connector Settings page that appears, fill in the following information:
    • Name — Enter a name for the account store
    • User Name — Enter the user name of the SQL Server Administrator
    • Password — Enter the password for the SQL Server Administrator
    • Server — Enter the FQDN or IP address of the SQL Server
    • Is Remote (Requires Cloud Gateway) — This setting appears for account stores with local directories, such as Active Directory, LDAP, SAP, etc. When enabled, this tells EmpowerID to use the Cloud Gateway Connection for that account store. The Cloud Gateway Connection must be installed on an on-premise machine. For installation information, please see Installing the EmpowerID Cloud Gateway Client..
  6. When ready click Submit to create the account store.
  7. EmpowerID creates the account store and the associated resource system. The next step is to configure the attribute flow between the account store and EmpowerID.

Step 2 – Configure Attribute Flow​

EmpowerID allows configuring attribute synchronization rules between the Linux system and the EmpowerID Identity Warehouse. Attribute flow rules define how attributes are synchronized and can be weighted for prioritization across multiple sources.

Attribute Flow Options

  • No Sync: No synchronization occurs between EmpowerID and the Linux system.
  • Bidirectional Flow: Changes in either system are reflected in the other.
  • Account Store Changes Only: Changes made in Linux sync to EmpowerID but not vice versa.
  • EmpowerID Changes Only: Changes made in EmpowerID sync to Linux but not vice versa.

CRUD Operations

  • Create: Creates an attribute if it does not exist.
  • Update: Updates an existing attribute.
  • Delete: Removes an attribute value.

Configuring Attribute Flow

  1. Navigate to Account Stores and Systems and locate the newly created Linux account store.
  2. Click on the Account Store link.
  3. Select the Attribute Flow Rules tab.
  4. Adjust the synchronization direction using the Attribute Flow dropdown.
  5. Modify CRUD operation scores as needed to prioritize attribute sources.
  6. Save your changes.
info

EmpowerID only considers scores for attribute CRUD operations when multiple account stores with the same user records are connected to EmpowerID, such as would be the case if an HR System and this account store were being inventoried by EmpowerID.

Now that the attribute flow has been set, the next steps include configuring the account store and enabling EmpowerID to inventory it.

Step 3 – Configure account store settings​

  1. On the Account Store and Resource System page, click the Account Store tab and then click the pencil icon to put the account store in edit mode.

    This opens the edit page for the account store. This page allows you to specify the account proxy used to connect EmpowerID to your SQL server as well as how you want EmpowerID to handle the user information it discovers there during inventory. Settings that can be edited are described in the table below the image.

    Account Store Settings
    SettingDescription
    General Settings
    IT Environment TypeAllows you to specify the type of environment in which you are creating the account store.
    Account Store TypeAllows you to categorize the account store in order to filter resource objects appearing to users based on the type of account store. Types include:
    • Default
    • Finance
    • ITDS
    • Insurance
    • Security
    For example, the Account Store Type can used to filter the groups that appear to users shopping for those objects in the IAM Shop.
    Option 1 Specify an Account ProxyAllows you to change the credentials for the account that EmpowerID uses to connect to and manage the account store.
    Option 2 Select a Vaulted Credential as Account ProxyAllows you to use a credential that you have vaulted in EmpowerID as the account that EmpowerID uses to connect to and manage the account store.
    Inventoried Directory ServerAllows you to select a connected server as the directory server for the account store.
    Authentication and Password Settings
    Allow Password SyncEnables or disables the synchronization of password changes to user accounts in the domain based on password changes for the owning person object or another account owned by the person. This setting does not prevent password changes by users running the reset user account password workflows.
    Queue Password ChangesSpecifies whether EmpowerID sends password changes to the Account Password Reset Inbox for batch processing.
    Password Manager Policy for Accounts without PersonSpecifies the Password Manager Policy to be used for user accounts not joined to an EmpowerID Person.
    Provisioning Settings
    Allow Person Provisioning (Joiner Source)Specifies whether EmpowerID Persons can be provisioned from user accounts in the account store.
    Allow Attribute FlowSpecifies whether attribute changes should flow between EmpowerID and the account store.
    Allow Provisioning (By RET)Allows or disallows the Resource Entitlement (RET) Inbox process to auto-provision accounts for this domain for users who receive RET policy-assigned user accounts, but have not yet had them provisioned.
    Allow Deprovisioning (By RET)Allows or disallows the Resource Entitlement Inbox process to auto de-provision accounts for this domain for users who still have RET policy-assigned user accounts, but no longer receive a policy that grants them a user account in the domain. De-provisioning only occurs if the de-provision action on the Resource Entitlement policy is set to De-Provision.
    Max Accounts per PersonThis specifies the maximum number of user accounts from this domain that an EmpowerID Person can have linked to them. This prevents the possibility of a runaway error caused by a wrongly configured Join rule. It is recommended that this value be set to 1 unless users will have more than 1 account and you wish them to be joined to the same person.
    Allow Account Creation on Membership RequestSpecifies whether EmpowerID creates user accounts in the account store when an EmpowerID Person without one requests membership within a group belonging to the account store.
    Recertify All Group Changes as DetectedSpecifies whether detected group changes should trigger recertification.
    Inventory Auto Provision OUs as IT System LocationsSpecifies whether EmpowerID provisions OUs as Locations under the All IT Locations in the Locations tree.
    Inventory Auto Provision External Roles as Business RolesSpecifies whether EmpowerID provisions Business Roles from any external roles in the connected system.
    Default Person Business RoleSpecifies the default EmpowerID Business Role to be assigned to each EmpowerID Person provisioned from the user accounts in the account store.
    Default Person Location (leave blank to use account container)Specifies the default EmpowerID Location to be assigned to each EmpowerID Person provisioned from the user accounts in the account store.
    Directory Clean Up Enabled
    Directory Clean Up EnabledSpecifies whether the SubmitAccountTermination permanent workflow should claim the account store for processing account terminations. When enabled, accounts in the account store that meet the qualifications to be marked for deletion are moved into a special OU within the external directory, disabled and finally deleted after going through an automated approval process. This process involves setting a number of system settings in EmpowerID and requires multiple approvals by designated personnel before an account is finally removed from the account store.
    Report Only Mode (No Changes)When enabled, a report of what the Directory Clean Up process would do is written to the log. The process itself is ignored and all accounts are set to Termination Pending,
    Special Use Settings
    Automatically Join Account to a Person on Inventory (Skip Account Inbox)Specifies whether EmpowerID should attempt to join user accounts in the account store to an existing EmpowerID Person during the inventory process. When enabled, the Account Inbox is bypassed.
    Automatically Create a Person on Inventory (Skip Account Inbox)Specifies whether EmpowerID should create new EmpowerID Persons from the user accounts discovered in the account store during the inventory process. When enabled, the Account Inbox is bypassed.
    Queue Password Changes on FailureSpecifies whether EmpowerID should send password changes to the Account Password Reset Inbox only when the change fails.
    Inventory Settings
    Inventory Schedule IntervalSpecifies the time span that occurs before EmpowerID performs a complete inventory of the account store. The default value is 10 minutes.
    Inventory EnabledAllows EmpowerID to inventory the user information in the account store.
    Membership Settings
    Enable Group Membership ReconciliationAllows EmpowerID to manage the membership of the account store’s groups, adding and removing user to and from groups based on policy-based assignment rules.
    Membership Schedule IntervalSpecifies the time span that occurs before EmpowerID runs the Group Membership Reconciliation job. The default value is 10 minutes.

  2. Edit the account store as needed and then click Save to save your changes.

Next, enable the Account Inbox permanent workflow to allow the Account Inbox to provision or join the user accounts in Oracle to EmpowerID Persons as demonstrated below.

Step 5 - Enable the Account Inbox Permanent Workflow​

The Account Inbox workflow is essential for processing user accounts discovered during inventory.

  1. Navigate to Infrastructure Admin > EmpowerID Server and Settings > Permanent Workflows.
  2. Locate Account Inbox and click its Display Name link.
  3. Click the pencil icon to enter edit mode.
  4. Check Enabled.
  5. Click Save to activate the workflow.

Step 6 - Monitor Inventory​

EmpowerID continuously inventories the account store, identifying new, updated, or orphaned accounts.

  1. Navigate to Identity Lifecycle > Account Inbox.
  2. Use the tabbed views to monitor inventory:
    • All: Lists all discovered user accounts and their statuses.
    • Dashboard: Displays an overview of account inventory activities.
    • Orphans: Highlights user accounts without an associated EmpowerID Person.

Regular monitoring ensures that newly discovered accounts are processed correctly and remain synchronized with EmpowerID policies.