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.
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.
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