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:
- Nathan Wheeler’s non-recursive, XML-based user function to split concatenated strings back into tuplets
- How to Perform SQL Server Row-by-Row Operations Without Cursors by David VanDeSompele
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.