Configure Oracle data source for SQL Server Reporting Services (SSDT and Report Server)

  • [アーティクル]

In general, there are several links available to download the ODAC components from the Oracle official site.  It varies across the Oracle versions, and the type of installer that we would need. Most of the time, we look for the .exe installer to install the Oracle related drivers as we are more comfortable with the Windows MSI installers.

In this blog, we will go through the ODAC driver installation and configuration with the SQL Server Reporting services (SSRS) data source to connect Oracle data source using Oracle native driver.

 

Assumption:

  1. We would be using a single system to develop the report using SQL Server Data Tools (SSDT) and host the report in Report Server. It means SSDT and SSRS both are installed in the same system.
  2. SSDT version – Visual Studio 2017
  3. SSRS – SQL Server Reporting Services 2014.

 

Steps:

1. SSDT runs in 32 bit whereas SSRS runs in 64 bit. Since we are in the same system, we need to install both the bit-ness of Oracle drivers one by one.

We need to go to the Oracle official site to search for the drivers for the required version. Please note that we need to check for the installer which has the OLE DB/ ODP. Net (Oracle data Provider for .Net components.)

For 64-bit driver-

https://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

(Download link available during the time the blog had been written)

 

For 32-bit driver- https://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html

(Download link available during the time the blog had been written)

2. After uncompressing the file, you will see the installer file. Once you double click on that, you will get the information on GUI which is self-explanatory.

You need to note down where the oracle driver is getting installed, you need to place the tnsnames.ora file in that location which we will discuss later.  For me, the installation folder as

E:\app\client\xxxx\product\12.2.0\client_1 (32 bits)

E:\app\client\xxxx\product\12.2.0\client_2\ (64 bits)

Client_#, the number will change according to your installation order.

No need to update any environmental variable. During the installation, it was already done.  You can check the same going to the My computer -> Properties -> Advance System setting -> Environmental Variables -> System Variables -> Path

 

3. Once the 32 bits and 64 bits driver installations are done, you need to place the tnsnames.ora file in the following location

E:\app\client\xxxx\product\12.2.0\client_1\Network\Admin

E:\app\client\xxxx\product\12.2.0\client_2\Network\Admin

 

The format of tnsnames.ora file as below- Ref: https://docs.oracle.com/cd/F49540_01/DOC/network.815/a67440/appb.htm

 <ServerName> =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ##.###.##.###)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

 

 

4. So, we have now successfully installed the Oracle drivers. The next step is to test the connection from Visual Studio and SSRS.

 

5. Open the SSDT. I tested in SSDT 2017. Create a Report Server Project –> File-> New -> Project -> Reporting Services -> Report Server Project

 

 

6. After creating the Reporting Services Project, you need to do the test connection. You can do the test connection by following the below screenshot. Please note that here we are using the OLE DB driver.

 

7. The same test connection, we will now be doing in SSRS. To do that we need to register the OraOLEDB driver. Go to the command Prompt (Run as Admin) -> and then run the following command. The path might vary as per your installation directory. We need to register the 64 bits driver since SSRS run on 64 bits ( E:\app\client\xxxx\product\12.2.0\client_2\bin\OraOLEDB12.dll)

 

8. Once this is done, after deploying the project, we can do the test connection in SSRS

 

 

9. We can use the ODP.Net to connect to the SSRS as well. This is the default driver which shows in SSDT. We need to register the ODP.Net DLLs. Go to the 64 bits installation folder. For me it is -

E:\app\client\xxxx\product\12.2.0\client_2

Run the following commands using the command Prompt (Run as admin).

 E:\app\client\xxxx\product\12.2.0\client_2\odp.net\bin\2.x>oraprovcfg /action:gac /providerpath:E:\app\client\xxxx\product\12.2.0\client_2\odp.net\bin\2.x\Oracle.DataAccess.dll
E:\app\client\xxxx\product\12.2.0\client_2\odp.net\bin\4>oraprovcfg /action:gac /providerpath:E:\app\client\xxxx\product\12.2.0\client_2\odp.net\bin\4\Oracle.DataAccess.dll

 

 

10. Once it is done, we can test the connection from SSRS, and as well as from SSDT.

 

This is how you would be able to test the connectivity from the SSRS to the Oracle database. If it is not working, you might need to test the Oracle connection outside of SSRS. If that does work, and only connections made from SSRS fails, then I would recommend you contact the Microsoft Support team.

 

 

Author:      Samarendra Panda - Support Engineer, SQL Server BI Developer team, Microsoft

Reviewer:   Krishnakumar Rukmangathan – Support Escalation Engineer, SQL Server BI Developer team, Microsoft

Comments

  • Anonymous
    June 11, 2018
    One recommendation when installing Oracle providers - change the path to indicate the version and 32-bit or 64-bit. You're less likely this way to question which folder is for which version later.Another recommendation, you can configure an environment variable "TNS_ADMIN" to point to a common TNSnames.ora file so you only need to maintain one.
  • Anonymous
    January 03, 2019
    I have never logged in to respond to a blog entry like this, but this is very well written and informative.Just note that the 64-bit 12.2 drivers have a bug that will not allow a SYS_REFCURSOR to be returned into an SSRS report via a stored procedure.The 11G 64-bit drivers do work though. I spent quite a few days working this out.
    • Anonymous
      June 06, 2019
      I'm having same issue with SSRS, however with the Oracle 12.2 DB the 11G client doesn't work. Currently I don't have a work around or method for calling the PL/SQL and returning a SYS_REFCURSOR. It works inside of Visual Studio, but when published it fails to run. I think because the Visual Studio is using the Managed version of ODP.net and when published it is using the Oracle Client.
  • Anonymous
    January 10, 2019
    thanks so much for the brilliant overview! i'm up and running, but there is one slight glitch. within vs2017 ide, I can use the server explorer to add a data connection, and the driver dropdown lists odp.net, unmanaged, and managed...perfect! I test the managed connection, and voila! all good. however, when I try to create a new datasource for a report in the solution explorer, only the odp.net driver shows in the driver list. what's up with that?thanks in advance.
  • Anonymous
    January 10, 2019
    The comment has been removed
    • Anonymous
      June 10, 2019
      The comment has been removed