0

I am trying to connect SSRS 2017 to an Oracle database to call a stored procedure via ODBC connection. I am able to connect SSRS to Oracle DB and run simple SQL queries. Every time I try to use a Stored Procedure name instead of a SQL query, I get errors that the SP can not access Oracle DB.

Is there any particular format in which I am supposed to pass the SP name.

Note: My SP is a simple select * from Table query without parameters.

Any help will be apreciated.

Priyanka2304
  • 200
  • 3
  • 16

2 Answers2

0

Does the account that SSRS is using to run the report have permission to run the stored procedure?

By default, all stored procedures are secured against other users. You have to grant the EXECUTE database privilege to users who need to access the stored procedures. In this case, that is the service account that runs reports in SSRS (not the account you are logged in to on your computer - SSRS uses its own account).

Chris Latta
  • 20,316
  • 4
  • 62
  • 70
  • same user id is being used to run SP on database and SP on SSRS. If the account that SSRS takes is different then how can that be checked? – Priyanka2304 Jun 12 '19 at 12:53
0

For us, it was a matter of installing the Oracle Developer tools version 18.3.0 inside of Visual Studio and Using the Type - ORACLE Database when setting up the Shared Data Source Properties. Our Oracle Database is version 11G Client. I also needed to add the schema prefix to our package names.

Todd Schulz
  • 1
  • 1