Skip to main content
Solved

IAM Windows Domain Group inheritance

  • September 16, 2022
  • 2 replies
  • 60 views

C. Lousberg
Captain
Forum|alt.badge.img+4

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

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):

Show content
1USE [YOUR_IAM_DATABASE]
2GO
3/****** Object: StoredProcedure [dbo].[task_import_active_directory_grp] Script Date: 27-9-2022 15:45:37 ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8ALTER procedure [dbo].[task_import_active_directory_grp]
9(
10 @tenant_id "tenant_id" ,
11 @usr_grp_id "usr_grp_id" ,
12 @active_directory_grp_name "active_directory_grp_name" ,
13 @active_directory_domain_name "active_directory_domain_name" ,
14 @net_bios_domain_name "net_bios_domain_name" ,
15 @user_name "user_name" ,
16 @password "password"
17)
18as
19begin
20
21 -- Do not count affected rows for performance
22 SET NOCOUNT ON;
23
24 --control_proc_id: task_import_active_directory_grp
25 --template_id: task_import_active_directory_grp
26 --prog_object_item_id: sf_gui_task_import_active_directory_group
27 --template_description: Imports all users from a Active Direcory group in a usr_grp
28
29 declare @query nvarchar(4000)
30 declare @path varchar(1000)
31 declare @error_msg as varchar(1000)
32
33 begin tran
34 begin try
35
36 -- Delete the existing user from the group.
37 delete from usr_grp_usr
38 where tenant_id = @tenant_id
39 and usr_grp_id = @usr_grp_id
40
41 -- Determine the LDAP path for the group
42 set @query =
43 'select @ADsPath = ADsPath
44 from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
45 SELECT ADsPath
46 FROM ''''LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + '''''
47 WHERE name =''''' + @active_directory_grp_name + '''''
48 '') a'
49
50 exec sp_executesql @query, N'@ADsPath varchar(1000) output', @ADsPath= @path output
51
52 if @path is null
53 begin
54 exec tsf_send_message 'active_directory_grp_not_found', null, 1
55 rollback tran
56 return
57 end
58
59 set @path = substring(@path, len('LDAP://') + 1, len(@path))
60
61 -- Give a different message for the root admin as for another admin
62 -- So we don't give up information about users of other tenants
63 if exists( select 1
64 from usr_root_admin
65 where usr_id = dbo.tsf_user()
66 )
67 begin
68 set @error_msg = 'tenant_mismatch_for_root_admin'
69 end
70 else
71 begin
72 set @error_msg = 'tenant_mismatch_for_non_root_admin'
73 end
74
75 -- Check if the user does not exist for another tenant
76 --set @query =
77 --'if exists (select 1
78 --from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
79 -- SELECT
80 -- sAMAccountName
81 -- FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + '''''
82 -- WHERE objectCategory=''''Person''''
83 -- AND objectCategory=''''user''''
84 -- AND memberof =''''' + @path + '''''
85 -- AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
86 --'') a
87 --join usr_general u
88 -- on u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default
89 --where u.tenant_id <> ' + cast(@tenant_id as varchar(10)) + ')
90 --begin
91 -- exec tsf_send_message ''' + @error_msg + ''', null, 0
92 --end
93 --'
94 set @query =
95 'if exists (select 1
96 from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
97 SELECT
98 sAMAccountName
99 FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + '''''
100 WHERE objectCategory=''''Person''''
101 AND objectCategory=''''user''''
102 AND ''''memberof:1.2.840.113556.1.4.1941:''''=''''' + @path + '''''
103 AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
104 '') a
105 join usr_general u
106 on u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default
107 where u.tenant_id <> ' + cast(@tenant_id as varchar(10)) + ')
108 begin
109 exec tsf_send_message ''' + @error_msg + ''', null, 0
110 end
111 '
112
113 exec (@query)
114
115
116 -- Update users if they exist
117 --set @query =
118 --'update u
119 -- set first_name = a.givenName,
120 -- sur_name = a.sn,
121 -- email = a.mail,
122 -- update_user = dbo.tsf_user(),
123 -- update_date_time = sysdatetime()
124 --from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
125 -- SELECT name
126 -- ,sn
127 -- ,sAMAccountName
128 -- ,givenName, mail
129 -- FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + '''''
130 -- WHERE objectCategory=''''Person''''
131 -- AND objectCategory=''''user''''
132 -- AND memberof =''''' + @path + '''''
133 -- AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
134 --'') a
135 --join usr_general u
136 -- on u.tenant_id = ' + cast(@tenant_id as varchar(10)) + '
137 -- and u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default
138 --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
139 -- 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
140 -- 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)
141 --'
142 set @query =
143 'update u
144 set first_name = a.givenName,
145 sur_name = a.sn,
146 email = a.mail,
147 update_user = dbo.tsf_user(),
148 update_date_time = sysdatetime()
149 from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
150 SELECT name
151 ,sn
152 ,sAMAccountName
153 ,givenName, mail
154 FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + '''''
155 WHERE objectCategory=''''Person''''
156 AND objectCategory=''''user''''
157 AND ''''memberof:1.2.840.113556.1.4.1941:''''=''''' + @path + '''''
158 AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
159 '') a
160 join usr_general u
161 on u.tenant_id = ' + cast(@tenant_id as varchar(10)) + '
162 and u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default
163 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
164 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
165 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)
166 '
167
168 exec (@query)
169
170 -- Determine the users in the group, add the users to IAM if they do not yet exist.
171 --set @query =
172 --'insert into usr
173 --(
174 -- tenant_id,
175 -- usr_id,
176 -- first_name,
177 -- sur_name,
178 -- name,
179 -- gender,
180 -- email,
181 -- time_zone_id,
182 -- appl_lang_id,
183 -- authentication_type,
184 -- two_factor_authentication_type,
185 -- allow_fallback_to_email,
186 -- allow_change_password,
187 -- password_changed_count,
188 -- password_forgotten_count,
189 -- write_back_up_type_id,
190 -- insert_user,
191 -- insert_date_time,
192 -- update_user,
193 -- update_date_time
194 --)
195 --select
196 -- ' + cast(@tenant_id as varchar(10)) + ',
197 -- ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + ' a.sAMAccountName as usr_id,
198 -- a.givenName as first_name,
199 -- a.sn as sur_name,
200 -- a.givenName as name,
201 -- 0, -- Unknown
202 -- a.mail as email,
203 -- ''Etc/UTC'',
204 -- ''ENG'',
205 -- 1, -- Windows
206 -- 0, -- Password
207 -- 0, -- No
208 -- 0, -- No
209 -- 0, -- Changed count
210 -- 0, -- Forgotten count
211 -- 1, -- None
212 -- dbo.tsf_user(),
213 -- sysdatetime(),
214 -- dbo.tsf_user(),
215 -- sysdatetime()
216 --from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
217 -- SELECT name
218 -- ,sn
219 -- ,sAMAccountName
220 -- ,givenName, mail
221 -- FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + '''''
222 -- WHERE objectCategory=''''Person''''
223 -- AND objectCategory=''''user''''
224 -- AND memberof =''''' + @path + '''''
225 -- AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
226 --'') a
227 --where not exists (select 1
228 -- from usr u
229 -- -- do not check for tentant, because the usr id must be unique
230 -- where u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default)'
231
232 set @query =
233 'insert into usr
234 (
235 tenant_id,
236 usr_id,
237 first_name,
238 sur_name,
239 name,
240 gender,
241 email,
242 time_zone_id,
243 appl_lang_id,
244 authentication_type,
245 two_factor_authentication_type,
246 allow_fallback_to_email,
247 allow_change_password,
248 password_changed_count,
249 password_forgotten_count,
250 write_back_up_type_id,
251 insert_user,
252 insert_date_time,
253 update_user,
254 update_date_time
255 )
256 select
257 ' + cast(@tenant_id as varchar(10)) + ',
258 ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + ' a.sAMAccountName as usr_id,
259 a.givenName as first_name,
260 a.sn as sur_name,
261 a.givenName as name,
262 0, -- Unknown
263 a.mail as email,
264 ''Etc/UTC'',
265 ''ENG'',
266 1, -- Windows
267 0, -- Password
268 0, -- No
269 0, -- No
270 0, -- Changed count
271 0, -- Forgotten count
272 1, -- None
273 dbo.tsf_user(),
274 sysdatetime(),
275 dbo.tsf_user(),
276 sysdatetime()
277 from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
278 SELECT name
279 ,sn
280 ,sAMAccountName
281 ,givenName, mail
282 FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + '''''
283 WHERE objectCategory=''''Person''''
284 AND objectCategory=''''user''''
285 AND ''''memberof:1.2.840.113556.1.4.1941:''''=''''' + @path + '''''
286 AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
287 '') a
288 where not exists (select 1
289 from usr u
290 -- do not check for tentant, because the usr id must be unique
291 where u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default)'
292
293 exec (@query)
294
295 -- Add the users to the user group.
296 --set @query =
297 --'insert into usr_grp_usr
298 --(
299 -- tenant_id,
300 -- usr_grp_id,
301 -- usr_id,
302 -- insert_user,
303 -- insert_date_time,
304 -- update_user,
305 -- update_date_time
306 --)
307 --select
308 -- ' + cast(@tenant_id as varchar(10)) + ',
309 -- ''' + @usr_grp_id + ''',
310 -- ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'sAMAccountName,
311 -- dbo.tsf_user(),
312 -- sysdatetime(),
313 -- dbo.tsf_user(),
314 -- sysdatetime()
315 --from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
316 -- SELECT name
317 -- ,sn
318 -- ,sAMAccountName
319 -- ,givenName, mail
320 -- FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + '''''
321 -- WHERE objectCategory=''''Person''''
322 -- AND objectCategory=''''user''''
323 -- AND memberof =''''' + @path + '''''
324 -- AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
325 --'') a
326 --where not exists (select 1
327 -- from usr_grp_usr u
328 -- where u.tenant_id = ' + cast(@tenant_id as varchar(10)) + '
329 -- and u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default
330 -- and u.usr_grp_id = ''' + @usr_grp_id + ''')
331 -- -- Only add users from the same tenant as the user group
332 -- and exists (select 1
333 -- from usr u
334 -- where u.tenant_id = ' + cast(@tenant_id as varchar(10)) + '
335 -- and u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default)'
336
337 set @query =
338 'insert into usr_grp_usr
339 (
340 tenant_id,
341 usr_grp_id,
342 usr_id,
343 insert_user,
344 insert_date_time,
345 update_user,
346 update_date_time
347 )
348 select
349 ' + cast(@tenant_id as varchar(10)) + ',
350 ''' + @usr_grp_id + ''',
351 ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'sAMAccountName,
352 dbo.tsf_user(),
353 sysdatetime(),
354 dbo.tsf_user(),
355 sysdatetime()
356 from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
357 SELECT name
358 ,sn
359 ,sAMAccountName
360 ,givenName, mail
361 FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + '''''
362 WHERE objectCategory=''''Person''''
363 AND objectCategory=''''user''''
364 AND ''''memberof:1.2.840.113556.1.4.1941:''''=''''' + @path + '''''
365 AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
366 '') a
367 where not exists (select 1
368 from usr_grp_usr u
369 where u.tenant_id = ' + cast(@tenant_id as varchar(10)) + '
370 and u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default
371 and u.usr_grp_id = ''' + @usr_grp_id + ''')
372 -- Only add users from the same tenant as the user group
373 and exists (select 1
374 from usr u
375 where u.tenant_id = ' + cast(@tenant_id as varchar(10)) + '
376 and u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default)'
377
378 exec (@query)
379
380 -- When no errors have occurred, update the user group with the new AD information, if needed.
381 update usr_grp
382 set usr_grp_type = 1,
383 active_directory_domain_name = @active_directory_domain_name,
384 active_directory_grp_name = @active_directory_grp_name,
385 net_bios_domain_name = @net_bios_domain_name,
386 active_directory_updated_on = sysdatetime(),
387 update_user = dbo.tsf_user(),
388 update_date_time = sysdatetime()
389 where tenant_id = @tenant_id
390 and usr_grp_id = @usr_grp_id
391
392 end try
393 begin catch
394
395 if error_number() = 7321
396 begin
397 exec tsf_send_message 'active_directory_access_denied', null, 1;
398 end
399 else
400 throw;
401
402 if @@trancount > 0
403 rollback transaction;
404
405 end catch;
406
407 if @@trancount > 0
408 commit transaction;
409
410end

 

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. ðŸ˜„

View original
Did this topic help you find an answer to your question?
This topic has been closed for replies.

2 replies

Mark Jongeling
Administrator
Forum|alt.badge.img+23
  • Administrator
  • 4037 replies
  • Answer
  • September 28, 2022

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):

Show content
1USE [YOUR_IAM_DATABASE]
2GO
3/****** Object: StoredProcedure [dbo].[task_import_active_directory_grp] Script Date: 27-9-2022 15:45:37 ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8ALTER procedure [dbo].[task_import_active_directory_grp]
9(
10 @tenant_id "tenant_id" ,
11 @usr_grp_id "usr_grp_id" ,
12 @active_directory_grp_name "active_directory_grp_name" ,
13 @active_directory_domain_name "active_directory_domain_name" ,
14 @net_bios_domain_name "net_bios_domain_name" ,
15 @user_name "user_name" ,
16 @password "password"
17)
18as
19begin
20
21 -- Do not count affected rows for performance
22 SET NOCOUNT ON;
23
24 --control_proc_id: task_import_active_directory_grp
25 --template_id: task_import_active_directory_grp
26 --prog_object_item_id: sf_gui_task_import_active_directory_group
27 --template_description: Imports all users from a Active Direcory group in a usr_grp
28
29 declare @query nvarchar(4000)
30 declare @path varchar(1000)
31 declare @error_msg as varchar(1000)
32
33 begin tran
34 begin try
35
36 -- Delete the existing user from the group.
37 delete from usr_grp_usr
38 where tenant_id = @tenant_id
39 and usr_grp_id = @usr_grp_id
40
41 -- Determine the LDAP path for the group
42 set @query =
43 'select @ADsPath = ADsPath
44 from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
45 SELECT ADsPath
46 FROM ''''LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + '''''
47 WHERE name =''''' + @active_directory_grp_name + '''''
48 '') a'
49
50 exec sp_executesql @query, N'@ADsPath varchar(1000) output', @ADsPath= @path output
51
52 if @path is null
53 begin
54 exec tsf_send_message 'active_directory_grp_not_found', null, 1
55 rollback tran
56 return
57 end
58
59 set @path = substring(@path, len('LDAP://') + 1, len(@path))
60
61 -- Give a different message for the root admin as for another admin
62 -- So we don't give up information about users of other tenants
63 if exists( select 1
64 from usr_root_admin
65 where usr_id = dbo.tsf_user()
66 )
67 begin
68 set @error_msg = 'tenant_mismatch_for_root_admin'
69 end
70 else
71 begin
72 set @error_msg = 'tenant_mismatch_for_non_root_admin'
73 end
74
75 -- Check if the user does not exist for another tenant
76 --set @query =
77 --'if exists (select 1
78 --from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
79 -- SELECT
80 -- sAMAccountName
81 -- FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + '''''
82 -- WHERE objectCategory=''''Person''''
83 -- AND objectCategory=''''user''''
84 -- AND memberof =''''' + @path + '''''
85 -- AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
86 --'') a
87 --join usr_general u
88 -- on u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default
89 --where u.tenant_id <> ' + cast(@tenant_id as varchar(10)) + ')
90 --begin
91 -- exec tsf_send_message ''' + @error_msg + ''', null, 0
92 --end
93 --'
94 set @query =
95 'if exists (select 1
96 from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
97 SELECT
98 sAMAccountName
99 FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + '''''
100 WHERE objectCategory=''''Person''''
101 AND objectCategory=''''user''''
102 AND ''''memberof:1.2.840.113556.1.4.1941:''''=''''' + @path + '''''
103 AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
104 '') a
105 join usr_general u
106 on u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default
107 where u.tenant_id <> ' + cast(@tenant_id as varchar(10)) + ')
108 begin
109 exec tsf_send_message ''' + @error_msg + ''', null, 0
110 end
111 '
112
113 exec (@query)
114
115
116 -- Update users if they exist
117 --set @query =
118 --'update u
119 -- set first_name = a.givenName,
120 -- sur_name = a.sn,
121 -- email = a.mail,
122 -- update_user = dbo.tsf_user(),
123 -- update_date_time = sysdatetime()
124 --from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
125 -- SELECT name
126 -- ,sn
127 -- ,sAMAccountName
128 -- ,givenName, mail
129 -- FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + '''''
130 -- WHERE objectCategory=''''Person''''
131 -- AND objectCategory=''''user''''
132 -- AND memberof =''''' + @path + '''''
133 -- AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
134 --'') a
135 --join usr_general u
136 -- on u.tenant_id = ' + cast(@tenant_id as varchar(10)) + '
137 -- and u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default
138 --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
139 -- 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
140 -- 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)
141 --'
142 set @query =
143 'update u
144 set first_name = a.givenName,
145 sur_name = a.sn,
146 email = a.mail,
147 update_user = dbo.tsf_user(),
148 update_date_time = sysdatetime()
149 from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
150 SELECT name
151 ,sn
152 ,sAMAccountName
153 ,givenName, mail
154 FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + '''''
155 WHERE objectCategory=''''Person''''
156 AND objectCategory=''''user''''
157 AND ''''memberof:1.2.840.113556.1.4.1941:''''=''''' + @path + '''''
158 AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
159 '') a
160 join usr_general u
161 on u.tenant_id = ' + cast(@tenant_id as varchar(10)) + '
162 and u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default
163 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
164 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
165 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)
166 '
167
168 exec (@query)
169
170 -- Determine the users in the group, add the users to IAM if they do not yet exist.
171 --set @query =
172 --'insert into usr
173 --(
174 -- tenant_id,
175 -- usr_id,
176 -- first_name,
177 -- sur_name,
178 -- name,
179 -- gender,
180 -- email,
181 -- time_zone_id,
182 -- appl_lang_id,
183 -- authentication_type,
184 -- two_factor_authentication_type,
185 -- allow_fallback_to_email,
186 -- allow_change_password,
187 -- password_changed_count,
188 -- password_forgotten_count,
189 -- write_back_up_type_id,
190 -- insert_user,
191 -- insert_date_time,
192 -- update_user,
193 -- update_date_time
194 --)
195 --select
196 -- ' + cast(@tenant_id as varchar(10)) + ',
197 -- ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + ' a.sAMAccountName as usr_id,
198 -- a.givenName as first_name,
199 -- a.sn as sur_name,
200 -- a.givenName as name,
201 -- 0, -- Unknown
202 -- a.mail as email,
203 -- ''Etc/UTC'',
204 -- ''ENG'',
205 -- 1, -- Windows
206 -- 0, -- Password
207 -- 0, -- No
208 -- 0, -- No
209 -- 0, -- Changed count
210 -- 0, -- Forgotten count
211 -- 1, -- None
212 -- dbo.tsf_user(),
213 -- sysdatetime(),
214 -- dbo.tsf_user(),
215 -- sysdatetime()
216 --from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
217 -- SELECT name
218 -- ,sn
219 -- ,sAMAccountName
220 -- ,givenName, mail
221 -- FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + '''''
222 -- WHERE objectCategory=''''Person''''
223 -- AND objectCategory=''''user''''
224 -- AND memberof =''''' + @path + '''''
225 -- AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
226 --'') a
227 --where not exists (select 1
228 -- from usr u
229 -- -- do not check for tentant, because the usr id must be unique
230 -- where u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default)'
231
232 set @query =
233 'insert into usr
234 (
235 tenant_id,
236 usr_id,
237 first_name,
238 sur_name,
239 name,
240 gender,
241 email,
242 time_zone_id,
243 appl_lang_id,
244 authentication_type,
245 two_factor_authentication_type,
246 allow_fallback_to_email,
247 allow_change_password,
248 password_changed_count,
249 password_forgotten_count,
250 write_back_up_type_id,
251 insert_user,
252 insert_date_time,
253 update_user,
254 update_date_time
255 )
256 select
257 ' + cast(@tenant_id as varchar(10)) + ',
258 ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + ' a.sAMAccountName as usr_id,
259 a.givenName as first_name,
260 a.sn as sur_name,
261 a.givenName as name,
262 0, -- Unknown
263 a.mail as email,
264 ''Etc/UTC'',
265 ''ENG'',
266 1, -- Windows
267 0, -- Password
268 0, -- No
269 0, -- No
270 0, -- Changed count
271 0, -- Forgotten count
272 1, -- None
273 dbo.tsf_user(),
274 sysdatetime(),
275 dbo.tsf_user(),
276 sysdatetime()
277 from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
278 SELECT name
279 ,sn
280 ,sAMAccountName
281 ,givenName, mail
282 FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + '''''
283 WHERE objectCategory=''''Person''''
284 AND objectCategory=''''user''''
285 AND ''''memberof:1.2.840.113556.1.4.1941:''''=''''' + @path + '''''
286 AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
287 '') a
288 where not exists (select 1
289 from usr u
290 -- do not check for tentant, because the usr id must be unique
291 where u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default)'
292
293 exec (@query)
294
295 -- Add the users to the user group.
296 --set @query =
297 --'insert into usr_grp_usr
298 --(
299 -- tenant_id,
300 -- usr_grp_id,
301 -- usr_id,
302 -- insert_user,
303 -- insert_date_time,
304 -- update_user,
305 -- update_date_time
306 --)
307 --select
308 -- ' + cast(@tenant_id as varchar(10)) + ',
309 -- ''' + @usr_grp_id + ''',
310 -- ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'sAMAccountName,
311 -- dbo.tsf_user(),
312 -- sysdatetime(),
313 -- dbo.tsf_user(),
314 -- sysdatetime()
315 --from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
316 -- SELECT name
317 -- ,sn
318 -- ,sAMAccountName
319 -- ,givenName, mail
320 -- FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + '''''
321 -- WHERE objectCategory=''''Person''''
322 -- AND objectCategory=''''user''''
323 -- AND memberof =''''' + @path + '''''
324 -- AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
325 --'') a
326 --where not exists (select 1
327 -- from usr_grp_usr u
328 -- where u.tenant_id = ' + cast(@tenant_id as varchar(10)) + '
329 -- and u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default
330 -- and u.usr_grp_id = ''' + @usr_grp_id + ''')
331 -- -- Only add users from the same tenant as the user group
332 -- and exists (select 1
333 -- from usr u
334 -- where u.tenant_id = ' + cast(@tenant_id as varchar(10)) + '
335 -- and u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default)'
336
337 set @query =
338 'insert into usr_grp_usr
339 (
340 tenant_id,
341 usr_grp_id,
342 usr_id,
343 insert_user,
344 insert_date_time,
345 update_user,
346 update_date_time
347 )
348 select
349 ' + cast(@tenant_id as varchar(10)) + ',
350 ''' + @usr_grp_id + ''',
351 ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'sAMAccountName,
352 dbo.tsf_user(),
353 sysdatetime(),
354 dbo.tsf_user(),
355 sysdatetime()
356 from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
357 SELECT name
358 ,sn
359 ,sAMAccountName
360 ,givenName, mail
361 FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + '''''
362 WHERE objectCategory=''''Person''''
363 AND objectCategory=''''user''''
364 AND ''''memberof:1.2.840.113556.1.4.1941:''''=''''' + @path + '''''
365 AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
366 '') a
367 where not exists (select 1
368 from usr_grp_usr u
369 where u.tenant_id = ' + cast(@tenant_id as varchar(10)) + '
370 and u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default
371 and u.usr_grp_id = ''' + @usr_grp_id + ''')
372 -- Only add users from the same tenant as the user group
373 and exists (select 1
374 from usr u
375 where u.tenant_id = ' + cast(@tenant_id as varchar(10)) + '
376 and u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default)'
377
378 exec (@query)
379
380 -- When no errors have occurred, update the user group with the new AD information, if needed.
381 update usr_grp
382 set usr_grp_type = 1,
383 active_directory_domain_name = @active_directory_domain_name,
384 active_directory_grp_name = @active_directory_grp_name,
385 net_bios_domain_name = @net_bios_domain_name,
386 active_directory_updated_on = sysdatetime(),
387 update_user = dbo.tsf_user(),
388 update_date_time = sysdatetime()
389 where tenant_id = @tenant_id
390 and usr_grp_id = @usr_grp_id
391
392 end try
393 begin catch
394
395 if error_number() = 7321
396 begin
397 exec tsf_send_message 'active_directory_access_denied', null, 1;
398 end
399 else
400 throw;
401
402 if @@trancount > 0
403 rollback transaction;
404
405 end catch;
406
407 if @@trancount > 0
408 commit transaction;
409
410end

 

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. ðŸ˜„


Mark Jongeling
Administrator
Forum|alt.badge.img+23

Hi,

Has this been resolved with the edited Stored procedure or do you require further assistance?


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings