Saturday 17 May 2008

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)


No comments:

Post a Comment