Map Active Directory to SQL Provider

You can map Active Directory to SQL Provider.

Advantages of Mapping

• Mapping enables user accounts existing in multiple user providers to login or work on a given task from any of the user providers.

• Duplicate entries for a user account from different user providers can be avoided.

Scenario:

Log on to the Enterprise Console using domain login and use the resource properties defined in the SQL provider (form) in workflow design.

To set mapping

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

Active Directory provider properties

<dataproviders>

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

<properties><property name="sAMAccountName" persistcolumn="UserIdString" type="string" purpose="identifier" displayname="Account Name" />

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

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

<property name="title" type="string" purpose="designation" displayname="Designation" />

</properties>

<Preferences><SearchScope>SubTree</SearchScope>

<ReferralChasing>External</ReferralChasing><TimeOut>60</TimeOut><SizeLimit>0</SizeLimit>

<AuthenticationUser>skelta\testuser</AuthenticationUser><AuthenticationPassword IsEncrypted="true">tj49KeDsRuxVbVZlZDFLFw==</AuthenticationPassword>

</Preferences>

<LDAPServer>LDAP://</LDAPServer>

<LDAPSearchBase>DC=Testskelta,DC=dom</LDAPSearchBase>

<LDAPBaseFilter>(objectCategory=Person)</LDAPBaseFilter>

<DomainName>TestSKELTA\</DomainName>

</provider>

</dataproviders>

SQL Provider Properties

<dataproviders>

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

<properties><property name="userid" 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" persistcolumn="Email"/>

</properties>

<datasource>server=skeltadtp75a;database=TestUser;Integrated security=true</datasource>

<SourceTable>users</SourceTable>

</provider>

</dataproviders>

Instance name for ActiveDirectory is activedirectory.

Instance name for SQL is sqlprovider.

To map between Active Directory and SQL provider

The mail property from active directory and email property from database has the common field to map, that is, they both have the same information for the given user. Mail is an active directory property whereas email is a column in SQL provider.

<mappingfields>

<mappingfield mappedfromprovider="ActivedirectoryInstance name">

<fields mappedtoprovider="sql provider instance name">

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

</fields>

</mappingfield>

<mappingfield mappedfromprovider=" sql provider instance name">

<fields mappedtoprovider=" ActivedirectoryInstance name ">

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

</fields>

</mappingfield>

</mappingfields>

A 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

1. Create a AVEVA Work Tasks Repository with Active Directory as resource provider.

2. 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.

3. 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="userid" 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" persistcolumn="Email"/>

   </properties>

   <datasource>server=skeltadtp75a;database=TestUser;Integrated security=true</datasource>

   Authenticationsource="false" at the provider level disables the login to the Repository from the Enterprise Console for this provider. Using the "Authenticationsource" attribute, you can decide which provider to use for user authentication while log in to the Enterprise Console. Add persistcolumn attribute for the common properties in both the providers.

   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="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\testuser</AuthenticationUser>

   <AuthenticationPassword IsEncrypted="true">tj49KeDsRuxVbVZlZDFLFw==</AuthenticationPassword>

   </Preferences>

   <LDAPServer>LDAP://</LDAPServer>

   <LDAPSearchBase>DC=Testskelta,DC=dom</LDAPSearchBase>

   <LDAPBaseFilter>(objectCategory=Person)</LDAPBaseFilter>

   <DomainName>TestSKELTA\</DomainName>

   </provider>

   </dataproviders>

   For each of the property there can be entitymappedproperty attribute. This attribute value can be used to map to other property names from different providers if both the purpose are same.

4. Update Definitionxml of SKEntity table for the user entity.

   Example:

   <entity type="user"><properties><property name="userid" type="string" 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_SQL provider"><field name="mail" mappedto="email" /></fields></mappingfield>

   <mappingfield mappedfromprovider="AD_SQL provider"><fields mappedtoprovider="activedirectory"><field name="email" mappedto="mail" /></fields></mappingfield>

   </mappingfields>

   The properties configured in definition xml of SKEntity table are available to create the query for assigning the activities to the resources. As per the above scenario, we are retaining only SQL provider properties. If we require active directory properties in the workflow design same can be updated in this column, however we need to make sure that the properties defined in the xml are unique. Also it is mandatory to append the mapping column.

   Important: Even if the identifier is of type int, you need to mention it as string globally (in SKEntity table). This is because when we are accessing the resource properties, it is in the string form that is, "providername::userid".

5. 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.
   

User Table

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

Log on to Repository using Active Directory credentials.