Skip to main content

Hi,

since some months our SQL Job which will sync the AD groups to IAM runs into an error after a certain time.

SQL Job:

DECLARE
    @usr_grp_id AS varchar(100)
	,@active_directory_grp_name AS varchar(100)
    ,@active_directory_domain_name AS varchar(1000)
    ,@net_bios_name AS varchar(15)
    ,@user_name AS varchar(100)
    ,@password AS nvarchar(100)

SET @user_name = '{DOMAIN SERVICE ACCOUNT}'
SET @password = '{DOMAIN SERVICE ACCOUNT PASSWORD}'

DECLARE sync_ad_groups CURSOR FAST_FORWARD
FOR
SELECT usr_grp_id
	,active_directory_grp_name
    ,active_directory_domain_name
    ,net_bios_domain_name
FROM usr_grp
WHERE 1 = 1
	AND usr_grp_type = 1
	AND active_directory_grp_name IS NOT NULL

OPEN sync_ad_groups

FETCH NEXT
FROM sync_ad_groups
INTO @usr_grp_id
	,@active_directory_grp_name
    ,@active_directory_domain_name
    ,@net_bios_name

WHILE @@fetch_status <> - 1
BEGIN
	IF @@fetch_status <> - 2
	BEGIN
		EXEC task_import_active_directory_grp 
             @tenant_id = 1
            ,@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
        ,@active_directory_domain_name
        ,@net_bios_name
END

CLOSE sync_ad_groups

DEALLOCATE sync_ad_groups

SQL Job Error:

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

I also added a line in the job to retrieve the dbo.tsf_user(), and this is equal to the {DOMAIN SERVICE ACCOUNT} value.

Also there is only 1 (default) tenant in our IAM and the {DOMAIN SERVICE ACCOUNT} user is added as a “User Adminisitrator”.

When running the follwing command I also get 1 as a result:

select dbo.is_authorized_usr_admin(1, '{DOMAIN SERVICE ACCOUNT}')

 

Now comes the strangest thing:

When I run the SQL Job code manually via an SSMS Query (so not executing the job, but running the code from a Query window), the result is ok.

When afterwards executing the job again (which before ran into the error), it executed succesfully again! (for some days/weeks).

So...I'm a little bit confused what's happening in here….maybe somewhere related to the ‘quite new’ tenant structure ?

The {DOMAIN SERVICE ACCOUNT} is used for accessing the AD and is probably a different user than the database user executing the job. The database user executing can be found by logging dbo.tsf_user().

The database user executing the job must be authorized for user administration in IAM. The information posted here will probably be of help:

 

Hi Anne, 

thanks for your reply.

As mentioned in my post, I already checked te dbo.tsf_user() by logging it during AD sync execution.
From that info I could see that the {DOMAIN SERVICE ACCOUNT} = dbo.tsf_user().
And this user was User Administrator in IAM.


And why is the job executing oke for ‘some weeks’, and then ‘out of a sudden’ providing the error as mentioned ?

Thanks.

best regards,

Cyril

 

Hi Cyril,

The only reason I can see is that somehow the CONTEXT_INFO is overridden somewhere during job execution. This would cause dbo.tsf_user() to return a different value than system_user.

Can you add the following code to the script:

-- Force context_info() to match system_user
declare @context_info varbinary(128) = cast(system_user as varbinary(128));
set context_info @context_info;

Azure databases are known to mess with CONTEXT_INFO but I would'nt know why this would suddenly happen for your database.

hi Anne,

will add this piece of code when the issue occurs again...so can take up to a few weeks….
Will update this post then.

Regarding SQL: we are using an on-prem SQL server, so no Azure related issues 😉

Terms & ConditionsCookie settings