Intermittent direct query failures with oracle sso connections

[ramapr1]

We've enabled VPD in our oracle database, ver 18 and it is Exadata. We have built power bi direct query models using this db. Have configured kerberos on the gateway machine, and enabled sso on the gateway connection.

We are experiencing intermittent failures when trying to run these direct query models. When the report fails, an oracle error - “ORA-12631: Username retrieval failed” is being captured in the gateway logs, and a session does not get created in the db.

So far we have verified possible culprits such as firewall blocks, network drops and servers time sync, and they have all been eliminated as the root cause of the issue. The sso consistently works from PBI desktop and sqlplus.

Is anyone in the community been successfully using DQ-SSO combination for oracle ? Have you experienced ORA-12631 errors ?

[v-deddai1-msft]

Hi @ramapr1 ,

Would you please check if you have set spn correctly and set up delegate account correctly? Please follow the steps in

https://docs.microsoft.com/zh-cn/power-bi/connect-data/service-gateway-sso-kerberos

And would you please  change SQLNET.AUTHENTICATION_SERVICES= (NTS) parameter to SQLNET.AUTHENTICATION_SERVICES = (ALL) to enable kerberos5 for Kerberos authentication you can follow the  https://ittutorial.org/ora-12631-username-retrieval-failed-on-windows-login-with-sqlplus/

For more details, please refer to http://dba-oracle.com/bk_sqlnet_authentication_services.htm

Best Regards,

Dedmon Dai

[ramapr1]

Thank you for looking into my issue and sharing those recommendations.

In my sqlnet.ora file, I've the auth configured as - 

SQLNET.AUTHENTICATION_SERVICES=(KERBEROS5PRE, KERBEROS5)

If I change the value either to (ALL) or (none) or keep one/remove other, the sso fails consistently - from pbi desktop, sqlplus and pbi service. It is my understanding that, both KERBEROS5PRE, KERBEROS5 should exist in the sqlnet file for the sso to kick-in.

[v-deddai1-msft]

Hi @ramapr1 ，

So is the problem solved?

Best Regards,

Dedmon Dai

[ramapr1]

No, the intermittent issue with sso still exists.

what I meant in my previous reply is that changing sql auth parameters to (ALL) in the sqlnet file did not help.

[v-deddai1-msft]

Hi @ramapr1 ,

I really can't reproduce your issue. If you have Pro account I suggest you  try to open a support ticket. If you have a Pro account it is free. Go to https://support.powerbi.com. Scroll down and click "CREATE SUPPORT TICKET".

Best Regards,

Dedmon Dai