Troubleshooting To connect to an Oracle database with Power BI Desktop, the correct Oracle client software must be installed on the computer running Power BI Desktop. The Oracle client software you use depends on which version of Power BI Desktop you've installed: 32-bit or 64-bit. It also depends on your version of Oracle server. Download the correct version of the Oracle X Copy from the below link: 64-bit Oracle Data Access Components (ODAC) for Windows. Download the same bit as Power BI. If you have 64-bit Power BI, download 64-bit ODAC XCopy. Extract the downloaded ZIP file; Run a command prompt from the extracted directory as an administrator.
We had a case that came in where the customer was trying to configure a Data Source within the Power BI Admin Center for an Oracle Server. During the creation of that Data Source, they went to set the credentials, and received the following error.
- Once I had completed my Power BI Desktop file I then uploaded it into the Power BI Service. Configuration of the Power BI Service Gateway. In the steps below I now will show how I created and configured the Gateway to connect to the Oracle database. NOTE: I had already installed and configured the On-Premise Gateway within.
- 'The recommended provider ('Oracle.DataAccess.Client') is not installed. You can continue with your current provider, however it has been deprecated and may not work properly.' I click on 'Learn more' hyperlink which led me to this document page.
Oracle Dataaccess Version 2.112.1.0
Failed to test connection. The ‘OraOLEDB.Oracle’ provider is not registered on the local machine.
There was nothing present within System Health of the Power BI Admin Center. Within the Data Management Gateway Event Log, we do see the error.
Log Name: Data Management GatewaySource: Data Management Gateway Host ServiceDate: 1/7/2015 9:55:27 AMEvent ID: 20001Task Category: NoneLevel: WarningKeywords: ClassicUser: N/AComputer: CAPTTHRACE.battlestar.localDescription:Failed to connect to the database. Error message: The ‘OraOLEDB.Oracle’ provider is not registered on the local machine.
Even though we may be doing the test connection on a different machine from where the Data Management Gateway is located, the test connection is actually invoked via the Data Management Gateway. That is why we see the error in the Event Log for the Data Management Gateway.
This issue is due to the fact that we can’t find the Oracle OLEDB Provider from the Data Management Gateway perspective. We can verify this by using a Universal Data Link (UDL) file to see what OLEDB Providers are available on the machine. See Connectivity 101 for more information regarding a UDL File.
NOTE: By default the Oracle OLEDB Provider should be installed. I saw this with both Oracle 11 and Oracle 12.
Bitness Matters (32bit vs. 64bit)
One thing to remember with the UDL and with the Data Management Gateway is that bitness matters. If you are on a 64bit machine, the UDL, by default, will show the 64bit OLEDB Providers. If you installed the 32bit Data Management Gateway on a 64bit Windows machine, you will need the 32bit Oracle OLEDB Provider.
Looking on the machine where I have the Data Management Installed, I don’t see Oracle’s OLEDB Provider listed on the 64bit side. I have the 64bit Data Management Gateway installed.
32bit Providers
When looking at the 32bit UDL, you will see an Oracle provider listed called Microsoft OLE DB Provider for Oracle.
This is not the Oracle OLEDB Provider that comes from Oracle itself. This is an old Microsoft Provider that ships with windows and makes use of the Oracle 7 & 8 client libraries. The Data Management Gateway does not use this Provider. The Microsoft provider is only a 32bit provider and will not show up in the 64bit list.
Oracle’s Provider
In my limited testing, the Oracle OLEDB provider was installed when I installed the Oracle Server itself. So, in most cases, I would think the provider should already be listed. If it isn’t, you will need to install it using the Oracle Data Access Components (ODAC) install. This comes in a 64bit and 32bit flavor. Here are the links, I could find today, to download ODAC. You may want to validate the version of your Oracle Server if you want to match up the versions between the Server and Client libraries.
Power Bi Oracle Data Access Client Not Installed Supported
64-bit Oracle Data Access Components (ODAC)https://www.oracle.com/technetwork/database/windows/downloads/index-090165.html
32-bit Oracle Data Access Components (ODAC)https://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html
After that is installed, we should see Oracle Provider for OLE DB in the provider list within the UDL. You will then need to restart the Data Management Gateway Service. The test connection should work at that point. If it still doesn’t, try rebooting the server that the Data Management Gateway is installed on.
Adam W. Saxton | Microsoft SQL Server Escalation Serviceshttps://twitter.com/awsaxton