How to pass actual SAS User EG Login account to Impala DB for odbc.ini DSN connection

[chenchug]

Hi All,

I have set up SAS 9.4 M7 and SAS Access to Impala connection. Impala DBA shared Service accounts and added all SAS user log in accounts into Impala DB AD groups to have read access. I have done connection set up configuration part and able to access Impala data using metadata Library/libname statement/pass through code using Impala DB service account.

Impala DBA can see the SAS code gets executing from using Impala DB service account.

We wanted to capture actual SAS user Login ID in Impala whoever connects EG and run query against to Impala DB.

Example: if user 'SAS121' connect to EG and run query against to Impala DB, then DBA should see the Impala service account and 'SAS121' details.

for this, we have done following:

- Enabled the Impala Service account Delegation permissions capability.

- Impala DBA updated the configuration in Impala DB to capture service account and actual user account.

- Impala DBA asked me to add 'DelegationUID=' to Impala DSN settings in odbc.ini file . 

- When I hardcode value for "DelegationUID=Lion" then Impala DBA can see 'Lion' ID, or  add 'DelegationUID=test' then Impala DBA can see Impala Service account and 'test'.

- Wanted to add parameter to capture actual SAS Login account details along with Impala Service account.

- Have to add "DelegationUID= <value>" to pass the user login ID to Impala DB.

- Tried with "DelegationUID=&sysuserid", but getting error as "&sysuserid not authorized to access Impala DB".

what would be the right variable to capture the SAS EG session logged on user account to pass to Impala DB?

has anyone worked on and got the solution for this? please share your recommendation or suggestions?

Thanks

[SASKiwi]

&sysuserid - contains the userid your SAS server session runs under. This is normally the same as the userid EG connects with which you can check using &_CLIENTUSERID - note this is only available in EG initiated SAS server sessions.

[chenchug]

Thank you @SASKiwi  for your reply.

I have added "DelegationUID=&_CLIENTUSERID" in odbc.ini and got below error.

[Error] CLI error trying to establish connection: [Cloudera][ImpalaODBC] (110) Error while executing a query in Impala: [HY000] : AuthorizationException: User '&_CLIENTUSERID' does not have privileges to access: client_test_db.*.*

We were pointing to Oracle earlier and Oracle DBA used to capture Service account and SAS User Logon Account in Oracle '$VSession' table.

Is there a we can capture SAS logon user ID while connecting to Impala too?

Thank you.

[Sajid01]

Hello @chenchug 
SAS macro &sysuserid holds the userid of the user.
If you want to capture this in the in any external databases (here you are using Impala, but it could be anything else), then create a table in the database to hold the userid.
Insert/update the table with userid, time and other details as needed.
Please note that every software has its own design what is possible in one application may not be possible in the other using the same procedures.

[chenchug]

Thank you for your reply @Sajid01.

how can we capture and insert into DB tables when we dont pass the SAS actual logon ID along with SAS query to Impala DB?

can you please share more details? as i said we can capture actual UserID details for 'OSUSER'(is SAS EG logon ID) field in $VSession in oracle so we can capture and insert. similarly how can we pass capture while querying Impala DB.

Thank you.

[Sajid01]

Oracle and impala are different Applications and I am not aware if it can be done in impala in the same way.

However a work around is possible as I have given in my earlier post.

[SASKiwi]

@chenchug  - I suggest you open a track with SAS Tech Support on this. They should be able to confirm what authentication methods are supported on Impala. FYI we use a similar mechanism accessing SQL Server using Windows Authentication and that works fine too.

[chenchug]

 Hi All,

thank you for all your replies.

Hi @SASKiwi ....I did open SAS Track. working on it. So far no luck.