Hi there,
We are trying to configure an Oracle datasource with some particularities in the authentication. We want that the user that is logged in the windows machine uses its credentials through Kerberos to impersonate as a user proxy.
Our environment is Windows 10 and we are using Power BI Desktop Reporting Services.
The connection is successfully created when using sqlplus
sqlplus [userproxy]/(forward slash to use current user)@TDWX(database)
And it also works when configuring a Windows ODBC and then using it as an ODBC datasource in PowerBI
When configuring a WINDOWS ODBC of type Oracle and entering TDWX as TNS Service Name and [WTDWXY1]/ as User Id, Test connection results in "Connection successful"
Then when using this ODBC as Datasource in PowerBI is necessary to enter in Database credentails only the user name: [WTDWXY1]/ and the connection is established successfully and the schemas and tables can be selected.
However when trying to do the same but using the Oracle Client, we are getting an error as it seems the configuration is not correctly sent to the underlying Oracle Client.
We got the following error after filling under Database the User name as [WTDWXY1]/ and click on “Connect”:
No se puede conectar. Se encontró un error al intentar conectarse. Detalles "Oracle ORA-897: faltan los nombres de usuario o no son válidos".
We have look a bit deeper with our database team and the connection is not reaching the database so the error seems to be how Power BI sends this configuration to the underlying Oracle client. Look into power BI traces the following information is captured regarding to this error:
DataMashup.Trace Error: 24579 : {"Start":"2019-07-24T06:56:07.4542946Z","Action":"Engine/IO/Db/Oracle/Connection/Open","HostProcessId":"13276","ConnectionId":"4aa7a8f3-5140-41da-bb7e-039e60611995","Exception":"Exception:\r\nExceptionType: Oracle.DataAccess.Client.OracleException, Oracle.DataAccess, Version=4.112.3.0, Culture=neutral, PublicKeyToken=89b483f429c47342\r\nMessage: ORA-987: faltan los nombres de usuario o no son v\u00E1lidos\r\nStackTrace:\n at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)\r\n at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, Object src)\r\n at Oracle.DataAccess.Client.OracleConnection.Open()\r\n at Microsoft.Mashup.Engine1.Library.Common.TracingDbConnection.<Open>b__0(IHostTrace trace)\r\n at Microsoft.Mashup.Engine1.Library.Common.Tracer.<>c__DisplayClass4.<TracePerformance>b__3(IHostTrace trace)\r\n at Microsoft.Mashup.Engine1.Library.Common.Tracer.TracePerformance[T](String method, Func`2 func)\r\n\r\n\r\n","ProductVersion":"2.65.5313.1562 (18.12)","ActivityId":"211a9ba6-3dd6-4bee-9723-05238563cd98","Process":"Microsoft.Mashup.Container.NetFX45","Pid":22376,"Tid":1,"Duration":"00:00:00.2482060"}
DataMashup.Trace Information: 24579 : {"Start":"2019-07-24T06:56:07.7025506Z","Action":"BackgroundThread/RollingTraceWriter/Flush","ProductVersion":"2.65.5313.1562 (18.12)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"Microsoft.Mashup.Container.NetFX45","Pid":22376,"Tid":10,"Duration":"00:00:00.0000410"}
DataMashup.Trace Information: 24579 : {"Start":"2019-07-24T06:56:07.7025933Z","Action":"Engine/IO/Db/Oracle/AuthorizationError","HostProcessId":"13276","Exception":"Oracle.DataAccess.Client.OracleException ORA-987: faltan los nombres de usuario o no son v\u00E1lidos at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)\r\n at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, Object src)\r\n at Oracle.DataAccess.Client.OracleConnection.Open()\r\n at Microsoft.Mashup.Engine1.Library.Common.TracingDbConnection.<Open>b__0(IHostTrace trace)\r\n at Microsoft.Mashup.Engine1.Library.Common.Tracer.<>c__DisplayClass4.<TracePerformance>b__3(IHostTrace trace)\r\n at Microsoft.Mashup.Engine1.Library.Common.Tracer.TracePerformance[T](String method, Func`2 func)\r\n at Microsoft.Mashup.Engine1.Library.Common.TracingDbConnection.Open()\r\n at Microsoft.Mashup.Engine1.Library.Common.WrappedDbConnection.Open()\r\n at Microsoft.Mashup.Engine1.Library.Common.DbExtensions.<>c__DisplayClass1.<Open>b__0()\r\n at Microsoft.Mashup.Engine1.Library.Common.DbEnvironment.RunWithRetryGuard[T](Func`1 action, Func`2 retryAfterSqlError, Action finalizeOnRetry, String dataSourceNameString, IEngineHost host)\r\n at Microsoft.Mashup.Engine1.Library.Common.DbEnvironment.ConvertDbExceptions[T](IResource resource, Func`1 action, Func`2 retryAfterSqlError, Action finalizeOnRetry, String dataSourceNameString, IEngineHost host)","ExceptionNumber":"987","ResourceExceptionKind":"None","ProductVersion":"2.65.5313.1562 (18.12)","ActivityId":"211a9ba6-3dd6-4bee-9723-05238563cd98","Process":"Microsoft.Mashup.Container.NetFX45","Pid":22376,"Tid":1,"Duration":"00:00:00.0002084"}
DataMashup.Trace Information: 24579 : {"Start":"2019-07-24T06:56:07.7029690Z","Action":"Engine/IO/Db/Oracle/Connection/Dispose","HostProcessId":"13276","ConnectionId":"4aa7a8f3-5140-41da-bb7e-039e60611995","ProductVersion":"2.65.5313.1562 (18.12)","ActivityId":"211a9ba6-3dd6-4bee-9723-05238563cd98","Process":"Microsoft.Mashup.Container.NetFX45","Pid":22376,"Tid":1,"Duration":"00:00:00.0000156"}
DataMashup.Trace Information: 24579 : {"Start":"2019-07-24T06:56:07.7029986Z","Action":"Engine/IO/Db/Oracle/Connection/Dispose","HostProcessId":"13276","ConnectionId":"4aa7a8f3-5140-41da-bb7e-039e60611995","ProductVersion":"2.65.5313.1562 (18.12)","ActivityId":"211a9ba6-3dd6-4bee-9723-05238563cd98","Process":"Microsoft.Mashup.Container.NetFX45","Pid":22376,"Tid":1,"Duration":"00:00:00.0000082"}
DataMashup.Trace Information: 24579 : {"Start":"2019-07-24T06:56:07.7030023Z","Action":"BackgroundThread/RollingTraceWriter/Flush","ProductVersion":"2.65.5313.1562 (18.12)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"Microsoft.Mashup.Container.NetFX45","Pid":22376,"Tid":10,"Duration":"00:00:00.0000180"}
DataMashup.Trace Information: 24579 : {"Start":"2019-07-24T06:56:07.7030211Z","Action":"BackgroundThread/RollingTraceWriter/Flush","ProductVersion":"2.65.5313.1562 (18.12)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"Microsoft.Mashup.Container.NetFX45","Pid":22376,"Tid":11,"Duration":"00:00:00.0000312"}
I hope someone has found similar problem and can help out as we prefer to use native driver to odbc approach.
Thank you very much.
