Universal Connector Integration Details

EmpowerID provides a Universal Connector that can be used for inventorying and managing resources originating in account stores for which EmpowerID does not provide a specific out of the box connector, like HR systems and custom applications via an Universal Connector database. (EmpowerID currently has out-of-the-box connectors for Active directory, LDAP, Google Apps, Office 365, and others). Using the Universal Connector requires the data in your external system be imported into the Universal Connector database via the technology of your choice—including scripting, an ETL tool like Microsoft SSIS, or code in the language of their choice—according to the schema defined for it by EmpowerID.

To find this topic helpful, you should have a good understanding of SQL and database principles.

Inbound Data

EmpowerID provides the following tables for importing data from an external system to the Universal Connector database. These tables are as follows:

There is no need to import data to each of these tables; organizations only need to insert the data that is relevant to them. The Universal Connector will still operate if any of the following tables are not populated with data.

User Table

This table is used to manage user information. When EmpowerID inventories the Universal Connector database, it writes a record for each inventoried user account in this table. Each record inserted into the table is represented in EmpowerID as a user account that can be linked to an EmpowerID Person and in turn synchronized to another system. When populating these fields with data, it is helpful to correlate them one-to-one with user attributes in these other systems.

In order to avoid resource-intensive calls to the EmpowerID synchronization engine, the records in this table should only be updated when a change occurs to those records in the external system.
Field Data Type Max Length (Bytes) Optional Default Description
UserGUID uniqueidentifier 16 No (newid()) Uniquely identifies a user account in EmpowerID. If this field is blank, EmpowerID will generate the guid. This field should never be updated for the life of the record.
UserID nvarchar(200) 400 No Specifies the UserID for a user in the external system. This value is derived from the external system and must be unique for each user. This field should never be deleted or updated for the life of the record.
LogonName nvarchar(200) 400 No Specifies the logon name for the user account. This value must be unique for each user and can be set to any desired value, such as a user's EmployeeID contained within an external system or a combination of data that can be found, derived or calculated from an external system. If this field is blank, EmpowerID will generate a unique logon name for each user record.
Active bit 1 No ((1)) Specifies whether a user is active. This field can be used in place of the Status field.
FriendlyName nvarchar(255) 510 Yes Specifies the name shown to users in EmpowerID user interfaces. The field maps to the DisplayName attribute in Active Directory.
Name nvarchar(255) 510 Yes Specifies the account name. This value should be unique and maps to the CN or DistinguishedName in Active Directory.
FirstName nvarchar(50) 100 Yes Specifies the first name of the user and maps to the givenName attribute in Active Directory.
MiddleName nvarchar(50) 100 Yes Specifies the middle name of the user and maps to the middleName attribute in Active Directory.
LastName nvarchar(50) 100 Yes Specifies the last name of the user. Maps to the LastName field in Active Directory.
Email nvarchar(255) 510 Yes Specifies the email address of the user. The value of this field can be used to set the email address of the user in Exchange when an Exchange Resource Entitlement exists. Additionally, this field is used by EmpowerID to determine whether the specific user account needs to be joined to an EmpowerID Person. For more information, see Overview of the Account Inbox.
EmployeeID nvarchar(50) 100 Yes Specifies the Employee ID of the user in the external system and will set the EmployeeID attribute of the user in Active Directory. When used, this value must be unique for each user. Additionally, this field is used by EmpowerID to determine whether the specific user account needs to be joined to an EmpowerID Person. For more information, see Overview of the Account Inbox.
BusinessRoleID nvarchar(200) 400 Yes Specifies the Business Role ID of the Business Role associated with the user in EmpowerID. When populating Business Role data, you can elect to set this field or the BusinessRoleName field. If BusinessRoleID is selected, this field will be set to the ID of the corresponding Business Role in the Business Role table of the EmpowerID metadirectory. If the user does not have a Business Role, both fields can be left blank. If the user has more than one Business Role and Location, the UserBusinessRoleLocation table can used instead of, or in conjunction with, this field as EmpowerID performs a union of the Business Role and Location data in the User table and the Business Role and Location data in the UserBusinessRoleLocation table.
If this field is left blank and multiple entries exist in the UserBusinessRoleLocation table for a specific user, the record with the lowest priority will be set as the primary Business Role.

EmpowerID uses this information for the following processes:

  • RBAC: People belong to mapped Business Roles and Locations.
  • Role and Location Compiler and Role and Location Processor Jobs: These jobs will change a Person's primary Business Role and Location, as well as remove secondary Locations based on Business Role and Location mappings.
BusinessRoleName nvarchar(200) 400 Yes Specifies the Business Role Name of the Business Role associated with the user in EmpowerID. When populating Business Role data, you can elect to set this field or the BusinessRoleID field. If BusinessRoleName is selected, this field will be set to the name of the corresponding Business Role in the Business Role table of the EmpowerID metadirectory. If the user does not have a Business Role, both fields can be left blank. If the user has more than one Business Role and Location, the UserBusinessRoleLocation table can used instead of, or in conjunction with, this field as EmpowerID performs a union of the Business Role and Location data in the User table and the Business Role and Location data in the UserBusinessRoleLocation table.
If this field is left blank and multiple entries exist in the UserBusinessRoleLocation table for a specific user, the record with the lowest priority will be set as the primary Business Role.

EmpowerID uses this information for the following processes:

  • RBAC: People belong to mapped Business Roles and Locations.
  • Role and Location Compiler and Role and Location Processor Jobs: These jobs will change a Person's primary Business Role and Location, as well as remove secondary Locations based on Business Role and Location mappings.
LocationID nvarchar(200) 400 Yes Specifies the ID of the Location associated with the user in EmpowerID. When populating Location data, you can elect to set this field or the LocationName field. If LocationID is selected, this field will be set to the ID of the corresponding Location in the Location table of the EmpowerID metadirectory. If the user does not have a Location, both fields can be left blank. If the user has more than one Location, the UserBusinessRoleLocation table can used instead of, or in conjunction with, this field as EmpowerID performs a union of the Business Role and Location data in the User table and the Business Role and Location data in the UserBusinessRoleLocation table.
If this field is left blank and multiple entries exist in the UserBusinessRoleLocation table for a specific user, the record with the lowest priority will be set as the primary Location.

EmpowerID uses this information for the following processes:

  • RBAC: People belong to mapped Business Roles and Locations.
  • Role and Location Compiler and Role and Location Processor Jobs: These jobs will change a Person's primary Business Role and Location, as well as remove secondary Locations based on Business Role and Location mappings.
LocationName nvarchar(200) 400 Yes Specifies the name of the Location associated with the user in EmpowerID. When populating Location data, you can elect to set this field or the LocationID field. If LocationName is selected, this field will be set to the name of the corresponding Location in the Location table of the EmpowerID metadirectory. If the user does not have a Location, both fields can be left blank. If the user has more than one Location, the UserBusinessRoleLocation table can used instead of, or in conjunction with, this field as EmpowerID performs a union of the Business Role and Location data in the User table and the Business Role and Location data in the UserBusinessRoleLocation table.
If this field is left blank and multiple entries exist in the UserBusinessRoleLocation table for a specific user, the record with the lowest priority will be set as the primary Location.

EmpowerID uses this information for the following processes:

  • RBAC: People belong to mapped Business Roles and Locations.
  • Role and Location Compiler and Role and Location Processor Jobs: These jobs will change a Person's primary Business Role and Location, as well as remove secondary Locations based on Business Role and Location mappings.
ManagerLogonName nvarchar(200) 400 Yes Specifies the LogonName of the user's manager, if any. If populated, the field will be used to set the user's direct manager in EmpowerID. When populating user manager data, you can elect to set this field or the ManagerAccountID field. If the user does not have a manager, both fields can be left blank.
ManagerAccountID nvarchar(200) 400 Yes Specifies the User ID of the user's manager, if any. If populated, the field will be used to set the user's direct manager in EmpowerID. When populating user manager data, you can elect to set this field or the ManagerLogonName field. If the user does not have a manager, both fields can be left blank.
Telephone nvarchar(50) 100 Yes Specifies the primary telephone number of the user's place of business and and maps to the telephoneNumber attribute in Active Directory.
MobileNumber nvarchar(50) 100 Yes Specifies the mobile number of the user and maps to the mobile attribute in Active Directory.
BusinessPhone nvarchar(50) 100 Yes Specifies the business telephone number of the user.
HomePhone nvarchar(50) 100 Yes Specifies the home telephone number of the user and maps to the homePhone attribute in Active Directory.
StreetAddress nvarchar(255) 510 Yes Specifies the street address of the user and maps to the streetAddress attribute in Active Directory.
StreetAddress2 nvarchar(255) 510 Yes Specifies a secondary street address of the user.
City nvarchar(100) 200 Yes Specifies the city where the user is located and maps to the l attribute in Active Directory.
State nvarchar(100) 200 Yes Specifies the state where the user is located and maps to the st attribute in Active Directory.
Country nvarchar(50) 200 Yes Specifies the country where the user is located and maps to the co attribute in Active Directory.
Province nvarchar(100) 200 Yes Specifies the province where the user is located.
Company nvarchar(256) 512 Yes Specifies the user's company name and maps to the company attribute in Active Directory.
Department nvarchar(256) 512 Yes Specifies the user's department name and maps to the department attribute in Active Directory.
DepartmentNumber nvarchar(50) 100 Yes Specifies the user's department number and maps to the departmentNumber attribute in Active Directory.
OfficeLocation nvarchar(450) 900 Yes Specifies the location or address of the user's office and maps to the physicalDeliveryOfficeName attribute in Active Directory.
Location nvarchar(450) 900 Yes Specifies the user's location within an organization, such as their office number and maps to the location attribute in Active Directory.
Division nvarchar(450) 900 Yes Specifies the user's division and maps to the division attribute in Active Directory.
PersonalTitle nvarchar(255) 510 Yes Specifies the user's personal title, such as "Mr", "Mrs" or "Ms." This field maps to the personalTitle attribute in Active Directory
Description nvarchar(255) 510 Yes Specifies a description for the user and maps to the description attribute in Active Directory.
SecondLastName nvarchar(50) 100 Yes Specifies a second last name for the user, where such is used.
GenerationalSuffix nvarchar(10) 20 Yes Specifies a generational suffix for the user, such as "JR" or "SR." This field maps to the generationQualifier attribute in Active Directory.
Initials nvarchar(6) 12 Yes Specifies the user's initials and maps to the initials attribute in Active Directory.
BirthName nvarchar(255) 510 Yes Specifies the name given to the user at birth.
DisplayNamePrintable nvarchar(255) 510 Yes Specifies the printable name for the user and maps to the displayNamePrintable attribute in Active Directory.
PreferredFirstName nvarchar(50) 100 Yes Specifies the preferred first name of the user.
PreferredLastName nvarchar(50) 100 Yes Specifies the preferred last name of the user.
JobTitle nvarchar(255) 100 Yes Specifies the user's job title within an organization and maps to the title attribute in Active Directory.
AboutMe nvarchar(max) max Yes Specifies user-defined demographic information about the user.
PreferredLanguage nvarchar(50) 100 Yes Specifies the preferred written or spoken language of the user. If this value is set on the person, then EmpowerID displays its user interfaces to that user in that language when localization is applied.
PostOfficeBox nvarchar(50) 100 Yes Specifies the post office box for the user and maps to the postOfficeBox attribute in Active Directory.
SocialSecurityNumber nvarchar(50) 100 Yes Specifies the social security number for the user. Please not that EmpowerID does not mask these characters. It is up to you to mask this or just include the last four digits if not.
NationalIdentificationNumber nvarchar(50) 100 Yes Specifies the national identification number for the user.
CarLicense nvarchar(50) 100 Yes Specifies the vehicle license or registration plate of the user's vehicle. This field maps to the carLicense attribute in Active Directory.
Ethnicity nvarchar(50) 100 Yes Specifies the ethnicity of the user.
Gender nvarchar(15) 30 Yes Specifies the gender of the user.
District nvarchar(256) 512 Yes Specifies the district of the user.
Assistant nvarchar(450) 900 Yes Specifies an assistant to the user.
PhotoUrl nvarchar(256) 512 Yes Specifies the location of the user's photo.
IMAddress nvarchar(256) 512 Yes
SIPAddress nvarchar(256) 512 Yes
IdentityURL nvarchar(256) 512 Yes
URLPersonal nvarchar(256) 512 Yes Specifies a person URL for the user.
URLBusiness nvarchar(256) 512 Yes Specifies the business URL for the user.
URLOWA nvarchar(256) 512 Yes Specified the OWA URL for the user.
Pager nvarchar(50) 512 Yes Specifies the pager number for the user.
IpPhone nvarchar(50) 100 Yes Specifies the user's IP phone number.
Fax nvarchar(50) 100 Yes Specifies a fax number for the user.
Notes nvarchar(1024) 100 Yes Field for inserting notes.
DateOfBirth datetime 8 Yes Specifies the date of birth for the user.
CityOfBirth nvarchar(50) 100 Yes Specifies the city of birth for the user.
CountryOfBirth nvarchar(50) 100 Yes Specifies the country of birth for the user.
EmployeeIDOther nvarchar(50) 100 Yes Specifies an alternative Employee ID for the user.
EmployeeType nvarchar(50) 100 Yes Specifies the user's employee type.
ExtensionAttribute1 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
ExtensionAttribute2 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
ExtensionAttribute3 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
ExtensionAttribute4 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
ExtensionAttribute5 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
ExtensionAttribute6 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
ExtensionAttribute7 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
ExtensionAttribute8 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
ExtensionAttribute9 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
ExtensionAttribute10 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
ExtensionAttribute11 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
ExtensionAttribute12 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
ExtensionAttribute13 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
ExtensionAttribute14 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
ExtensionAttribute15 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
ExtensionAttribute16 xml max Yes Can be used for setting a unique attribute value.
ExtensionAttribute17 varbinary(max) max Yes Can be used for setting a unique attribute value.
ExtensionAttribute18 varbinary(max) max Yes Can be used for setting a unique attribute value.
ExtensionAttribute19 bit 1 Yes Can be used for setting a unique attribute value.
ExtensionAttribute20 bit 1 Yes Can be used for setting a unique attribute value.
ExtensionAttribute21 bit 1 Yes Can be used for setting a unique attribute value.
ExtensionAttribute22 bit 1 Yes Can be used for setting a unique attribute value.
ExtensionAttribute23 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
ExtensionAttribute24 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
ExtensionAttribute25 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
ExtensionAttribute26 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
ExtensionAttribute27 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
Alias nvarchar(100) 200 Yes
TargetAddress nvarchar(255) 510 Yes
ExpectedHireDate datetime 8 Yes
Deleted bit 1 No ((0)) Specifies whether a user has been deleted in the external system. DO NOT delete the record from the table. If the record no longer exists in the external system, then you can set this value to true.
If the user has been deleted, the LogonName will need to be updated if the same values might be reused later by a new user.
DeletedDate datetime 8 Yes Specifies the date and time when the user was deleted from the external system. For information purposes only.
TerminationDate datetime 2048 Yes Specifies the date and time when the user was terminated from a hired condition.
Disabled bit 1 No ((0))
LockedOut bit 1 No ((0))
LockedOutTime bigint 8 Yes
ExpiresOn datetime 8 Yes
ValidFrom datetime 8 Yes
ValidUntil datetime 8 Yes
PasswordNeverExpires bit 1 No ((0)) Specifies whether the user's password is set to never expire.
CannotChangePassword bit 1 No ((0)) Specifies whether the user can change their password.
PasswordExpires datetime 8 Yes Specifies the date and time a user's password expires.
PasswordLastChanged datetime 8 Yes Specifies the date and time the user last changed their password.
MustChangePasswordAtNextLogon bit 1 No ((0)) Specifies whether the user must change their password the next time they log in to the system.
RequiresSmartCardForInteractiveLogon bit 1 No ((0))
EnableSyncPassword bit 1 No ((1)) Specifies whether EmpowerID will synchronize the password set on the user's Person object back to the account for the user in the external system. This setting does not affect Active Directory or any other type of "downstream" identity store. password for that user in the external system, if applicable. This has no correlation to any account Flag that controls if EmpowerID will reset the password for that account in reset password workflows. Set to false to prevent password resets to flow to this account.
DoNotAllowDelete bit 1 No ((0)) Specifies whether the user can be deleted in EmpowerID workflows.
LastLogonTime datetime 8 Yes
Status nvarchar(50) 100 Yes Used to specify the condition of the user in an external system, such as whether they are in a "pre-hire" condition, full-standing, on leave or terminated. This field can be used to invoke other logic in EmpowerID, such as triggering RET policies that provision and deprovision resources like Exchange mailboxes, etc.
SecurityIdentifier nvarchar(255) 510 Yes Specifies the unique value used to identity the user account and maps to the securityIdentifier attribute in Active Directory.
SID nvarchar(255) 510 Yes Specifies the unique value that identifies the user as a security principle. This field maps to the objectSid attribute in Active Directory.
DistinguishedName nvarchar(2048) 4096 Yes Specifies the location of the user object in the external system.
UserPrincipalName nvarchar(255) 510 Yes Specifies the UPN of the user.
ProfilePath nvarchar(450) 900 Yes Specifies the path to the user's profile and maps to the profilePath attribute in Active Directory.
LogonScript nvarchar(450) 900 Yes Specifies the logon script to run for the user, if any.
HomeDirectory nvarchar(450) 900 Yes Specifies the home directory for the user account and maps to the homeDirectory attribute in Active Directory.
HomeDrive nvarchar(5) 10 Yes Specifies the drive letter to which to map the UNC path set for HomeDirectory. This field maps to the homeDrive attribute in Active Directory.
AllowDialin bit 1 Yes Specifies whether the user can connect remotely.
HideInEmpowerID bit 1 No ((0)) Specifies whether the account will be hidden from most views in EmpowerID.
AllowJoin bit 1 No ((1)) Specifies whether EmpowerID will join the user account to an EmpowerID Person.
AllowProvision bit 1 No ((1)) Specifies whether EmpowerID will provision a new EmpowerID Person for the user account upon inventory.
ConfigurationXml xml max Yes Extensibility field. Reserved.
SystemChangedDate datetime 8 Yes This field is optional. It is meant to help you track changes to records in the external system.
A best practice for writing code to synchronize these tables is to compare the ID field of the object and the SystemChangedDate field in this table and comparing them with the value of those fields in the external system. If it is discovered that the SystemChangedDate in the external system is greater, then those records should be synced.
CreatedDate datetime 8 No (getutcdate()) This field is auto-populated with the UTC time corresponding to the date and time the user record is created.
ChangedTime timestamp 8 No

This field is time-stamped corresponding to the time any values associated with the user record are modified. This field should not be manually updated or inserted.

When EmpowerID initially inventories the records in the tables of the Universal Connector database, it pulls all the data it discovers there into the EmpowerID metadirectory, provisioning the appropriate business objects therein. Then on subsequent inventory calls, EmpowerID checks the ChangedTime field on those records to see if the value of that field is greater than the last time inventory ran. If the value is greater, EmpowerID updates the corresponding record for that object in the EmpowerID metadirectory.

Group Table

This table is used to manage the information about any groups contained within an external system and each record inserted into the table is represented as a generic group in EmpowerID. The data in this table is typically used to represent a collection of individuals, such as the Sales team, but can also be used to represent system roles, such as the Administrator, Editor or Viewer role or any other objects in an external system that signifies multiple users.

In order to avoid resource-intensive calls to the EmpowerID synchronization engine, the records in this table should only be updated when a change occurs to those records in the external system.
Field Data Type Max Length (Bytes) Optional Default Description
GroupGUID uniqueidentifier 16 No (newid()) Uniquely identifies a group in EmpowerID. If this field is blank, EmpowerID will generate the guid. This field should never be updated for the life of the record
GroupID nvarchar(200) 400 No Specifies the ID for group from the external system. The value of this field must be unique for each group. This field should never be deleted or updated for the life of the record.
LogonName nvarchar(200) 400 No Specifies the group LogonName. The value of this field should be unique.
Name nvarchar(200) 400 Yes Specifies the name of the group. This value should be unique.
FriendlyName nvarchar(255) 510 Yes

Specifies the name of the group shown to users in EmpowerID user interfaces.
Description nvarchar(max) max Yes Specifies a description for the group.
IsMailEnabled bit 1 No ((0)) Specifies whether the group is mail-enabled.
Email nvarchar(100) 512 Yes Specifies the email address for the group.
MailNickName nvarchar(256) 512 Yes
Notes nvarchar(max) max Yes
ExtensionAttribute1 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
ExtensionAttribute2 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
ExtensionAttribute3 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
ExtensionAttribute4 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
ExtensionAttribute5 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
ExtensionAttribute6 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
ExtensionAttribute7 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
ExtensionAttribute8 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
ExtensionAttribute9 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
ExtensionAttribute10 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
ExtensionAttribute11 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
ExtensionAttribute12 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
ExtensionAttribute13 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
ExtensionAttribute14 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
ExtensionAttribute15 nvarchar(1024) 2048 Yes Can be used for setting a unique attribute value.
ExtensionAttribute16 xml max Yes Can be used for setting a unique attribute value.
ExtensionAttribute17 varbinary(max) max Yes Can be used for setting a unique attribute value.
ExtensionAttribute18 varbinary(max) max Yes Can be used for setting a unique attribute value.
ExtensionAttribute19 bit 1 Yes Can be used for setting a unique attribute value.
ExtensionAttribute20 bit 1 Yes Can be used for setting a unique attribute value.
ExtensionAttribute21 bit 1 Yes Can be used for setting a unique attribute value.
ExtensionAttribute22 bit 1 Yes Can be used for setting a unique attribute value.
Deleted bit 1 No ((0)) Specifies whether the group has been deleted. DO NOT delete the record from the table.
If the group has been deleted, the LogonName will need to be updated if the same values might be reused for a new group later.
DeletedDate datetime 8 Yes Specifies the date and time when the group was deleted. For information purposes only.
DoNotAllowDelete bit 1 No ((0)) Specifies whether the group can be deleted in EmpowerID workflows.
AllowJoinRequests bit 1 No ((1)) Specifies whether the group will be visible in workflows that allow users to request group membership. Also specifies whether the group appears in the IT Shop as a requestable resource.
AutoAcceptJoinLeaveRequests bit 1 No ((0)) Specifies whether users can join or leave the group without requiring access permission to the JoinGroup or LeaveGroup workflows.
HIdeInEmpowerID bit 1 No ((0)) Specifies whether the group will be hidden in most EmpowerID views.
HIdeMembership bit 1 No ((0)) Specifies whether group membership will be hidden in most EmpowerID views.
IsHighSecurityGroup bit 1 No ((0)) Specifies whether the group is a high security group. If true, the group will be flagged and appear in some reports and SetGroups as such.
ValidFrom datetime 8 Yes
ValidUntil datetime 8 Yes
DistinguishedName nvarchar(2048) 4096 Yes Specifies the location of the group in the external system.
ConfigurationXml xml max No ((1)) Extensibility field. Reserved.
SystemChangedDate datetime 8 Yes This field is optional. It is meant to help you track changes to records in the external system.
A best practice for writing code to synchronize these tables is to compare the ID field of the object and the SystemChangedDate field in this table and comparing them with the value of those fields in the external system. If it is discovered that the SystemChangedDate in the external system is greater, then those records should be synced.
CreatedDate datetime 8 Yes This field is auto-populated with the UTC time corresponding to the date and time the record is created.
ChangedTime timestamp 8 No (getutcdate()) Auto-populated timestamp corresponding to the time a modification occurs to the group. Do not update or insert.
When EmpowerID initially inventories the records in the tables of the Universal Connector database, it pulls all the data it discovers there into the EmpowerID metadirectory, provisioning the appropriate business objects therein. Then on subsequent inventory calls, EmpowerID checks the ChangedTime field on those records to see if the value of that field is greater than the last time inventory ran. If the value is greater, EmpowerID updates the corresponding record for that object in the EmpowerID metadirectory.

Location Table

This table is used to manage location information. Each record inserted into the table is represented as an external location in EmpowerID. The schema for this table allows organizations to insert data to represent all possible locations to which users can belong within the organization, including departments, divisions, geographical sites, org charts and functional areas. As with the Business Role table, each location inserted into the table can be mapped to an EmpowerID Location. Assigning users to one or more locations in this table assigns to those users to one or more locations in EmpowerID according to the mapping rules set for those locations.

In order to avoid resource-intensive calls to the EmpowerID synchronization engine, the records in this table should only be updated when a change occurs to those records in the external system.
Field Data Type Max Length (Bytes) Optional Default Description
LocationGUID uniqueidentifier 16 No (newid()) Uniquely identifies a location in EmpowerID. If this field is blank, EmpowerID will generate the guid. This field should never be updated for the life of the record
LocationID nvarchar(200) 400 No Specifies the ID for location in the external system. The value of this field must be unique for each location. This field should never be deleted or updated for the life of the record.
Name nvarchar(200) 400 Yes Specifies the name of the location. This value should be unique.
ParentLocationID nvarchar(200) 400 Yes Specifies the ID of the location's parent. If populated, this sets the location parent in EmpowerID. When populating the parent of a location, you can elect to use this field or the ParentName field. If the location does not have a parent, both fields can be left empty.
ParentName nvarchar(200) 400 Yes Specifies the name of the location's parent. If populated, this sets the location parent in EmpowerID. When populating the parent of a location, you can elect to use this field or the ParentLocationID field. If the location does not have a parent, both fields can be left empty.
FriendlyName nvarchar(255) 510 Yes

Specifies the name of the location shown to users in EmpowerID user interfaces.
Description nvarchar(512) 1024 Yes Specifies a description for the location.
ExtensionAttribute1 nvarchar(500) 1000 Yes Can be used for setting a unique attribute value.
ExtensionAttribute2 nvarchar(500) 1000 Yes Can be used for setting a unique attribute value.
ExtensionAttribute3 nvarchar(500) 1000 Yes Can be used for setting a unique attribute value.
ExtensionAttribute4 nvarchar(500) 1000 Yes Can be used for setting a unique attribute value.
ExtensionAttribute5 nvarchar(500) 1000 Yes Can be used for setting a unique attribute value.
ExtensionAttribute6 nvarchar(500) 1000 Yes Can be used for setting a unique attribute value.
ExtensionAttribute7 nvarchar(500) 1000 Yes Can be used for setting a unique attribute value.
ExtensionAttribute8 nvarchar(500) 1000 Yes Can be used for setting a unique attribute value.
ExtensionAttribute9 nvarchar(500) 1000 Yes Can be used for setting a unique attribute value.
ExtensionAttribute10 nvarchar(500) 1000 Yes Can be used for setting a unique attribute value.
ExtensionAttribute11 nvarchar(max) max Yes Can be used for setting a unique attribute value.
ExtensionAttribute12 nvarchar(max) max Yes Can be used for setting a unique attribute value.
ExtensionAttribute13 xml max Yes Can be used for setting a unique attribute value.
ExtensionAttribute14 varbinary(max) max Yes Can be used for setting a unique attribute value.
ExtensionAttribute15 varbinary(max) max Yes Can be used for setting a unique attribute value.
Deleted bit 1 No ((0)) Specifies whether the location has been deleted in the external location. DO NOT delete the record from the table.
If the location has been deleted, the Name field will need to be updated if the same values might be reused for a new location later.
DeletedDate datetime 8 Yes Specifies the date and time when the location was deleted. For information purposes only.
IsAssignable bit 1 No ((1)) Specifies whether the location can be selected from the Location trees in EmpowerID. Set to false if you do not want users to be assigned to the Location.
ShowInTree bit 1 No ((1)) Specifies whether the location appears in the Location trees in EmpowerID.
ConfigurationXml xml max No ((1)) Extensibility field. Reserved.
SystemChangedDate datetime 8 Yes This field is optional. It is meant to help you track changes to records in the external system.
A best practice for writing code to synchronize these tables is to compare the ID field of the object and the SystemChangedDate field in this table and comparing them with the value of those fields in the external system. If it is discovered that the SystemChangedDate in the external system is greater, then those records should be synced.
CreatedDate datetime 8 Yes (getutcdate()) This field is auto-populated with the UTC time corresponding to the date and time the record is created.
ChangedTime timestamp 8 No Auto-populated timestamp corresponding to the time a modification occurs to the location. Do not update or insert.
When EmpowerID initially inventories the records in the tables of the Universal Connector database, it pulls all the data it discovers there into the EmpowerID metadirectory, provisioning the appropriate business objects therein. Then on subsequent inventory calls, EmpowerID checks the ChangedTime field on those records to see if the value of that field is greater than the last time inventory ran. If the value is greater, EmpowerID updates the corresponding record for that object in the EmpowerID metadirectory.

Business Role Table

This table is used to manage Business Role information. Each record inserted into the table is represented as an External Business Role in EmpowerID that can be mapped to any EmpowerID Business Roles.

In order to avoid resource-intensive calls to the EmpowerID synchronization engine, the records in this table should only be updated when a change occurs to those records in the external system.


Field Data Type Max Length (Bytes) Optional Default Description
BusinessRoleGUID uniqueidentifier 16 No (newid()) Uniquely identifies a Business Role in EmpowerID. If this field is blank, EmpowerID will generate the guid. This field should never be updated for the life of the record
BusinessRoleID nvarchar(200) 400 No Specifies the ID for Business Role. The value of this field must be unique for each Business Role. This field should never be deleted or updated for the life of the record.
Name nvarchar(200) 400 Yes Specifies the name of the Business Role. This value should be unique.
ParentName nvarchar(200) 400 Yes Specifies the name of the Business Role's parent. If populated, this sets the Business Role parent in EmpowerID. When populating the parent of a Business Role, you can elect to use this field or the ParentName field. If the Business Role does not have a parent, both fields can be left empty.
FriendlyName nvarchar(255) 510 Yes

Specifies the name of the Business Role shown to users in EmpowerID user interfaces.
Description nvarchar(512) 1024 Yes Specifies a description for the Business Role.
ExtensionAttribute1 nvarchar(500) 1000 Yes Can be used for setting a unique attribute value.
ExtensionAttribute2 nvarchar(500) 1000 Yes Can be used for setting a unique attribute value.
ExtensionAttribute3 nvarchar(500) 1000 Yes Can be used for setting a unique attribute value.
ExtensionAttribute4 nvarchar(500) 1000 Yes Can be used for setting a unique attribute value.
ExtensionAttribute5 nvarchar(500) 1000 Yes Can be used for setting a unique attribute value.
ExtensionAttribute6 nvarchar(500) 1000 Yes Can be used for setting a unique attribute value.
ExtensionAttribute7 nvarchar(500) 1000 Yes Can be used for setting a unique attribute value.
ExtensionAttribute8 nvarchar(500) 1000 Yes Can be used for setting a unique attribute value.
ExtensionAttribute9 nvarchar(500) 1000 Yes Can be used for setting a unique attribute value.
ExtensionAttribute10 nvarchar(500) 1000 Yes Can be used for setting a unique attribute value.
ExtensionAttribute11 nvarchar(max) max Yes Can be used for setting a unique attribute value.
ExtensionAttribute12 nvarchar(max) max Yes Can be used for setting a unique attribute value.
ExtensionAttribute13 xml max Yes Can be used for setting a unique attribute value.
ExtensionAttribute14 varbinary(max) max Yes Can be used for setting a unique attribute value.
ExtensionAttribute15 varbinary(max) max Yes Can be used for setting a unique attribute value.
Deleted bit 1 No ((0)) Specifies whether the Business Role has been deleted. DO NOT delete the record from the table.
If the Business Role has been deleted, the Name will need to updated if the same values might be reused for a new Business Role later.
DeletedDate datetime 8 Yes Specifies the date and time when the Business Role was deleted. For information purposes only.
IsAssignable bit 1 No ((1)) Specifies whether the Business Role can be selected from the Business Role trees in EmpowerID. Set to false if you do not want users to be assigned to the Business Role.
ShowInTree bit 1 No ((1)) Specifies whether the Business Role appears in the Business Role trees in EmpowerID.
ConfigurationXml xml max No ((1)) Extensibility field. Reserved.
SystemChangedDate datetime 8 Yes This field is optional. It is meant to help you track changes to records in the external system.
A best practice for writing code to synchronize these tables is to compare the ID field of the object and the SystemChangedDate field in this table and comparing them with the value of those fields in the external system. If it is discovered that the SystemChangedDate in the external system is greater, then those records should be synced.
CreatedDate datetime 8 Yes (getutcdate()) This field is auto-populated with the UTC time corresponding to the date and time the record is created.
ChangedTime timestamp 8 No Auto-populated timestamp corresponding to the time a modification occurs to the group. Do not update or insert.
When EmpowerID initially inventories the records in the tables of the Universal Connector database, it pulls all the data it discovers there into the EmpowerID metadirectory, provisioning the appropriate business objects therein. Then on subsequent inventory calls, EmpowerID checks the ChangedTime field on those records to see if the value of that field is greater than the last time inventory ran. If the value is greater, EmpowerID updates the corresponding record for that object in the EmpowerID metadirectory.




UserGroup Table

This table is used to map users with multiple group entries. The data in this table should be synchronized with the state of information from the external system.

When changes to the objects in your external system originate in the external system, you should not remove and repopulate any data contained this table with new values as EmpowerID may be performing an inventory of the data during a moment when the table contains no information. If this occurs, EmpowerID could interpret that to mean that your users have been removed from their groups and will update any related records in EmpowerID to that end. It is best to use a strategy of syncing changes only.
When a user is added to or removed from a group, the ChangedTime field on the appropriate records in the User table of the Universal Connector database are updated. At the next inventory, EmpowerID detects the change and updates the corresponding records in the EmpowerID metadirectory.


Field Data Type Max Length (Bytes) Optional Default Description
GroupID nvarchar(200) 400 No Specifies the GroupID for the group to which the user belongs.
UserID nvarchar(200) 400 No Specifies the UserID of the user belonging to the group.
CreatedDate datetime 8 Yes (getutcdate()) This field is auto-populated with the UTC time corresponding to the date and time the record is created.


UserBusinessRoleLocation Table

This table is used to associate users with Business Role and Location combinations. The data in this table can be used in conjunction with, or as a replacement for, the Business Role and Location fields in the User table as EmpowerID performs a union of the Business Role and Location data in the User table and the Business Role and Location data in the UserBusinessRoleLocation table.

When changes to the objects in your external system originate in the external system, you should not remove and repopulate any data contained this table with new values as EmpowerID may be performing an inventory of the data during a moment when the table contains no information. If this occurs, EmpowerID could interpret that to mean that your users have been removed from their Business Roles and Locations and will update any related records in EmpowerID to that end. It is best to use a strategy of syncing changes only.
Data populated in the UserBusinessRoleLocation table should not overlap with the Business Role and Location specified in the User table. Additionally, the priority specified in the UserBusinessRoleLocation table should be greater than 0.
When a user is added to or removed from a Business Role and Location, the ChangedTime field on the appropriate records in the User table of the Universal Connector database is updated. At the next inventory, EmpowerID detects the change and updates the corresponding records in the EmpowerID metadirectory.


Field Data Type Max Length (Bytes) Optional Default Description
UserID nvarchar(200) 200 No Specifies the UserID of the user in the Business Role and Location.
BusinessRoleID nvarchar(200) 200 No Specifies the BusinessRoleID of the user's Business Role.
LocationID nvarchar(200) 200 No Specifies the LocationID of the user's location.
Priority int 4 No ((0)) Used to help order the Business Role and Location combinations to which the user is associated. In EmpowerID this field can be used to help break ties between any Resource Entitlements, or Default Person Attributes where the policies which applies to the higher priority combination would be chosen.
CreatedDate datetime 8 Yes (getutcdate()) This field is auto-populated with the UTC time corresponding to the date and time the record is created.



Outbound Data

Once data has been imported into EmpowerID, you can use the bidirectional capabilities of the Universal Connector to write any changes occurring to that data in EmpowerID back to the originating system. The Universal Connector gives you two options for doing so:

  1. You can process the updates in real-time as they occur in EmpowerID. This is the recommended method.
  2. You can process the updates in batches.
The method by which EmpowerID handles change processing depends on the settings applied to the Universal Connector account store in the EmpowerID Management Console. See Configuring the Universal Connector in the EmpowerID Administrator's Guide for more information about these settings.

Real-time Processing

When using real-time processing, you will need to write a .NET module that references the TheDotNetFactory.Framework.Connectors.StandardConnector.dll and implements the IChangeLogProcessor interface. The IChangeLogProcessor provides methods that you can use to process any changes occurring to the state of your business objects against your backend systems.

The IChangeLogProcessor is provided as a reference example; however, any type of coding or scripting could be used to read the changes from the change log and execute them in the custom directory or application.

The IChangeLogProcessor provides the following methods:

Method Description
bool CreateBusinessRole(Dictionary<string, object> attributes, out string businessRoleID) This method is invoked when a new Business Role is created in EmpowerID. Should return true if successful. Parameters are as follows:
  • Attributes - Name/Value pair collection of properties the new Business Role should have. The following are minimally required:
    • Name - As entered in EmpowerID
    • BusinessRoleGUID - Unique ID in EmpowerID
  • businessRoleID - Outbound parameter that should return the uniqueID of the record created in the target system.
bool CreateGroup(Dictionary<string, object> attributes, out string GroupID) This method is invoked when a new group is created in EmpowerID. Should return true if successful. Parameters are as follows:
  • Attributes - Name/Value pair collection of properties the new group should have. The following are minimally required:
    • Name - As entered in EmpowerID
    • GroupGUID - Unique ID in EmpowerID
    • Any and all other properties (non-null) entered by the user during group creation
  • GroupID - Outbound parameter that should return the unique ID of the record created in the target system.
bool CreateUser(Dictionary<string, object> attributes, out string userID) This method is invoked when a new user is created in EmpowerID. Should return true if successful. Parameters are as follows:
  • Attributes - Name/Value pair collection of properties the new user should have. The following are minimally required:
    • Name - As entered in EmpowerID
    • UserGUID - Unique ID in EmpowerID
    • LogonName - As entered in EmpowerID
    • During RET Provisioning - Any and all attributes set for attribute flow (any direction)
    • Any and all other properties (non-null) entered by the user during account creation
  • UserID - Outbound parameter that should return the unique ID of the record created in the target system.
bool CreateLocation(Dictionary<string, object> attributes, out string locationID) This method is invoked when a new location is created in EmpowerID. Should return true if successful. Parameters are as follows:
  • Attributes - Name/Value pair collection of properties the new user should have. The following are minimally required:
    • Name - As entered in EmpowerID
    • locationGUID - Unique ID in EmpowerID
  • locationID - Outbound parameter that should return the unique ID of the record created in the target system.
bool DeleteBusinessRole(string businessRoleID) This method is invoked when a Business Role is deleted in EmpowerID. Should return true if successful. Parameters are as follows:
  • businessRoleID - Unique ID of the record to be deleted in the target system.
bool DeleteGroup(string groupID) This method is invoked when a group is deleted in EmpowerID. Should return true if successful. Parameters are as follows:
  • groupID - Unique ID of the record to be deleted in the target system.
bool DeleteUser(string userID) This method is invoked when a user is deleted in EmpowerID. Should return true if successful. Parameters are as follows:
  • userID - Unique ID of the record to be deleted in the target system.
bool DeleteLocation(string locationID) This method is invoked when a location is deleted in EmpowerID. Should return true if successful. Parameters are as follows:
  • locationID - Unique ID of the record to be deleted in the target system.
bool Disable(string userID) This method is invoked when a user becomes disabled in EmpowerID. Should return true if successful. Parameters are as follows:
  • userID - Unique ID of the record to be disabled in the target system.
bool Enable(string userID) This method is invoked when a previously disabled user becomes enabled in EmpowerID. Should return true if successful. Parameters are as follows:
  • userID - Unique ID of the record to be enabled in the target system.
bool ResetPassword(string userID, string password) This method is invoked when a user's password is reset in EmpowerID. Should return true if successful. Parameters are as follows:
  • userID - Unique ID of the user in the target system.
  • password - new password
bool RestoreGroup(string groupID) This method is invoked when a previously deleted group is restored in EmpowerID. Should return true if successful. Parameters are as follows:
  • groupID - Unique ID of the group to be restored in the target system.
bool RestoreUser(string userID) This method is invoked when a previously deleted user is restored in EmpowerID. Should return true if successful. Parameters are as follows:
  • userID - Unique ID of the user to be restored in the target system.
bool Unlock(string userID) This method is invoked when a previously locked out user is unlocked in EmpowerID. Should return true if successful. Parameters are as follows:
  • userID - Unique ID of the user to be unlocked in the target system.
bool UpdateBusinessRole(string businessRoleID, Dictionary<string, object> attributes) This method is invoked when one or more Business Role attributes are updated in EmpowerID. Should return true if successful. Parameters are as follows:
  • businessRoleID - Unique ID of the Business Role to be updated in the target system.
  • Attributes - Name/Value pair collection of the properties that have changed.
bool UpdateGroup(string groupID, Dictionary<string, object> attributes) This method is invoked when one or more group attributes are updated in EmpowerID. Should return true if successful. Parameters are as follows:
  • groupID - Unique ID of the group to be updated in the target system.
  • Attributes - Name/Value pair collection of the properties that have changed.
bool UpdateLocation(string locationID, Dictionary<string, object> attributes) This method is invoked when one or more location attributes are updated in EmpowerID. Should return true if successful. Parameters are as follows:
  • locationID - Unique ID of the Location to be updated in the target system.
  • Attributes - Name/Value pair collection of the properties that have changed.
bool UpdateUser(string userID, Dictionary<string, object> attributes) This method is invoked when one or more user attributes are updated in EmpowerID. Should return true if successful. Parameters are as follows:
  • userID - Unique ID of the user account to be updated in the target system.
  • Attributes - Name/Value pair collection of the properties that have changed.
bool RemoveFromGroup(List<string> userIDs, string groupID) This method is invoked when a user is removed from a group in EmpowerID. Should return true if successful. Parameters are as follows:
  • groupID - Unique ID of the group from which members are being removed.
  • userIDs - List of unique IDs of the users being removed from the group.
bool AddToGroup(List<string> userIDs, string groupID) This method is invoked when a user is added to a group in EmpowerID. Should return true if successful. Parameters are as follows:
  • groupID - Unique ID of the group to which new members are being added.
  • userIDs - List of unique IDs of the users who are being added to the group.
bool RemoveFromGroup(string userID, string groupID) This method is invoked when a user is removed from a group in EmpowerID. Should return true if successful. Parameters are as follows:
  • groupID - Unique ID of the group from which the user is being removed.
  • userID - Unique ID of the user being removed from the group.
bool AddToGroup(string userID, string groupID) This method is invoked when a user is added to a group in EmpowerID. Should return true if successful. Parameters are as follows:
  • groupID - Unique ID of the group to which the user is being added.
  • userID - Unique ID of the user being added to the group.
bool SetGroupMembers(List<string> userIDs, string groupID)

This method is invoked when a group's membership is being explicitly/definitively set by EmpowerID. Should return true if successful. Parameters are as follows:

  • groupID - Unique ID of the group from which users are being removed.
  • userIDs - List of unique IDs of the users who should be part of the group (other users would presumable be removed).

Batch Processing

When the Universal Connector account store is set for batch processing, EmpowerID writes information about each change occurring to a business object via EmpowerID as a record in the ChangeLog table of the Universal Connector database.

To employ batch processing, you need to write application-specific code that reads from the ChangeLog table and process the data against your managed system.

The ChangeLog table contains the following fields:

Field Description
ChangeLogID This specifies the unique identifier for the change log event.
ObjectID This is the unique identifier (the object GUID) that corresponds to the changed object. This identifier is generated by EmpowerID for the object during inventory.
ChangeLogObjectTypeID

This specifies what type of object was changed as defined by the ChangeLogObjectType table of the Universal Connector database. This field can contain the following four values:

ChangeLogObjectTypeID Object Type
1 User
2 Group
3 Location
4 Business Role
ChangeLogTypeID

This specifies what type of change occurred to the object as defined by the ChangeLogType table of the Universal Connector database. For each entry in the ChangeLog table, changes can be of one of the following types:

ChangeLogTypeID Name Description
1 Create Specifies that the object was created in EmpowerID.
2 Update Specifies that one or more object attributes were edited in EmpowerID.
3 Delete Specifies that the object was deleted in EmpowerID.
4 ResetPassword Specifies that the user's password was reset in EmpowerID.
5 Enable Specifies that a previously disabled user account was disabled in EmpowerID.
6 Disable Specifies that a previously enabled user account was disabled in EmpowerID.
7 AddToGroup Specifies that the object was added to a group in EmpowerID.
8 RemoveFromGroup Specifies that the object was removed from a group in EmpowerID.
9 SetGroupMembers Contains a list of all users that should belong to a specific group.
10 Move Specifies that the object was moved from one location to another in EmpowerID.
11 Unlock Specifies that a locked user account was unlocked in EmpowerID.
12 Restore Specifies that a previously deleted object was restored in EmpowerID.
ChangeLogData This field contains an XML-formatted list any the change that occurred.
ProcessTime This is an optional field that can be used by developers to specify when a change event is processed by their system.
ProcessStatus This is an optional field that can be used by developers to specify the status of the change event.
The following status codes are suggested as these are the codes EmpowerID uses within its own system to indicate the status of an event; however, you are free to use other codes in addition to these or implement your own status scheme altogether.

  • 0 - The event was not processed. This is the default value.
  • 1 - The event has been claimed by a server, but the process has not yet completed. In the event multiple servers could process the change event, claiming prevents the change event from being processed by more than one server.
  • 2 - The event has been processed successfully.
  • 3 - An error occurred processing the event.
  • 4 - The event was ignored.
FailedCount This is an optional field that can be used to specify the number of times a changed event has failed to process in a client application. Developers can use this data to perform certain tasks, such as firing a retry mechanism in their system.
LockedByServer This is an optional field that can be used by developers to specify which server has claimed a change event. This field is useful in the event multiple servers could process the change event.
ModifiedDate Specifies the date and time in UTC that an object was modified in EmpowerID.
ConfigurationXml
CreatedDate Specifies the date and time in UTC that a change event was created in EmpowerID. As entries in the ChangeLog table are written chronologically based on the created date, it is suggested that developers use this field to process these changes against their systems ordered by oldest to newest CreatedDate.


Once you have developed your module, you will need to register the assembly in EmpowerID by updating the ChangeLogAdapterAssembly and the ChangeLogAdapterType fields for your organization's Universal Connector. You can do so by editing the resource system in the EmpowerID Web application. To edit these fields in the EmpoweID Web application, do the following:
  1. Log in as an administrator and navigate to the Managed Systems Search page by clicking on the Find > EmpowerID System Configuration > Find Account Store tiles.
  2. From the Managed Systems Search page, click on the link for your specific Universal Connector resource system to open the Resource System Details page for it.
  3. From the Resource System Details page for your connector, click on the Edit link for it. This link has the pencil icon beside it.
  4. In the Edit page that opens, locate the ChangeLogAdapterAssembly and ChangeLogAdapterType fields, update them appropriately and then click Save.



Please note that these fields must contain the name of your assembly and the namespace-qualified name of the assembly and the assembly type, respectively.