Hi,
we are using an On Premise Windows AD-server to administrate the users assigned to a Thinkwise application group.
Now we run into a case where AD Group A has 10 users, and AD Group B has added AD Group A as a member.
When now syncing the IAM to get the users from AD Group B, we don't get any users.
Is this missing functionality ? Can't find the answer in the IAM documentation.
Thanks.
best regards,
Cyril
Hi Cyril,
Together with my colleague we looked into this, but currently it's not possible. However, we did find a possible solution using LDAP_MATCHING_RULE_IN_CHAIN (:1.2.840.113556.1.4.1941:).
Below I have added an Alteration of the task_import_active_directory_grp task of IAM (2022.2):
USE EYOUR_IAM_DATABASE]
GO
/****** Object: StoredProcedure edbo].]task_import_active_directory_grp] Script Date: 27-9-2022 15:45:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure edbo].]task_import_active_directory_grp]
(
@tenant_id "tenant_id" ,
@usr_grp_id "usr_grp_id" ,
@active_directory_grp_name "active_directory_grp_name" ,
@active_directory_domain_name "active_directory_domain_name" ,
@net_bios_domain_name "net_bios_domain_name" ,
@user_name "user_name" ,
@password "password"
)
as
begin
-- Do not count affected rows for performance
SET NOCOUNT ON;
--control_proc_id: task_import_active_directory_grp
--template_id: task_import_active_directory_grp
--prog_object_item_id: sf_gui_task_import_active_directory_group
--template_description: Imports all users from a Active Direcory group in a usr_grp
declare @query nvarchar(4000)
declare @path varchar(1000)
declare @error_msg as varchar(1000)
begin tran
begin try
-- Delete the existing user from the group.
delete from usr_grp_usr
where tenant_id = @tenant_id
and usr_grp_id = @usr_grp_id
-- Determine the LDAP path for the group
set @query =
'select @ADsPath = ADsPath
from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
SELECT ADsPath
FROM ''''LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + '''''
WHERE name =''''' + @active_directory_grp_name + '''''
'') a'
exec sp_executesql @query, N'@ADsPath varchar(1000) output', @ADsPath= @path output
if @path is null
begin
exec tsf_send_message 'active_directory_grp_not_found', null, 1
rollback tran
return
end
set @path = substring(@path, len('LDAP://') + 1, len(@path))
-- Give a different message for the root admin as for another admin
-- So we don't give up information about users of other tenants
if exists( select 1
from usr_root_admin
where usr_id = dbo.tsf_user()
)
begin
set @error_msg = 'tenant_mismatch_for_root_admin'
end
else
begin
set @error_msg = 'tenant_mismatch_for_non_root_admin'
end
-- Check if the user does not exist for another tenant
--set @query =
--'if exists (select 1
--from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
-- SELECT
-- sAMAccountName
-- FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + '''''
-- WHERE objectCategory=''''Person''''
-- AND objectCategory=''''user''''
-- AND memberof =''''' + @path + '''''
-- AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
--'') a
--join usr_general u
-- on u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default
--where u.tenant_id <> ' + cast(@tenant_id as varchar(10)) + ')
--begin
-- exec tsf_send_message ''' + @error_msg + ''', null, 0
--end
--'
set @query =
'if exists (select 1
from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
SELECT
sAMAccountName
FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + '''''
WHERE objectCategory=''''Person''''
AND objectCategory=''''user''''
AND ''''memberof:1.2.840.113556.1.4.1941:''''=''''' + @path + '''''
AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
'') a
join usr_general u
on u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default
where u.tenant_id <> ' + cast(@tenant_id as varchar(10)) + ')
begin
exec tsf_send_message ''' + @error_msg + ''', null, 0
end
'
exec (@query)
-- Update users if they exist
--set @query =
--'update u
-- set first_name = a.givenName,
-- sur_name = a.sn,
-- email = a.mail,
-- update_user = dbo.tsf_user(),
-- update_date_time = sysdatetime()
--from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
-- SELECT name
-- ,sn
-- ,sAMAccountName
-- ,givenName, mail
-- FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + '''''
-- WHERE objectCategory=''''Person''''
-- AND objectCategory=''''user''''
-- AND memberof =''''' + @path + '''''
-- AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
--'') a
--join usr_general u
-- on u.tenant_id = ' + cast(@tenant_id as varchar(10)) + '
-- and u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default
--where (u.first_name <> a.givenName or u.first_name is null and a.givenName is not null or u.first_name is not null and a.givenName is null
-- or u.sur_name <> a.sn or u.sur_name is null and a.sn is not null or u.sur_name is not null and a.sn is null
-- or u.email <> a.mail or u.email is null and a.mail is not null or u.email is not null and a.mail is null)
--'
set @query =
'update u
set first_name = a.givenName,
sur_name = a.sn,
email = a.mail,
update_user = dbo.tsf_user(),
update_date_time = sysdatetime()
from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
SELECT name
,sn
,sAMAccountName
,givenName, mail
FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + '''''
WHERE objectCategory=''''Person''''
AND objectCategory=''''user''''
AND ''''memberof:1.2.840.113556.1.4.1941:''''=''''' + @path + '''''
AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
'') a
join usr_general u
on u.tenant_id = ' + cast(@tenant_id as varchar(10)) + '
and u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default
where (u.first_name <> a.givenName or u.first_name is null and a.givenName is not null or u.first_name is not null and a.givenName is null
or u.sur_name <> a.sn or u.sur_name is null and a.sn is not null or u.sur_name is not null and a.sn is null
or u.email <> a.mail or u.email is null and a.mail is not null or u.email is not null and a.mail is null)
'
exec (@query)
-- Determine the users in the group, add the users to IAM if they do not yet exist.
--set @query =
--'insert into usr
--(
-- tenant_id,
-- usr_id,
-- first_name,
-- sur_name,
-- name,
-- gender,
-- email,
-- time_zone_id,
-- appl_lang_id,
-- authentication_type,
-- two_factor_authentication_type,
-- allow_fallback_to_email,
-- allow_change_password,
-- password_changed_count,
-- password_forgotten_count,
-- write_back_up_type_id,
-- insert_user,
-- insert_date_time,
-- update_user,
-- update_date_time
--)
--select
-- ' + cast(@tenant_id as varchar(10)) + ',
-- ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + ' a.sAMAccountName as usr_id,
-- a.givenName as first_name,
-- a.sn as sur_name,
-- a.givenName as name,
-- 0, -- Unknown
-- a.mail as email,
-- ''Etc/UTC'',
-- ''ENG'',
-- 1, -- Windows
-- 0, -- Password
-- 0, -- No
-- 0, -- No
-- 0, -- Changed count
-- 0, -- Forgotten count
-- 1, -- None
-- dbo.tsf_user(),
-- sysdatetime(),
-- dbo.tsf_user(),
-- sysdatetime()
--from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
-- SELECT name
-- ,sn
-- ,sAMAccountName
-- ,givenName, mail
-- FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + '''''
-- WHERE objectCategory=''''Person''''
-- AND objectCategory=''''user''''
-- AND memberof =''''' + @path + '''''
-- AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
--'') a
--where not exists (select 1
-- from usr u
-- -- do not check for tentant, because the usr id must be unique
-- where u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default)'
set @query =
'insert into usr
(
tenant_id,
usr_id,
first_name,
sur_name,
name,
gender,
email,
time_zone_id,
appl_lang_id,
authentication_type,
two_factor_authentication_type,
allow_fallback_to_email,
allow_change_password,
password_changed_count,
password_forgotten_count,
write_back_up_type_id,
insert_user,
insert_date_time,
update_user,
update_date_time
)
select
' + cast(@tenant_id as varchar(10)) + ',
' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + ' a.sAMAccountName as usr_id,
a.givenName as first_name,
a.sn as sur_name,
a.givenName as name,
0, -- Unknown
a.mail as email,
''Etc/UTC'',
''ENG'',
1, -- Windows
0, -- Password
0, -- No
0, -- No
0, -- Changed count
0, -- Forgotten count
1, -- None
dbo.tsf_user(),
sysdatetime(),
dbo.tsf_user(),
sysdatetime()
from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
SELECT name
,sn
,sAMAccountName
,givenName, mail
FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + '''''
WHERE objectCategory=''''Person''''
AND objectCategory=''''user''''
AND ''''memberof:1.2.840.113556.1.4.1941:''''=''''' + @path + '''''
AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
'') a
where not exists (select 1
from usr u
-- do not check for tentant, because the usr id must be unique
where u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default)'
exec (@query)
-- Add the users to the user group.
--set @query =
--'insert into usr_grp_usr
--(
-- tenant_id,
-- usr_grp_id,
-- usr_id,
-- insert_user,
-- insert_date_time,
-- update_user,
-- update_date_time
--)
--select
-- ' + cast(@tenant_id as varchar(10)) + ',
-- ''' + @usr_grp_id + ''',
-- ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'sAMAccountName,
-- dbo.tsf_user(),
-- sysdatetime(),
-- dbo.tsf_user(),
-- sysdatetime()
--from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
-- SELECT name
-- ,sn
-- ,sAMAccountName
-- ,givenName, mail
-- FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + '''''
-- WHERE objectCategory=''''Person''''
-- AND objectCategory=''''user''''
-- AND memberof =''''' + @path + '''''
-- AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
--'') a
--where not exists (select 1
-- from usr_grp_usr u
-- where u.tenant_id = ' + cast(@tenant_id as varchar(10)) + '
-- and u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default
-- and u.usr_grp_id = ''' + @usr_grp_id + ''')
-- -- Only add users from the same tenant as the user group
-- and exists (select 1
-- from usr u
-- where u.tenant_id = ' + cast(@tenant_id as varchar(10)) + '
-- and u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default)'
set @query =
'insert into usr_grp_usr
(
tenant_id,
usr_grp_id,
usr_id,
insert_user,
insert_date_time,
update_user,
update_date_time
)
select
' + cast(@tenant_id as varchar(10)) + ',
''' + @usr_grp_id + ''',
' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'sAMAccountName,
dbo.tsf_user(),
sysdatetime(),
dbo.tsf_user(),
sysdatetime()
from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
SELECT name
,sn
,sAMAccountName
,givenName, mail
FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + '''''
WHERE objectCategory=''''Person''''
AND objectCategory=''''user''''
AND ''''memberof:1.2.840.113556.1.4.1941:''''=''''' + @path + '''''
AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
'') a
where not exists (select 1
from usr_grp_usr u
where u.tenant_id = ' + cast(@tenant_id as varchar(10)) + '
and u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default
and u.usr_grp_id = ''' + @usr_grp_id + ''')
-- Only add users from the same tenant as the user group
and exists (select 1
from usr u
where u.tenant_id = ' + cast(@tenant_id as varchar(10)) + '
and u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default)'
exec (@query)
-- When no errors have occurred, update the user group with the new AD information, if needed.
update usr_grp
set usr_grp_type = 1,
active_directory_domain_name = @active_directory_domain_name,
active_directory_grp_name = @active_directory_grp_name,
net_bios_domain_name = @net_bios_domain_name,
active_directory_updated_on = sysdatetime(),
update_user = dbo.tsf_user(),
update_date_time = sysdatetime()
where tenant_id = @tenant_id
and usr_grp_id = @usr_grp_id
end try
begin catch
if error_number() = 7321
begin
exec tsf_send_message 'active_directory_access_denied', null, 1;
end
else
throw;
if @@trancount > 0
rollback transaction;
end catch;
if @@trancount > 0
commit transaction;
end
I have commented out the original code and copy paste it below it including the LDAP matching rule in chain. You can try this to see if that helps your situation. 
Hi,
Has this been resolved with the edited Stored procedure or do you require further assistance?
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.