A distraught user contacted me today with a problem that her Power BI dashboard was not refreshing and returning the following error:
OLE DB:ORA-12154: TNS:could not resolve the connect identifier specified
The problem was not just isolated to her; but common across all her team.
I’m not an expert on Oracle and Power BI; and I kindly suggest for her to contact the Helpdesk. She came back a few minutes later saying that the Helpdesk just passed her off unceremoniously to another team.
Deciding to give her a hand – I had a think about the error message.
TNS files seem to end up in all sorts of weird and wonderful locations on our organisation’s systems.
I also remembered that there was a patching of the Oracle client just done last week.
I asked her to do a search for tnsnames.ora on her C drive.
Meanwhile I ran the following command through the command line on my own computer; which should be a similar configuration:
echo %TNS_ADMIN%
She got back:
C:\app\product\12.1.0\client_1\network\admin
And I got back:
C:\app\product\19.3.0\client_1\network\admin
My hypothesis that IT installed a new version of the Oracle client and this process updated the %TNS_ADMIN% field.
What the patching didn’t do was to shift the tnsnames.ora file to the new directory of C:\app\product\19.3.0\client_1\network\admin.
When Power BI tried to run an Oracle query; it was using the new client installation and hence not finding the tnsnames.ora file.
After copying the TNS file into the correct directory; right away she was able to rerun her Power BI dashboard.