topic Re: Can't connect to Oracle Database from Microsoft Report Builder in Report Server

Can't connect to Oracle Database from Microsoft Report Builder

Hassanmoussa — Mon, 31 May 2021 10:14:35 GMT

Hi,

I have installed the 32 bit of ODAC on my laptop. The installer gave me the option for machine wide level but not GAC something (The option was not there. It used to be there). I can connect Power BI desktop to the Oracle Database but not report builder.

It is giving me the below issue:

"An attempt has been made to use a data extension 'ORACLE' that is either not registered for this report server or is not supported in this edition of Reporting Services."

I think I have 64 bit or 32 bit on server. I am not sure, but it is working fine for Power BI files on server. I also have Power BI Deskop x64 and it is working fine.

I am using the below connection string:

Data Source=Service_Name;User Id=user;Password=password;Integrated Security=no;

Could you please help?

Re: Can't connect to Oracle Database from Microsoft Report Builder

Hassanmoussa — Mon, 31 May 2021 10:31:12 GMT

Update:

I have run both cmd lines on both server and my machine. They were in the below article. On both server and my machine I am using version 12.2

https://docs.microsoft.com/en-us/sql/reporting-services/report-data/oracle-connection-type-ssrs?view=sql-server-ver15

Still same issue.

Re: Can't connect to Oracle Database from Microsoft Report Builder

v-deddai1-msft — Wed, 02 Jun 2021 09:46:02 GMT

Hi Hassanmoussa ,

Trying to un-install and re-install ODAC 12.2. And when you run the cmd in https://docs.microsoft.com/en-us/sql/reporting-services/report-data/oracle-connection-type-ssrs?view... . Please use run as administrator.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

Re: Can't connect to Oracle Database from Microsoft Report Builder

Hassanmoussa — Sun, 06 Jun 2021 07:46:22 GMT

How do I uninstall the ODAC exactly? There is no uninstall for that. Should I simply delete the files? Could you please be more specific and share an article please?

Re: Can't connect to Oracle Database from Microsoft Report Builder

Anonymous — Mon, 07 Jun 2021 01:06:11 GMT

Hi @Hassanmoussa ,

I had faced same issue on Report builder, then I had tried to connect to Oracle database through ' ODBC ' type ( 32bit ) and it went fine. You can try this way ...

Re: Can't connect to Oracle Database from Microsoft Report Builder

Hassanmoussa — Mon, 07 Jun 2021 09:58:29 GMT

OK Here is an update. First, many thanks to v-deddai1-msft

Here are the steps I followed one by one to solve my problem. Please Read them first, then execute. Otherwise you will get lost and get into trouble.

1- Delete any reference to oracle packages in machine.config:

See this article to find machine.config

.net - Where Is Machine.Config? - Stack Overflow

- Please becareful not delete parent section of the reference. e.g. see below:

<system.data>
<DbProviderFactories>
"Some Add elements"
</DbProviderFactories>
</system.data>

- So you need to delete what's inside DbProviderFactories tag, not the whole tag.

2- Go the location of the oracle installation, and delete eveyerthing. Usually it is something like:

C:\app\client\product\12.2.0

-So what I did is I deleted the whole app folder.

3- Go to environment variable and delete the references to the oracle drivers. Below screenshot should say all:

4- Go to this article below. For your laptop / work computer, install 32 bit and 64 but execute the command lines for managed 32 and unmanged 64. I had report builder (32bit uses managed) and Power BI Desktop (64 bit uses Unmanged).

Oracle Connection Type (SSRS & Power BI Report Server) - SQL Server Reporting Services (SSRS) | Microsoft Docs

-Please don't use codes you find on the forum. Only use codes from the article.

5- On the server just install the 64 bit. But execute commands for both managed and unmanaged. The article above will explain it all.

6- If you have .Net applications in Visual Studio, please make sure they use the same version of oracle packages from Nuget that you installed.

Few Final notes:

A- Try to have the same version on server and on your development laptop. This will be very important if you have .Net apps. Otherwise your apps won't run if they refrence a higher version and on the server you have a lower version. or Atleast that's my experience.

B- I used the oracle (.exe) installer for 64 bit and 32 bit. Please run it as Admin otherwise it will throw an error. I never needed to use XCopy and I don't think you need that.

Re: Can't connect to Oracle Database from Microsoft Report Builder

petergroft — Tue, 12 Jul 2022 06:10:20 GMT

Connect Database Using The Direct Connection Method

This method works with all the versions of Oracle Forms builder above 6i. The steps are as follows:

In Oracle Forms builder, click on the File menu and then click Connect option.
Then in the Connect window, provide the username in the Username field.
Provide a password in the Password field.
In the Database field, provide the information using the format IP-Address:Port/OracleSID. For example, 123.456.78.9:1521/ORCL.
Then click on the connect button to connect the database.

This May Work,

Peter

Re: Can't connect to Oracle Database from Microsoft Report Builder

7700-2 — Mon, 19 Sep 2022 22:34:46 GMT

This is a lifesaver. This whole SSRS-Oracle connection is a nightmare, and you have solved about 6 months of frustration. Thank you so much!