Forum Discussion
How to update oracle data source credentials for on-premises gateway ?
Hi Anonymous ,
Thank you for reaching out to Microsoft Fabric Community Forum.
can you check this below mentioned points.
1)Normally, Oracle uses the tnsnames.ora based on which Oracle Client the application is using.
2)If your SQL Developer is installed separately, it usually does not read these files. But if any Power BI / Gateway / ODBC connection uses Oracle Client, then it depends on which client path is referenced in the PATH Environment Variable.
3)Check your Environment Variable PATH and see if any of these paths (ODAC or Oracle Client for Microsoft Tools) is mentioned. Whichever client path is first in the PATH variable, that's the one the system will use.
Q2)
MYORACLEDATA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = your_oracle_server_hostname_or_ip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = your_service_name_from_DBA)
)
)
HOST = the Oracle server's hostname or IP
SERVICE_NAME = your Oracle Database's service name (like orcl.example.com or whatever your DBA provides).3)Ask your DBA to check the Oracle sessions to find which machine/program is locking the account.
Regards,Chaithanya.
Hi Anonymous ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,
Chaithanya.
Hello Chaitanya,
I followed the troubleshooting steps you mentioed. First I tried to connect to oracle data source from desktop app.
Got account unlocked from DBA.
Cleared all permeissions.
Removed data source fro global source and closed desktop app.
Added new credentials in personal gateway and it accepted.
Restarted personal gateway
Opened dektop app, connected to oracle data source with new credentials and run the SQL query from the popped window.
All data appeared on dashboard with current date. Also I was able to refresh data. Then after few mins account got locked and data disappreed from dashboard with error unable to connect to source.
When I checked credentials, I could see it was switched back to old credentials. Password was old one and not newly updated one.
I had creed ticket to Microsoft team and that time I was not able to connect to data source from desktop app itself and they redommanded to contact DBA or database team. However, my DBA says it is power BI issue whicj is locking account.
Could you please elaborate more on Properly configured TNSNAMES.ORA file.
please share screenshot or details how this file should be configured.
currently it is like as below. Do I need to fill out these details? Also I do not see any password or credentials fields in those details.
<data source alias> =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <hostname or IP>)(PORT = <port>))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = <database service name>)
)
)Thanks much for your consistant support!
Hi Anonymous ,
Thank you for reaching out to Microsoft Fabric Community Forum.
I have given all the possbile debug steps to resolve your issue can try below as well.
1)Ensure only the 64-bit Oracle client is installed and check the Oracle client path using where tnsping in Command Prompt.
2)Test the TNS alias resolution with tnsping <TNS alias>.
3)Locate and open the tnsnames.ora file (e.g., C:\oracle\product\<version>\client_1\network\admin\tnsnames.ora).
4)Ensure the correct format in tnsnames.ora for the Oracle alias:
MYORACLEDATA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <hostname/IP>)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = <your_service_name>)
)
)
5)In Power BI Desktop, go to File → Options and settings → Data source settings and remove all existing Oracle-related data sources and credentials.6)Open Credential Manager in Windows Control Panel and delete any saved Oracle or Power BI Gateway credentials.
7)In the On-premises data gateway app, remove any old Oracle data sources and add a new data source with the correct TNS alias and updated credentials.
8)Restart the On-premises data gateway after saving the new configuration.
9)In Power BI Desktop, choose Oracle Database as the source, enter the correct TNS alias, and provide the updated username/password for authentication.
10)In Power BI Service, go to Settings → Datasets → Data source credentials, update the credentials, and temporarily disable scheduled refresh until all configurations are verified.
Regards,
Chaithanya.
Thank you Chaintanya for your sugesstions. Howerver,I have checked credentials manager for windows and did not find any oracel credentilas and cleared prermissions and removed all existing oracle connections from desktop app also cleared cached data data from file-> Options. and restarted personal gateway and it is still locking oracle account. I stopped Standard On-Premises Gateway service from server and satandand gateways is offline now. Still oracle account is getting locked in SQL developer. Did traceroute to hotname and it is showing Request timed out after 4 hops.
Also I have few questions regarding steps you recently mentioned.
3)Locate and open the tnsnames.ora file (e.g., C:\oracle\product\<version>\client_1\network\admin\tnsnames.ora):
Q. On my PC tnsnames.ora file is at 2 locations: 1. 1. 1.C:\Users\****username\ODAC122010Xcopy_x64\network\admin\sample location.
2.C:\Program Files\Oracle Client for Microsoft Tools\network\admin\sample
Q: Do I need to fill out those details in tnsnames.ora file and if yes then which folder location file I need to fill out since I have file at 2 locations.
4)Ensure the correct format in tnsnames.ora for the Oracle alias:
MYORACLEDATA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <hostname/IP>)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = <your_service_name>)
)Q. 2)Test the TNS alias resolution with tnsping <TNS alias>.
Where I can find TNS alias?
Thanks much for your consistant support!