You are viewing limited content. For full access, please sign in.

Discussion

Discussion

Active Directory as a Forms Data Source

posted on January 13, 2018 Show version history

Hello All,

During this year's Empower I ran into a lot of people who mentioned the topic of retrieving AD attributes using form field lookups. As a result, I thought people might appreciate a post detailing one way in which this can be accomplished.

Before I begin, there are two important "gotchas" to consider.

  1. The limitations of SQL/AD (ADSI) interactions prevent retrieval of more that 900 results, however, there is a workaround for this and I will provide an example.
  2. This approach will not support multi-value AD attributes. For example, roomNumber allows more than one value to be stored and attempting to retrieve anything from these attributes will cause the queries to fail. Unfortunately, I am not aware of any way around this limitation (If you have one, please do chime in!).

Additionally, I will provide a link at the end of the post that will provide more detailed instructions for creating the linked server and utilizing OpenQuery to pull data from Active Directory.

The following approach utilizes SQL linked servers, and ADSI (Active Directory Service Interfaces) OpenQuery.

Step 1

On your desired SQL server, create a linked server pointed at ADSI; this can be done a few different ways, such as with the built-in linked server stored procedure. For example,

USE [master]
GO 
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'DOMAIN\USER',@rmtpassword='*********'
GO 

For this to work, you'll need to provide credentials for an account that has rights to access the attributes you would like to retrieve from AD.

There's a lot of different things to configure, so please reference the link at the end of this post if you're not already familiar with the process of creating linked servers and/or ADSI.

 

Step 2

Verify the linked server was created successfully, and test out the connection by building and running a simple query. ADSI OpenQuery is essentially a query string within a query, so you have to be very careful about placement of your quotes.

The following example will pull the display name, phone number, and email address for all AD users.

SELECT * FROM OpenQuery ( 
  ADSI,  
  'SELECT displayName, telephoneNumber, mail 
  FROM  ''LDAP://DOMAIN.com/OU=Players,DC=DOMAIN,DC=com'' 
  WHERE objectCategory =  ''Person'' 
  ') AS adtbl

However, if you have 900+ users, this query will fail.

So, how do we get around this? By breaking our queries up into smaller chunks and merging them together after the fact.

One relatively simple way to accomplish this is by breaking groups up alphabetically. For example, we change the Where condition to

SELECT * FROM OpenQuery ( ADSI, 'SELECT displayName, telephoneNumber, mail FROM ''LDAP://DOMAIN.com/OU=Players,DC=DOMAIN,DC=com'' WHERE objectCategory = ''Person'' AND samAccountName = ''A*'' ') AS adtblA

After that, you can create a result group for each letter A-Z (or however you are breaking them up) and use UNION ALL to merge the results in a pre-built view that returns a list of all the users you want.

If you want the information for a specific user, you can create a stored procedure instead, and set something like name, account, etc, as your parameter.

CREATE PROCEDURE [dbo].[sp_getADUserByAccount] 
	-- Add the parameters for the stored procedure here
	@DomainUser varchar(25)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	/* copy parameters to temporary variables to prevent parameter sniffing */
	DECLARE @OPENQUERY nvarchar(2050)
	DECLARE @xUserDomainName varchar(100)
	DECLARE @Blank varchar(1)
	SET @Blank = ' '
	
	DECLARE @xDomainUser varchar(25)

        /* strip out domain if necessary */
	SELECT @xDomainUser = SUBSTRING(@DomainUser, CHARINDEX ( '\' , @DomainUser,0 )+1,25)
	
        /* dynamically build search string component to target specific account */
	SELECT @xUserDomainName = ' sAMAccountName = ''''' + @xDomainUser + ''''''	
        
        SET @OPENQUERY =	'SELECT	adU.employeeNumber as personnelNumber, adU.givenName AS firstName, adU.sn AS lastName, adU.initials AS middleInitial, adU.mail AS eMail, adU.manager AS manager, adU.department AS department, adU.title AS title, adU.name AS firstAndLastName, adU.[msDS-PrincipalName] AS domainAccount, adU.userPrincipalName, adU.displayname, adU.telephoneNumber, adU.employeeType, adU.physicalDeliveryOfficeName AS buildingName, mobile
            FROM	OPENQUERY(ADSI,
									''SELECT givenName, sn, initials, mail, employeeNumber, manager, department, title, name, msDS-PrincipalName, userPrincipalName, displayname, telephoneNumber, employeeType, physicalDeliveryOfficeName, mobile
									FROM	''''LDAP://DOMAIN/OU=Users,DC=DOMAIN,DC=local''''
									WHERE	objectCategory = ''''Person'''' AND 
											' + @UserDomainName + ''') AS adU'
	EXEC(@OPENQUERY)
END

Again, not going to go into too much detail on building the actual stored procedure and underlying openquery string because honestly it is not my strongest area. However, the link coming at the end of the post should help more with those finer details.

Test your view/sp directly in SQL to make sure everything is working and returning the correct/desired values before proceeding.

Step 3

Go into Forms administration, and make sure the database associated with your new views/stored procedures is set up as a data source, then link it to the form on which you would like to pull the information.

Step 4

On your form, go into your lookup rules, set your data source, and choose your view and set the condition, or choose your sp and set the input parameter.

Set the values you would like put into various fields. Note: views will return a list of available columns, but stored procedures variables must be entered manually so make sure they match exactly.

Step 5

Test out your form and make sure the desired values are being retrieved.

 

In our environment, we use this for many different things, such as

  1. Pulling a list of all AD users in a drop down
  2. Retrieving a list of a manager's subordinates as suggestions in a single line field
  3. Retrieving a user's building, phone number, etc.
  4. Building a list of users in a specific building
  5. Retrieving lists of non-user objects like distribution groups
  6. Even retrieving a business process initiator's manager in Workflow to send out alerts/notifications when certain issues arise.

 

As promised, the following link will provide much more useful instructions on the setup of ADSI in SQL.

Click here for instructions on configuring ADSI linked servers

I highly recommend looking at your ADSI Editor to see what attributes you can access and which ones your organization is actually utilizing because that's the only surefire way to know what is going to give you the information you want/need.

I hope this post proves helpful and please let me know if you spot any typos or think anything could use more clarification.

Happy Laserfiching!

19 0
replied two days ago

This guide seems helpful. I know I'm going to hit a wall with the 900 limit though. We are planning on doing this for a client who has over 100k AD accounts. Any good ideas on a way to split that up in <900 chunks?

0 0
replied two days ago

That's a tough one. To be honest we don't even use this approach anymore.

Instead, I built a standalone application with some API functionality so we could do more with AD account management in general.

The app runs a sync process every 10 minutes using the DirectoryServices libraries; this got around the ADSI limits, the issues with multi-value attributes, and other various other shortcomings.

 

I'd be worried about SQL struggling with 100k accounts through ADSI (it is not especially efficient) but splitting it up into smaller chunks is really going to depend on what kind of attributes are reliably populated.

For example, if they have people in a lot of different locations, then you could filter on the location attribute, or you could use a combination of filters.

Another option might be OUs if they are broken up into manageable sizes and don't change often.

0 0
replied two days ago

Hi Dylan-

We're using this, and I have two strategies to keep from hitting that limit (the second one implemented recently when the first proved not to be good enough). Note that we're definitely less than 100,000 so you may need to come up with some additional tricks.

The first is that our stored procedure essential iterates through the alphabet, so builds this up one letter at a time, A-Z, then handles accounts starting with numbers, iterating from -1 through 9

The second step was to limit to just active accounts and ignore those that have been disabled. This is flagged with a userAccountControl value of 512 (regular accounts) or 66048 (accounts with passwords that never expire; generally service accounts and something I may end up removing)

Here's the SQL for the stored procedure. I don't recall why it recreates both the table and the view. It's been over a year and a developer on my team came up with it. Change MYDOMAIN to your domain name.

CREATE PROCEDURE [dbo].[sp_ADSync] 
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @queryFormat VARCHAR(MAX) = '
	SELECT ''MYDOMAIN\'' + sAMAccountname as accountName, mail as email, physicalDeliveryOfficeName as branch, displayName
		FROM OPENQUERY(ADSI, ''
		SELECT displayName, mail, physicalDeliveryOfficeName, sAMAccountname
		FROM ''''LDAP://MYDOMAIN.com/DC=MYDOMAIN,DC=com''''
		WHERE objectClass = ''''User'''' AND objectCategory = ''''Person''''
			AND (userAccountControl = ''''512'''' OR userAccountControl = ''''66048'''') AND samAccountName = ''''#p0'''''')';

	DECLARE @sql VARCHAR(MAX) = 'CREATE VIEW [vw_ADDetails] AS ';
	DECLARE @asciiValue INT = ASCII('A');
	DECLARE @asciiEnd INT = ASCII('Z');
	WHILE @asciiValue <= @asciiEnd BEGIN 
		SET @sql = @sql + replace(@queryFormat, '#p0', CHAR(@asciiValue) + '*');
		IF @asciiValue < @asciiEnd  SET @sql = @sql + ' UNION ALL ';
		SET @asciiValue = @asciiValue + 1;
	END
	--PRINT @sql;

	SET @sql = @sql + ' UNION ALL ';
	DECLARE @asciiNumValue INT = ASCII(-1);
	DECLARE @asciiNumEnd INT = ASCII(9);
	WHILE @asciiNumValue <= @asciiNumEnd BEGIN 
		SET @sql = @sql + replace(@queryFormat, '#p0', CHAR(@asciiNumValue) + '*');
		IF @asciiNumValue < @asciiNumEnd  SET @sql = @sql + ' UNION ALL ';
		SET @asciiNumValue = @asciiNumValue + 1;
	END

	IF OBJECT_ID('[vw_ADDetails]') IS NOT NULL DROP VIEW [vw_ADDetails]
	EXEC(@sql);

	IF OBJECT_ID('[ADDetails]') IS NOT NULL DROP TABLE [ADDetails]

	SELECT *
	  INTO ADDetails
	  FROM vw_ADDetails
END

 

0 0
replied on September 11, 2019

Hi,  I am new to Laserfiche and Forms.  Can you screen shot steps 3 and 4? We have Forms 10.4 and I do not see where to add a data source in forms.  Thank you.

formsadmin.png
formsadmin.png (70.66 KB)
0 0
replied on September 11, 2019

You don't appear to have it:

0 0
replied on September 11, 2019 Show version history

Check which version you have. Forms Essentials is the "basic" version, but data sources are only available in Forms Professional.

Click your name in the top right, then select "About" to see the exact version.

0 0
replied on September 11, 2019

Yes, we have Forms Essential.  This is unfortunate, as we need this for routing to the current people.   Do you know of a way to do this with forms essential?  Can we have workflow do it and add it to the forms process?

0 0
replied on September 11, 2019

Workflow can query databases, so you should be able to have a workflow retrieve the data then use a Set Business Process Variables activity to send the data back to the form.

2 0
replied on September 11, 2019

Thank you!  We will try to use workflow to get this achieved.  

0 0
replied on January 13, 2018

This is a great resource! If your process can tolerate some delay, one way to get around some of the limitations is to warehouse the data. Every hour we run queries similar to what you posted, and write the results in a table that contains all of the fields we need. This way, the data that Forms sees is simplified and formatted correctly.

5 0
replied on May 24, 2018

That is what we do too, and it rewrites the table on schedule.

0 0
replied on May 24, 2018 Show version history

We actually ended up modifying our process to do something similar because we had a need to access multivalue AD attributes.

Instead of running the queries in real-time, we have a SQL activity that runs a 6-step process every 30 minutes and uses the Merge statement to update any rows that have new values.

To get the multivalue attributes, we have one of the steps kick off a PowerShell script that builds them into a CSV, then the next step imports those values into a temp table and joins them to the rest of the data.

0 0
replied on May 24, 2018

This is what we are doing: https://answers.laserfiche.com/questions/134381/Workflow-action-Query-active-directory#134399 query recreates the table every 6 hours (we only use it for employee Lookups).

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.