SSRS: OLE DB Oracle provider is not registered on the local machine

Question

I am using SQL Server Reporting Services (SSRS), and I am using Report Builder to create a report from Oracle database. So I created a new report and I added a new data source with OLE DB as a Connection Type, and then I clicked on Build button to configure the connection properties with Oracle Provider for OLE DB.

I provided the server name and the UserName and Password correctly but when I clicked on Test Connection button, I got the below error:

Unable to connect to Data source
The OraOLEDB.Oracle provider is not registered on the local machine

Why I got this error and How I can configure Oracle Data Source in SSRS Report Builder?

Answer 1

You get this error "The OraOLEDB.Oracle provider is not registered on the local machine" because the Oracle Data Access for Oracle Client is not currently installed on the current local machine.

Important Note:
- If you are using Microsoft Report Builder or Power BI Report Builder with versions before 15.7.01678.0001, you have to install the 32-bit Oracle Data Access for Oracle Client (ODAC-32 bit) to be able to connect to Oracle DB as a data source through the Oracle Provider for OLE DB.
- If you are using Power BI Report Builder version 15.7.01678.0001 and later, you should install the 64-bit Oracle Data Access for Oracle Client (ODAC-64 bit).
For more details, Please check Oracle Connection Type (SSRS & Power BI Report Server)

How to install Oracle Client Data Access for Microsoft Report Builder?

To can use Oracle Database as a Data Source in Microsoft SSRS Report Builder, you have first to install the 32-bit Oracle Data Access for Oracle Client that includes Oracle Provider for OLE DB by doing the following:

1. Open this URL 32-bit Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studio.
2. Based on your Oracle Server version, install the corresponding 32-bit release, so if you are using Oracle 11g, search for the 'ODTwithODAC122011.zip', then click on the download link to download the 32-bit Oracle Data Access for Oracle Client for Microsoft Report Builder.

   Note: you will be asked to log in with an Oracle account or create a new one to can download a component from the Oracle site.

3. Once the zipped folder is downloaded, Extract it, and then click on "Setup.exe" to start the Oracle Universal Installer.
   
4. Select product language then click 'Next'
   
5. To install and configure the Oracle Home, Use a Windows Build-IN account or provide the service account that will be used to run the Oracle Home Windows Service, and then click Next.
   
6. Specify the Oracle base directory location
   
7. If you have Visual Studio installed on your machine, check "Oracle Developer Tools for Visual Studio". Otherwise, select other options as shown
   
8. Check "Configure ODP.NET and Oracle Providers for ASP.NET at a machine-wide level", and then click Next.
   
9. Follow the installation wizard, and once the installation is done, you have to configure tnsnames.ora file as mentioned at Configure tnsnames.ora file for VS
10. Close the Report Builder and reopen it again.

    Don't forget to restart the SSRS service or Power BI report server service in the case you are installing the ODAC and change tnsnames.ora file on the same reporting server.

11. Create a new data source with Oracle Provider for OLE DB.
12. Test the connection that should be working properly now.
    

Note: Oracle Provider for OLE DB doesn't support executing Oracle Database Stored Procedure, Instead, you should use Oracle Connection type.

Comments

Great answer as usual Mohamed, I installed the ODAC 32 bit and the error is gone.however, I got another error when I clicked on the Test connection "ORA-12154: TNS:could not resolve the connect identifier specified", Could you please help me to fix this new error?

---

You are welcome, Looks like you didn't configure tnsnames.ora file as mentioned on step 7.
So I would suggest adding the new error in a new thread with more details to can help you faster, and please upvote and mark the answer as accepted in case it helped you!