Focal Point
[CASE-OPENED] Pass windows login id to oracle through webfocus data adapter

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/886108802

August 13, 2009, 10:37 PM
Kamesh
[CASE-OPENED] Pass windows login id to oracle through webfocus data adapter
Hi,

I want to pass my active directory windows login id to oracle database through data adapter.

1) Created data adapter called testCon as a trusted connection.
2) code in fex says
ENGINE SQLORA SET CONNECTION_ATTRIBUTES testCon/,
SQL SQLORA

select user from dual;

TABLE FILE SQLOUT
PRINT *
END
-EXIT

When I ran my report I am not getting my network id instead it's giving me some default userid used to connect to oracle from webfocus.

Please let me know the steps to achieve this.

Note: I am doing login authentication through .NET and once it authenticated then it will call the webfocus report.

Thanks

This message has been edited. Last edited by: Kerry,


WFConsultant

WF 8105M on Win7/Tomcat
August 14, 2009, 09:10 AM
dhagen
What is the authentication of the WFRS?


"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott
August 14, 2009, 09:35 AM
Kamesh
Security : OPSYS
Authentication : IWA


WFConsultant

WF 8105M on Win7/Tomcat
August 14, 2009, 10:07 AM
GinnyJakes
If Oracle is your only data source that you need to do that with, you can set your server up with DBMS security.

We do that here with Teradata. It works very well.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
August 14, 2009, 10:43 AM
Kamesh
Thanks for the suggestions.

There are several application running, so I dont want to change the security of webfocus server.

Is there any other way to do this?


WFConsultant

WF 8105M on Win7/Tomcat
August 14, 2009, 10:51 AM
GinnyJakes
What do you get if you run a simple focexec with a GETUSER function? I.e. What is the value of &IBIC_user? Is it your network id?

You aren't going to be able to pass credentials with a trusted connection. You are going to have to use explicit.

We have a similar requirement here for other data bases. We have a special node that runs an exit (we aren't on Windows) to verify the logon against the AD then pass those values in session variables. The programs that use them -INCLUDE a fex with a connect string that uses the passed variables to connect to the data source. You probably won't have to do anything that dramatic but your default Oracle connect string will have to have variables for the user and password.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
August 17, 2009, 03:15 PM
Kamesh
If I try the GetUser function, it returns the webfocus server userid.

This is my code,

-SET &IBIC_user='&USERID';

ENGINE SQLORA SET DEFAULT_CONNECTION PortalTest
SQL SQLORA

SELECT USER FROM DUAL;

TABLE FILE SQLOUT
PRINT *
END
-RUN
-EXIT


Error I am getting,

(FOC1400) SQLCODE IS 1017 (HEX: 000003F9)
(FOC1394) CONNECT FAILURE
: ORA-01017: invalid username/password; logon denied
L (FOC1405) SQL PREPARE ERROR.


WFConsultant

WF 8105M on Win7/Tomcat
August 17, 2009, 03:29 PM
GinnyJakes
If you have a default id stored for the reporting server in the client AND you don't make your users logon, you are never going to have anything but the default id in IBIC_user.

I also don't see a connect string for Oracle. It should look something like this:
ENGINE SQLORA SET CONNECTION_ATTRIBUTES ora_conn_name ora_server/userid,password


If this string is in your edasprof.prf, then the pw will be encrypted. If the connect string is not in your edasprof.prf, it needs to be somewhere. If it is in edasprof.prf and you have amper variables for the userid and password, then you need to make the users of the application logon in order to populate the variables. Or do some fancy Windows stuff to pass the Windows authentication to the reporting server. You might check the security manual on this topic.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
August 17, 2009, 06:44 PM
dhagen
quote:

ENGINE SQLORA SET CONNECTION_ATTRIBUTES testCon/,


Remove the comma from the above connection string. The comma signifies a trusted connection. The lack of comma signifies password passthrough. That should send the valid userid and password to Oracle.


"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott
August 17, 2009, 07:22 PM
Kamesh
I have tried that but didn't work. When I check the log it shows the webfocus serverid only not the active directory userid.


WFConsultant

WF 8105M on Win7/Tomcat
August 18, 2009, 09:19 AM
GinnyJakes
Kamesh,

Let's go over this again. First you have to have an EXPLICIT connection set up for Oracle. Then you have to replace the userid and password in the connect string with amper variables. Then the user has to logon with his or her credentials or you have to figure out a way to pass them to the reporting server as variables that you can substitute in a connect string.

A trusted connection is never going to work.

If you don't want to do the stuff in my first paragraph, then I suggest that you look into setting up your reporting server with DBMS security.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
August 19, 2009, 08:04 AM
CLH
Hi, have you tried setting the connection up as password passthru ? We use this with some of our Oracle connections.


Webfocus 8.0.7 on Windows
August 20, 2009, 09:32 AM
Kamesh
The trusted connection works if I run my report directly from reporting server. But when I try it from webfocus client, it didn't work. Is there a way that I can just pass the userid and able to connect to reporting server on fly?


WFConsultant

WF 8105M on Win7/Tomcat
August 20, 2009, 09:36 AM
GinnyJakes
I have told you how to do that in previous posts. You are not going to be able to use a trusted connection to do this.

Maybe you should open a case with IBI.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
August 20, 2009, 09:44 AM
Kamesh
Already I have opened a case with IBI. Still waiting for them to revert me back with some suggestions.


WFConsultant

WF 8105M on Win7/Tomcat