AVEVA™ Work Tasks

Map Active Directory and SQL Provider

Save PDF

TABLE OF CONTENTS

Map Active Directory and SQL Provider

Save PDF

Last UpdatedJun 10, 2024
3 minute read

Overview of mapping between Active Directory and SQL Provider

Mapping is required, if same user exists in more than one provider to avoid duplicate entries in database.

Advantage of Mapping

While mapping, if user exists in more than one provider then user can login or act on the given task from any of the providers.
Duplication of same user from different providers can be avoided.

Scenario: Log on to the EC site using domain login and use the resource properties defined in the sqlprovider (form) in workflow design.

To set the Mapping

Identify the column which is unique for all the providers i.e. user has to identify the common column which shares the same information for the given user across the providers. In below scenario, considered email as the common column

Active directory provider properties

</properties>

<SearchScope>SubTree</SearchScope>

<ReferralChasing>External </ReferralChasing> <TimeOut>60</TimeOut>

<AuthenticationUser>skelta\kalaiyar</AuthenticationUser>

<AuthenticationPassword IsEncrypted="true"> MqXasBHPAOsX6NU+oNwPSQ== </AuthenticationPassword>

</Preferences><LDAPServer>LDAP://</LDAPServer>

<LDAPSearchBase>DC=skelta,DC=dom </LDAPSearchBase> <LDAPBaseFilter>(objectCategory=Person)</LDAPBaseFilter>

<DomainName>SKELTA\</DomainName>

</provider>

</dataproviders>

sqlprovider Properties

dataproviders>

</properties>

<datasource>server=SKELTADTP112;database=TestMappingAd-SQL;Integrated security=true</datasource>

<SourceTable>users1</SourceTable>

</provider>

</dataproviders>

To Map between Active directory and SQL Provider

The 'mail' property from active directory and email property from DB has the common field to map i.e. they both have the same information for the given user mail is active directory property. email is a column in sqlprovider.

Default Instance name for ActiveDirectory is activedirectory

Default Instance name for sql is sqlprovider

<<mappingfields>

</fields>

</mappingfield>

</fields>

</mappingfield>

</mappingfields>

Note: User can map between any number of providers and on any common columns. Provider instance name are taken from SKEntityDatasourceprovider table.

To map Active Directory Provider with SQL Provider

Create repository with active directory as resource provider.
Insert a record to SKEntityDataSourceProvider with Application set to Repository name, EntityID taken from the SKEntity table (Userentity for mapping users), Provider to sqlprovider and Instancename with any UserdefinedName.
Update the DefnitionXML column for the added record with appropriate resource XML data.

Example:

<dataproviders>

<provider name="sqlprovider" type="sql server" authenticationsource="false">

<properties>

<property name="SQLuserid" type="int" purpose="identifier" />

<property name="name" type="string" purpose="name" display="true"/>

<property name="email" type="string" purpose="email" persistcolumn="Email"/>

</properties>

<datasource>server=SKELTADTP112;database=TestMappingAd-SQL;Integrated security=true

</datasource>

SourceTable>users</SourceTable>

</provider>

</dataproviders>

Note: Add persistcolumn attribute for the common column in both the providers. Authenticationsource="false" at the provider level disable the login to the repository from EC site for this provider and Connection string should be changed.
Update DefinitionXML column of the active directory by adding persistcolumn attribute for the common property.

Example:

<dataproviders>

<provider name="activedirectory" type="activedirectory">

<properties>

<property name="sAMAccountName" type="string" purpose="identifier" displayname="Account Name" entitymappedproperty="AD userid" />

<property name="displayName" type="string" purpose="name" displayname="Resource Name" entitymappedproperty="name" />

<property name="mail" type="string" purpose="email" displayname="Email" persistcolumn="Email" entitymappedproperty="email"/>

</properties>

<Preferences>

<SearchScope>SubTree</SearchScope>

<ReferralChasing>External </ReferralChasing> <TimeOut>60</TimeOut>

<SizeLimit>0</SizeLimit>

<AuthenticationUser>skelta\kalaiyar</AuthenticationUser>

<AuthenticationPassword IsEncrypted="true"> MqXasBHPAOsX6NU+oNwPSQ== </AuthenticationPassword>

</Preferences>

<LDAPServer>LDAP://</LDAPServer>

<LDAPSearchBase>DC=skelta,DC=dom </LDAPSearchBase> <LDAPBaseFilter>(objectCategory=Person)</LDAPBaseFilter>

<DomainName>SKELTA\</DomainName>

</provider>

</dataproviders>

Note: Active directory preferences should be changed.
Update DefinitionXml of SKEntity table for the user entity.

Example:

entity type="user">

<properties>

<property name="AD userid" type="string" purpose="identifier"/>

<property name="SQLuserid" type="int" purpose="identifier"/>

<property name="name" type="string" displayname="Name" purpose="name" display="true"/>

<property name="email" type="string" displayname= "Email ID" purpose ="email" />

</properties>

</entity>%%$#@@!!!*&

<mappingfields>

<mappingfield mappedfromprovider="activedirectory">

<fields mappedtoprovider= "AD_SQLprovider" >

<field name="mail" mappedto="email" />

</fields>

</mappingfield>

<mappingfield mappedfromprovider="AD_SQLprovider">

<fields mappedtoprovider ="activedirectory" >

<field name="email" mappedto="mail"/>

</fields>

</mappingfield>

</mappingfields>

Note: The properties configured in DefinitionXml of SKEntity table are available to create the query for assigning the activities to the resources. mappedfromprovider and mappedtoprovider are the instance name taken from the SKEntityDataSourceProvider.
Create the table named "users". This is the SourceTable for sqlprovider. The sample table is given below.

CREATE TABLE [dbo].[users](

[SQLuserid] [int] NULL,

[name] [varchar](50) NULL,

[email] [varchar](50) NULL

) ON [PRIMARY]

GO
When the Activities are assigned to resources, it will look for the resource in both the providers and if common field found for the same, it will assign the activity to the user based on the mapped id.

When the user with common field found and if that user's details are already created in SKVirtualActor table, AVEVA Work Tasks automatically maps that user using MappedToId.

AVEVA™ Work Tasks