CASE # 30643426 - SQL 2016 SSIS/SSRS not recognizing Oracle 19c 64 Bit Client

Question

We have opened a case with Microsoft, but are not getting any response on this. How do we get Microsoft to actually pay attention to a Support Ticket?

We have several SQL 2012 servers with SSIS/SSRS on them, using Oracle Clients for connecting to Oracle DBs. As 2012 is going out of support, we need to migrate these to SQL 2016 SSIS/SSRS. While we can get 2012 to properly reference and use the installed Oracle Clients (12.2 and 19c), SQL 2016 does not seem to even recognize that an Oracle Client is even installed on the server.

We have installed the Oracle 19c 64 Bit Client on the server. Also, the additional commands were run to load several Oracle component references into the GAC on the server (this is what typically fixed the issue with SQL 2012). In particular, we need to use the "Oracle Database" type of connection for establishing the connection to an Oracle DB, as this apparently references the .Net ODAC drivers for Oracle. We have a large number of reports that were built using this connection type, but cannot get these to work on the 2016 server.

Below are the commands we use to load the Oracle components to the GAC. These work when done on a SQL 2012 server, but don't seem to do what is needed for SQL 2016 to be able to use the installed Oracle 19c Client.

D:\SrvApps\Oracle\product\19.0.0\client_1\odp.net\bin\4\OraProvCfg.exe /action:gac /providerpath:"D:\SrvApps\Oracle\product\19.0.0\client_1\odp.net\bin\4\Oracle.DataAccess.dll"

D:\SrvApps\Oracle\product\19.0.0\client_1\odp.net\bin\4\OraProvCfg.exe /action:gac /providerpath:"D:\SrvApps\Oracle\product\19.0.0\client_1\odp.net\PublisherPolicy\4\Policy.4.112.Oracle.DataAccess.dll"

D:\SrvApps\Oracle\product\19.0.0\client_1\odp.net\bin\4\OraProvCfg.exe /action:gac /providerpath:"D:\SrvApps\Oracle\product\19.0.0\client_1\odp.net\PublisherPolicy\4\Policy.4.121.Oracle.DataAccess.dll"

D:\SrvApps\Oracle\product\19.0.0\client_1\odp.net\bin\4\OraProvCfg.exe /action:config /product:odp /frameworkversion:v4.0.30319 /providerpath:"D:\SrvApps\Oracle\product\19.0.0\client_1\odp.net\bin\4\Oracle.DataAccess.dll"

Any advice would be appreciated.

Answer 1

Why do you use a .NET data provider for your reports and not the easier & often better way with OleDB or ODBC?

Anyway, see https://learn.microsoft.com/en-us/sql/reporting-services/report-data/register-a-standard-net-framework-data-provider-ssrs?view=sql-server-ver15

Answer 2

Olaf,

We apparently have at least a few hundred reports (possibly a couple of thousand or so) that were written using the Oracle .Net connector for getting to the data. Many of these are several years old, but still in production. We do not have enough resources to redo all these reports in the time allowed. Therefore, we need to use the same technology the reports were written with so we can move off of the older servers to more modern ones.

Any project to rewrite all these reports would require executive sponsorship and funding. With the economy as it is right now, that is very unlikely to happen. Thus the need to get SQL 2016 to recognize the installed Oracle 19c Client and use the DB connector technology the reports are already written for.

Thanks,

Answer 3

Has the DBA tried to make the SQL Server run as 2012?