: Active Directory Importer – Pulling In Data from Multiple Sub-Domains

Products

• Information Centric Analytics
• Data Loss Prevention Core Package

Issue/Introduction

Not able to bring in AD data from sub-domain domain controllers. - Two and Three Tier

Error Message: None. The indicating issue is when querying the ACtiveDirectoryDW database, the expected number of users and computers are not within the user and computer tables.

Cause

The current version of the ADConnector import utility cannot recurse a customer’s AD structure, scan each sub-domain individually, then

Resolution

For each sub-domain in the customer environment, repeat the following steps.

1. On the ICA SQL Server, navigate to the AD Import Utility folder for the domain you will be adding DCs for.
2. Run the Update query to add the sub domain to the Data Warehouse. For the server value, replace that value with the DNS name. If the DNS name does not work, use the FQDN or IP Address.

INSERT INTO [<<ActiveDirectoryDW Name>>].[dbo].[Server] (Server, UserName) VALUES ('<<DNS Name>>:<<Port>>' , '<<DomainName>>\<<UserName>>')

 

3. Run the executable with this command line to encrypt the service account password.

<driveletter>:\ ImportADUsersAndComputers.exe -setapipassword <<ServerID>> <<PasswordForUserName>>

 

4. Run the following Stored Procedure to populate the MetadataContainers table. This sets values for the executable to read and bring in the proper AD data.

EXEC spAddServeRMetadata <ServerID>

 

5. By default, all containers from Active Directory are brought in. Best practice is to set all containers off, then only activating those containers of interest.

The first UPDATE statement deactivates all containers for the specified Server ID.

The second UPDATE statement sets the containers of interest to enabled.

UPDATE MetadataContainers SET IsEnabled = 0 WHERE ServerID = <<ServerID>> UPDATE MetadataContainers SET IsEnabled = 1 WHERE [Name] IN ('User' , 'Computer' , 'Group') AND ServerID = <<ServerID>>

 

Again, please be sure to repeat steps 1-5 for each sub-domain to be added.

Once all the sub-domains have been added, run the executable to bring in the latest AD data to the respective Active Directory DW

<driveletter>:\ ImportADUsersAndComputers.exe

 

 

 

Example

Run this query for each sub-domain to be added to the newly created DW

INSERT INTO [ActiveDirectoryDW_INTL].[dbo].[Server] (Server, UserName) VALUES ('SUBDOMAINAME' ,'ACME-INTL\RiskFabric')

 

Run the executable with this command line to encrypt the service account password.

REM CD to the folder the .exe is installed to. ImportADUsersAndComputers.exe -setapipassword 2 10.10.10.12DCPasswordHere2!

 

For this import, the customer is only interested in bringing in User and Computer data only from the ServerID of “2.” First, the customer sets all the IsEnabled values for all containers to 0, then run the following UPDATE queries:

UPDATE MetadataContainers SET IsEnabled = 0 WHERE ServerID = 2 UPDATE MetadataContainers SET IsEnabled = 1 WHERE [Name] IN ('User' , 'Computer') AND ServerID = 2

 

Once all the sub-domains have been added, run the executable to bring in the latest AD data to the respective Active Directory DW

REM CD to the folder the .exe is installed to. ImportADUsersAndComputers.exe