1
votes

I am developing an app and have a working connection to a Oracle Database using a Connection String like this:

"Data Source=(DESCRIPTION=(ADDRESS=(COMMUNITY=tcpcomm)(PROTOCOL=TCP)(HOST=mydatabasehost.myserver.com)(PORT=1529))(CONNECT_DATA=(SERVICE_NAME=code123.myserver.com))); User Id=user;Password=123456;";

I checked and I can also connect to this database using Oracle SQL Developer on my laptop.

Now I am trying to connect to an Oracle Database using Azure Logic Apps Oracle Connector.
Azure Logic Apps Connector But it requires:

serverhost:port/sid

I tried only serverhost:port but it also does not connect, gives this an error like this:

Test connection failed. Details: Oracle: ORA-12154: TNS:could not resolve the connect identifier specified inner exception: Oracle: ORA-12154: TNS:could not resolve the connect identifier specified clientRequestId:

I tried these 3 queries:

select distinct sid from v$mystat;
select * from global_name;
select value from v$parameter where name='service_names';

But none of the returned values worked locally when I select SID.
Oracle SQL Developer
I get

Invalid Username/password; logon denied

On Logic Apps this error appear: Failed to create connection for connection id

'/providers/Microsoft.PowerApps/apis/shared_oracle/connections/shared-oracle-....'. Encountered internal server error from Data Transfer Service. The tracking Id is '...'.

Locally, if I use the SERVICE_NAME then it works. But in order to use the Logic Apps connector I need the SID.

I checked the On-premises data gateway Network ports test and it says: On-premisses data gateway

How can I find out the SID by typing a SQL Query into my existing Oracle Connection on Oracle SQL Developer? Or how can I connect from Azure Logic Apps using the SERVICE_NAME?

Is it possible that the server is not accepting connections using the SID, and only accepts connections using the Service Name?

3
serverhost:port/sid is technically 'wrong'. This 'easy connect' syntax is serverhost:port/service_name. It doesn't work with (now obsolete) SIDs, so avoid being distracted by answers about SIDs!Christopher Jones
WHen I try serverhost:port/service_name then the error is Failed to create connection for connection id '/providers/Microsoft.PowerApps/apis/shared_oracle/connections/shared-oracle-...'. Encountered internal server error from Data Transfer Service. The tracking Id is ...Tony
Is the target database a pluggable database (PDB)?pmdba

3 Answers

2
votes

My first answer was lame. You are getting this error:

ORA-12154: TNS:could not resolve the connect identifier

So, it is trying to look up your server in a tnsnames.ora file rather than use host, port, sid or service name. It could be that there is another option that lets you type all that in. Or you may need to install an oracle client and setup a tnsnames.ora entry for your target server.

tnsnames.ora entry would look something like this:

MYSERVER=
(DESCRIPTION=(ADDRESS=(COMMUNITY=tcpcomm)(PROTOCOL=TCP) 
(HOST=mydatabasehost.myserver.com)(PORT=1529))(CONNECT_DATA= 
(SERVICE_NAME=code123.myserver.com)))

Bobby

1
votes

You can find the SID by querying the v$thread view in the existing Oracle SQL Developer connection:

If ORACLE_SID = DB_SID

SQL> select instance from v$thread;
INSTANCE
----------------
DB_SID

More info on DB_NAME and ORACLE_SID here

1
votes

You can query the Oracle SID with the query

SELECT sys_context('userenv','db_name') FROM DUAL;

It is accessible by every user, you don't need special privileges to use it.