Skip to main content

Hello there,

I have created a subroutine/stored procedure to have the AD groups automatically synchronized using an SQL Agent job. Initially this job starts up fine (every evening at 20:00), but after some time the job fails with the message below:

Executed as user: {service account}. <msg id="not_authorized_usr_admin"></msg> SQLSTATE 42000] (Error 50000).  The step failed.

If I then manually kick off the stored procedure, the job will function correctly again for some time.

Below the stored procedure which is used:

declare sync_ad_groups cursor fast_forward for

    		select 
    			usr_grp_id, active_directory_grp_name
    		from iam.dbo.usr_grp
    		where 1 = 1
    		and usr_grp_type = 1

    		open sync_ad_groups

    		fetch next from sync_ad_groups into @usr_grp_id, @active_directory_grp_name

    		while @@fetch_status <> -1
    		begin

    		   if @@fetch_status <> -2
    		   begin

    				exec iam.dbo.task_import_active_directory_grp
    					 @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_name					,
    					 @user_name							= @user_name						,
    					 @password							= @password

    		   end

    		   fetch next from sync_ad_groups into @usr_grp_id, @active_directory_grp_name

    		end

    		close sync_ad_groups
    		deallocate sync_ad_groups

What could be reason of this exception?

Thanks in advance.

Randolph

Hello Randolph,

The import AD-group task executes certain steps for which you must have the appropriate rights in IAM.

This means that your self-written procedure must be scheduled under an account with those rights. We expect that the reason the procedure does not fail every time is because running it will not always lead to an insert/update action, only when something has changed in the AD. But those times it does, the rights notification will occur.

Can you check if running this procedure under an account with more rights in IAM fixes the problem?

Hello Jeroen,

Thanks for the reply. If I use the same user (service account) for the AD sync task in IAM I will not receive the exception and new users are added to the User groups. The SP which is scheduled uses also these credentials. If I manually start the job the job runs fine even if new users needed to be insert into the group. 

 

SP with service account credentials:

Sales group which is empty
Empty
Active Directory Sync Task with same account as SP
Accounts are added

 So it looks like the service account is having sufficients right. Is this assumption correct?

Thanks in advance,
Randolph

Hello Randolph,

Looks like you are speaking of the service account for approaching the AD inside the task.

I was talking about the database account under which the procedure is scheduled. You can check this by going to SQL Server Management Studio, clicking ‘SQL Server Agent’, ‘Jobs’ and then right click the job and click ‘Properties’. What is the owner of the job? That is the account that should have sufficient rights.

Hope this helps.

Jeroen

Hello Jeroen,

Thanks for the reply, I changed the owner. I emptied a user group in IAM and had the job imported according to schedule and that worked. Thank you very much.

Regards,
Randolph

 

Hello Jeroen,

Somehow, my reply above wasn't quite the true after all. It turns out that SQL job failes when new users are to be added. Today I put some time into it and found that the update trigger "usr_tu” on the table IAM.dbo.usr was generating the exception. If I set the trigger to disabled then the SQL job was succesfull. If I enable it it will fail again and the SQL job will not be able to add new users to IAM. Next I commented out the following part in the function "is_authorized_usr_admin”:

  else if not exists(            select 1            from usr_manager a            join usr c              on c.usr_id = a.usr_id            where a.usr_id = dbo.tsf_user()              and (a.begin_on is null or a.begin_on <= getdate()) -- start of authorization period              and (a.end_on   is null or a.end_on   >= getdate()) -- end of authorization period                 and (c.begin_on is null or c.begin_on <= getdate())              and (c.end_on   is null or c.end_on   >= getdate())        )    begin        return 0    end

and then the SQL job is succesfull. By the way, the service account is "User manager" in IAM. However when I activate it again it fails again. Now I added the following in the SP which is executed in the SQL job:

select @service_account = key_value from application_settings where key_description = 'thinkwise_service_account';declare @binvar varbinary(128);            set @binvar = cast(@service_account as varbinary(128));            set context_info @binvar;

Where @service_account is a variable in which the service_account is stored

Now the SQL job is doing fine, but is this desirable?

Thanks in advance,

Randolph

(I selected SQL as language, but somehow it gets messy)

 

 By the way, the service account is "User manager" in IAM. 

 

The part of the function you've disabled in the is_authorized_usr_admin checks for this specifically. The job must not be running under the service account representing the user manager.

Can you verify what the function dbo.tsf_user() returns when you run the SQL job by writing a record with this value to a log table in the IAM database? This should be the service account, having to store and retrieve this information from the application_settings in the other database should not be needed.

Reply


Terms & ConditionsCookie settings