Enabling Local Authentication with Enhanced Query Execution

[BarCar]

We are trying to migrate our configuration to production and our new instance no longer supports the deprecated Custom Query job.

So, I'm stuck trying to use the new "Enhanced Query Execution" to set a service account for local authentication.

I've tried to follow the documentation per Guidelines for Constructing SQL Queries (saviyntcloud.com) but I am getting nowhere. The query below appears to match the required syntax for updates but returns an error.

SELECT 1 as LOCALAUTHENABLED, u.USERKEY as USERS__PRIMARYKEY from USERS u where u.USERNAME = 'myserviceacct'

The error returned is the helpful "Provided table and column name not in format".

Any ideas what I am missing?

[armaanzahir · Accepted solution]

Hi @BarCar ,

I do not believe the enhanced query execution job supports updates on the users table as is mentioned in the documentation.

Job Categories for Flat Job Control Panel (saviyntcloud.com)

Better to raise an FD for this.

Thanks,

Armaan

[BarCar]

Sigh. I missed that. Thanks.

[BarCar · Accepted solution]

For anybody else struggling to set LocalAuthEnabled for a user on v23.7 or later - here's the "easy" solution our professional services team came up with:

1. Go to Global Configuration -> Identity Lifecycle -> Register User form -> Action -> Create
2. Create a dynamic attribute as follows:
   1. Name: LocalAuthEnabled
   2. Request Type: User
   3. Label: Set LocalAuthEnabled
   4. Attribute Type: Boolean
   5. User Column: localAuthEnabled
   6. Select Editable on Update and Hide on Create
   7. Action: Mapping
3. Go to User Modification Auto Approve and check that box.
4. Go to Home -> Update User Request -> Search the user and you will see the form being opened where you will have the Localauthenabled attribute that you created
5. Select true and submit. 
6. Repeat for other users as needed
7. Go back to User Modification Auto Approve and uncheck that box.

All of this is just because the property is not exposed on the user record. Sigh.