How to import user group memberships from AD to an SQL Server database

I am currently setting up an Active Directory import for a non-.NET system at work with a very limited set of tools. Essentially, I only have what the standard LDAP query can fetch me and what little logic I can implement in Transact-SQL on our SQL Server. The big problem is that LDAP refuses to output N-to-M relations as anything other than “unique N + all related Ms concatenated into a single string” tuplets. Relational databases, on the other hand, refuse to split a single string field back into multiple records. This conflict of interests can only be solved by application-layer logic but the application I am working with cannot do that and I don’t want to involve any more middleware here.

So, after extensive googling, I have pieced together a solution based mainly on following:

To use my function you will need a table named “temp_ldap_import_group” with following columns:

  • group_name: The name of the group in AD.
  • members: The concatenated list of group members’ Distinguished Names. I use a non-indexed text data type here.

Its contents after importing the data from Active Directory (I assume you already managed this part) should look something like this:

group_name  members
----------- -------------------------------------------------
Group1      CN=Surname, Name,OU=ExternalUsers,OU=Users,OU=...
Group2      CN=Surname, Name,OU=InternalUsers,OU=Users,OU=...
Group3      CN=Surname, Name,OU=InternalUsers,OU=Users,OU=...

With that, you can use the following function:

CREATE FUNCTION [dbo].[fn_ParseLDAPGroupMemberships] ()
RETURNS @t TABLE ( group_name VARCHAR(50), user_dn VARCHAR(200) )
AS
  BEGIN
    -- Declare all variables
    DECLARE @iGroupLoopControl    int,
            @iNextGroupName       VARCHAR(50),
            @iCurrentGroupName    VARCHAR(50),
            @iCurrentGroupMembers VARCHAR(MAX),
            @xml                  XML
  
    -- Initialize variables
    SET @iGroupLoopControl = 1
    SELECT @iNextGroupName = MIN(group_name) FROM temp_ldap_import_group
  
    -- Start the main processing loop
    WHILE @iGroupLoopControl = 1
      BEGIN
        -- Select the data row
        SELECT @iCurrentGroupName = group_name, @iCurrentGroupMembers = members FROM temp_ldap_import_group WHERE group_name = @iNextGroupName
        
        -- Parse members into XML
        SET @xml = N'<root><r>' + REPLACE(@iCurrentGroupMembers, 'CN=', '</r><r>') + '</r></root>'
        
        -- Save the XML
        INSERT INTO @t(group_name, user_dn)
          SELECT @iCurrentGroupName, r.value('.','VARCHAR(200)')
          FROM @xml.nodes('//root/r[string-length(.) > 0]') AS RECORDS(r)
        
        -- Reset the looping variable
        SET @iNextGroupName = NULL
        SELECT @iNextGroupName = MIN(group_name) FROM temp_ldap_import_group WHERE group_name > @iCurrentGroupName
        -- Did we get a valid next row id?
        IF ISNULL(@iNextGroupName,'__NULL__') = '__NULL__'
          BEGIN
            SET @iGroupLoopControl = 0
          END
      END
    RETURN
  END;

To generate following the results:

group_name   user_dn
------------ ----------------------------------------------
Group1       Surname, Name,OU=SharedUsers,OU=Users,...,
Group1       Surname, Name,OU=InternalUsers,OU=Users,...,
Group1       Subgroup1,OU=Groups,...,
Group1       Subgroup2,OU=Groups,...,
Group2       Surname, Name,OU=InternalUsers,OU=Users,...,
Group2       Surname, Name,OU=InternalUsers,OU=Users,...,
Group3       Surname, Name,OU=InternalUsers,OU=Users,...,

Of course, this solution is not ideal (the data still needs to be saved somewhere, the AD DNs have to be parsed, etc.) but the main task, splitting the concatenated users into distinct tuplets, is thus accomplished.

Hope it helps.

This entry was posted in active directory, sql, sql server. Bookmark the permalink.

2 Responses to How to import user group memberships from AD to an SQL Server database

  1. Makito26Yt81 says:

    Very, very nice page! 🙂

Comments are closed.