+3
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
Best answer by Mark Jongeling
View original
+21
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 [YOUR_IAM_DATABASE]
GO
/****** Object: StoredProcedure [dbo].[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 [dbo].[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. 😄
+21
Hi,
Has this been resolved with the edited Stored procedure or do you require further assistance?
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.