Deployment Server / Active Directory Synchronization

This guide will help you set up Deployment Server Computer Groups to automatically synchronize to your Active Directory Organizational Units.

First, you will need to verify that all the appropriate credentials are found in your Domain Accounts List. Go to Tools > Options > Domain Accounts. Click the Add button and fill out the information of the Domain Account for each of your Domains. This account should have permissions to query Active Directory to determine Organizational Unit membership. It is also the account used for joining computers to the Domain, so it should have permissions to do a Domain Join.

Click to view.

Second, you will need to have either the AClient or DAgent (for Vista) installed on all of your computers. AClient reports Organizational Unit membership to Deployment Server. If you are importing computers from a .csv or .xls file, make sure to include domain and Organizational Unit information in the import file.

Third, run the following SQL Query against your Deployment Server database.

1. Copy everything between "--------Start Here--------" and "--------End Here--------"

   --------Start Here--------
   IF EXISTS (SELECT * FROM sysobjects WHERE name='ou2group' AND xtype='TR')
   DROP TRIGGER ou2group
   GO
   CREATE TRIGGER ou2group ON computer AFTER UPDATE AS
   IF (UPDATE(msnet_domain_ou) OR UPDATE(msnet_dns_domain))
   BEGIN
   DECLARE @Done bit
   DECLARE @Left int
   DECLARE @Right int
   DECLARE @GroupName varchar(64)
   DECLARE @DomainOu varchar(256)
   DECLARE @ParentID int
   DECLARE @GroupID int
   
   SELECT @Done = 0, @Left = 0, @Right = 0
   SELECT @GroupName = msnet_dns_domain, @DomainOU = msnet_domain_ou FROM INSERTED
   SELECT @ParentID = group_id FROM computer_group WHERE parent_id IS NULL AND name = @GroupName
   
   IF @ParentID IS NULL
   BEGIN
   EXEC ins_group @GroupName, @ParentID
   SELECT @ParentID = group_id FROM computer_group WHERE parent_id IS NULL AND name = @GroupName
   END
   
   WHILE @Done = 0
   BEGIN
   SET @Left = @Right + 1
   SET @Right = CHARINDEX('/', @DomainOU, @Left)
   IF @Right = 0
   BEGIN
   SET @Right = LEN(@DomainOU) + 1
   SET @Done = 1
   END
   SET @GroupName = SUBSTRING(@DomainOU, @Left, @Right - @Left)
   SET @GroupID = NULL
   SELECT @GroupID = group_id FROM computer_group WHERE parent_id = @ParentID AND name = @GroupName
   IF @GroupID IS NULL
   BEGIN
   EXEC ins_group @GroupName, @ParentID
   SELECT @ParentID = group_id FROM computer_group WHERE parent_id = @ParentID AND name = @GroupName
   END
   ELSE
   SET @ParentID = @GroupID
   END
   SELECT @GroupID = group_id FROM INSERTED
   IF((SELECT COUNT(1) FROM computer WHERE group_id = @GroupID) < 2)
   DELETE FROM computer_group WHERE group_id = @GroupID
   UPDATE computer SET group_id = @ParentID WHERE computer_id = (SELECT computer_id FROM INSERTED)
   END
   GO
   --------End Here--------

   
   



2. Open SQL Query Analyzer
   
   
   
   1. For SQL 2000, open Start > All Programs > Microsoft SQL Server>Query Analyzer
   
   
   
   2. For SQL 2005, open Start > All Programs > Microsoft SQL Server 2005>SQL Server Management Studio and click on the "New Query" button
   
   
   
   



3. In the Database Drop-down, select your Deployment Server Database (default is eXpress)
   

   

   
   



4. Paste the SQL Query that you copied in step 1 into the Query Window



5. Press the F5 key to execute the query



6. Check the status message to verify that the query has executed completely

You have just added a SQL Trigger that will be run every time a computer record is updated. When it is run, it will add the computer to a Computer Group that matches its Active Directory Organizational Unit membership. You might want to force your computers to update so that the membership is updated immediately. To force an update, open the Deployment Server Console and select View > Reset Client Connections. When the computers connect back to Deployment Server, you will see that they are automatically added to a Computer Group structure that matches your Active Directory Organizational Unit.

The SQL Trigger

I will now explain, step-by-step, what the SQL Trigger does.

1. We declare all of the variables we will use. Here is a brief description of each.
   

   DECLARE @Done bit
   
   
   A true/false value that will be set to 1 (true) when all groups for the computer's Organizational Unit have been created

   
   
   

   DECLARE @Left int
   
   
   This is the character position where the current Organizational Unit we are looking at starts

   
   
   

   DECLARE @Right int
   
   
   This is the character position where the current Organizational Unit we are looking at ends

   
   
   

   DECLARE @GroupName varchar(64)
   
   
   This is the name of the Organizational Unit we are looking at

   
   
   

   DECLARE @DomainOU varchar(256)
   
   
   This is the entire Organizational Unit in the format Organizational Unit/Organizational Unit/Organizational Unit/...

   
   
   

   DECLARE @ParentID int
   
   
   This is the Computer Group number of the parent group of the computer or the Organizational Unit we are looking at

   
   
   

   DECLARE @GroupID int
   
   
   This is the Group number of the Organizational Unit we are looking at

   
   



2. We determine if a Computer Group already exists for the Domain Name. If it does, we remember the group number.
   

   SELECT @GroupName = msnet_dns_domain, @DomainOU = msnet_domain_ou FROM INSERTED
   SELECT @ParentID = group_id FROM computer_group WHERE parent_id IS NULL AND name = @GroupName

   
   



3. If the Domain Computer Group does not exist, we create one and remember the group number.
   

   IF @ParentID IS NULL
   BEGIN
   EXEC ins_group @GroupName, @ParentID
   SELECT @ParentID = group_id FROM computer_group WHERE parent_id IS NULL AND name = @GroupName
   END

   
   



4. For each Organizational Unit listed in DomainOU, we check to see if a group already exists. If it does, we remember the group number, otherwise we create one and remember the group number.
   

   WHILE @Done = 0
   BEGIN

   
   
   
   
   
   1. Find the next Organizational Unit Name and remember it as DomainOU
      

      SET @Left = @Right + 1
      SET @Right = CHARINDEX('/', @DomainOU, @Left)
      IF @Right = 0
      BEGIN
      SET @Right = LEN(@DomainOU) + 1
      SET @Done = 1
      END
      SET @GroupName = SUBSTRING(@DomainOU, @Left, @Right - @Left)

      
      
   
   
   
   2. Determine if the Computer Group already exists.
      

      SET @GroupID = NULL
      SELECT @GroupID = group_id FROM computer_group WHERE parent_id = @ParentID AND name = @GroupName

      
      
   
   
   
   3. If the group doesn't already exist, create one and remember the group ID.
      

      IF @GroupID IS NULL
      BEGIN
      EXEC ins_group @GroupName, @ParentID
      SELECT @ParentID = group_id FROM computer_group WHERE parent_id = @ParentID AND name = @GroupName
      END

      
      
   
   
   
   4. If the group does already exist, remember the group ID
      

      ELSE
      SET @ParentID = @GroupID
      END

      
      
   
   
   
   



5. After we have created the entire Organizational Unit structure in Deployment Server, we check to see if the computer was the last one in the group, if it was, remove the group.
   

   SELECT @GroupID = group_id FROM INSERTED
   IF((SELECT COUNT(1) FROM computer WHERE group_id = @GroupID) < 2)
   DELETE FROM computer_group WHERE group_id = @GroupID

   
   



6. Last, it adds the computer to the correct Organizational Unit Group in Deployment Server.
   

   UPDATE computer SET group_id = @ParentID WHERE computer_id = (SELECT computer_id FROM INSERTED)