Anyone who has worked with FootPrints 12 knows that some features of previous versions are still missing. One of the most critical of these missing features is manager approvals. Fortunately there is a workaround that is sufficient for most environments with this requirement.
This post will document how to implement this workaround. Later we will explore some of the limitations of the workaround. The implementation requires three major steps, each of which are described in the appropriate section.
- Link an ADSI server to the FootPrints database server.
- Create a view of the LDAP server in the FootPrints database.
- Configure the FootPrints Address Book to use the new database view.
When complete, FootPrints 12 address book queries will be made against LDAP via the ADSI connector in SQL Server, which will also provide the Manager’s login ID in a format (sAMAccountName or distinguishedName) that is usable to the FootPrints 12 approval engine.
Link the ADSI Server
The blog post How to: Use SQL Server to query Active Directory describes how to perform this step, with a modification. For brevity we will not repeat the details in that post. You will perform these configurations in the database server used by FootPrints 12.
Before attempting this process, you will need a service account in AD whose login ID and password are known to you. Please do not use your own login because that password will probably be changed every 60-90 days. Administrator privileges are not required for this function.
In the Security tab, in the options under “For the login not defined in the list above, connections will:” if you use the choice “Be made using login’s current security context” as per the article, FootPrints will display error messages during contact searches because the Apache Tomcat service used by FootPrints does not have an appropriate security context.
Please choose instead “Be made using this security context” and enter the domain, login ID, and password for the AD service account.
Below is a summary of the settings that will be used in the Linked Server.
Linked server: ADSI
Server type: Other data source
Provider: OLE DB Provider for Microsoft Directory Services
Product name: Active Directory Services 2.5
Data source: adsdatasource
Provider string: ADSDSOObject
Creating a View
In the FootPrints database, generally fpscdb001, you will create a new view. You can paste the following query into the new view. Please note that the LDAP server name and domain names will need to be tailored to your environment. This view includes commonly used fields but additional fields may be required for your environment.
SELECT TOP 901 usr.sAMAccountName, usr.givenName, usr.sn, usr.department, usr.mail, usr.displayName, usr.title, usr.telephoneNumber, usr.physicalDeliveryOfficeName, mgr.sAMAccountName as ManagerId, manager
(ADSI, 'SELECT manager, distinguishedName, mail, sAMAccountName, displayName, sn, givenName, userPrincipalName, department, title, telephoneNumber, physicalDeliveryOfficeName
WHERE objectClass = ''Person'' AND displayName = ''*'' ') as usr
LEFT OUTER JOIN OPENQUERY
(ADSI, 'SELECT distinguishedName, sAMAccountName
WHERE objectClass = ''Person'' AND directReports = ''*'' ') as mgr
ON usr.manager = mgr.distinguishedName
You can now save the new view. When you do save the view, SQL Server Management Studio will ask you for the name of the view. You should provide a descriptive name such as v_AddressBook_LDAP.
Configuring the Address Book
This section is pending.
Configuring the Workspace Item
Having configured the Address Book with a field Manager ID that maps to the ManagerID attribute in the v_AddressBook_LDAP view, you will now need to configure an identically named field, Manager ID, in the workspace-type record definition, and link the field to the Link Control.
In the Workflow Process you can now configure an approval state to use the Manager ID as a dynamic approver.
Screen shots are pending.
The performance of address book searches using this method appears to be equivalent to queries directly against LDAP. However, there are a few caveats and limitations.
- The method does not support approvals by all managers of all linked contacts. Only the most recently linked contact is supported, because the most recently linked contact is the one that populated the Manager ID field in the workspace record.
- The Manager ID field must at least be on the agent form and be editable at least by the agent. There is currently no way for this field to be read-only.
- By default this method supports only 1,000 records in the query. If the query to Active Directory returns more than 1,000 records, it will be truncated. Pending method to override the default limitation.
- The query above is restricted to a single OU search in Active Directory. The UNION query command will be required to to perform simultaneous searches against multiple organizational units.
Editors Note: We have an immediate need for this information on a customer engagement. We will update the post in the near future.