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!