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.
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.
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:
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?
serverhost:port/sid
is technically 'wrong'. This 'easy connect' syntax isserverhost:port/service_name
. It doesn't work with (now obsolete) SIDs, so avoid being distracted by answers about SIDs! – Christopher Jonesserverhost:port/service_name
then the error isFailed 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