Skip to main content
Solved

IAM Windows Domain Group inheritance

  • September 16, 2022
  • 2 replies
  • 55 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
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. ðŸ˜„

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

2 replies

Mark Jongeling
Administrator
Forum|alt.badge.img+23
  • Administrator
  • 3936 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
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. ðŸ˜„


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