Establish Oracle OLEDB connection in SSAS

Question

Trying to establish an Oracle data source in SSAS using the data source wizard. I have the connection information in the traditional TNS format:

CMAN1=
  (CONFIGURATION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=proxysvr)(PORT=1521))
    (RULE_LIST=
      (RULE=(SRC=192.0.2.32/24)(DST=sales-server)(SRV=*)(ACT=accept)
        (ACTION_LIST=(AUT=on)(MCT=120)(MIT=30)))
      (RULE=(SRC=192.0.2.32)(DST=proxysvr)(SRV=cmon)(ACT=accept)))
    (PARAMETER_LIST=
      (MAX_GATEWAY_PROCESSES=8)
      (MIN_GATEWAY_PROCESSSES=3)))

Without having to provide username and password (security is contained within TNS protocol), how do create this connection?

There are not intuitive entry fields for this information.

I have gotten a little closer. I believe I enter the connection string as follows: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oradev01.aetvn.com)(PORT=1823)))(CONNECT_DATA=(SERVICE_NAME=gabqa1.world))) However, I still need a user account for login information. Does this user need access to the database? Or is it just any user who has domain access and then I impersonate the service account? — Pablo Boswell, Jan 08 '16 at 20:25

score 0 · Answer 1 · edited Aug 10 '21 at 14:46

After trial and error, here we go:

if you are using a TNS connection, you can enter the TNS connection alias in the connection string property as follows (parameter values are just an example):

(CONFIGURATION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=proxysvr)(PORT=1521))
    (RULE_LIST=
      (RULE=(SRC=192.0.2.32/24)(DST=sales-server)(SRV=*)(ACT=accept)
        (ACTION_LIST=(AUT=on)(MCT=120)(MIT=30)))
      (RULE=(SRC=192.0.2.32)(DST=proxysvr)(SRV=cmon)(ACT=accept)))
    (PARAMETER_LIST=
      (MAX_GATEWAY_PROCESSES=8)
      (MIN_GATEWAY_PROCESSSES=3)))

if you need security authorization, just add at the end (*after the last closing parentheses):

";User ID=xxxxx;Password=xxxxx"

Establish Oracle OLEDB connection in SSAS

1 Answers1