Oracle – Getting Oracle ODBC to run on a SharePoint Server

Things you shouldn’t do, and the ones you need to do !

1. Download the Instant Client Software

Minimal 2 components needed.

image

Instant Basic Windows client AND the Instant ODBC components, both need to be merged together to make it work.

2. ODBC Oracle does not work on our backend x64 bit server

Now this is where it gets interesting I logged into the report server and ran the report. This time I get a totally different error than when I’m logged in on a test workstation the error is “Cannot create a connection to data source ORA-01017:

SSRS Error in SP integrated mode : system.data.oracleclient requires oracle client software version 8.1.7 or greater

image

Things that did not work to solve it !!

SSRS settings in SharePoint does not work :

Import this Connection string to SSRS ?

<DataSource xmlns:xsd=”http://www.w3.org/2001/XMLSchema&#8221; xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance&#8221; xmlns:dwd=”http://schemas.microsoft.com/DataWarehouse/Designer/1.0&#8243; xsi:type=”RelationalDataSource” dwd:design-time-name=”d4e3b33c-c4ed-4f2c-bd9f-57588a57e2ad” xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”&gt;

<ID>MYORACLEDATASOURCEID</ID>

<Name>MYORACLEDATASOURCENAME</Name>

<CreatedTimestamp>0001-01-01T00:00:00Z</CreatedTimestamp>

<LastSchemaUpdate>0001-01-01T00:00:00Z</LastSchemaUpdate>

<ConnectionString>DATA SOURCE=MYORACLEDATASOURCE;PASSWORD=;USER ID=</ConnectionString>

<ConnectionStringSecurity>PasswordRemoved</ConnectionStringSecurity>

<ManagedProvider>Oracle.DataAccess.Client</ManagedProvider>

<Timeout>PT0S</Timeout>

</DataSource>

Step 1 Add the dataSource to the ssrs web.config

<connectionStrings>

<add

name=”BaanDB”

connectionString= “Data Source=ServerName;Integrated Security=false;User Id=User;Password=Pswd”

providerName=”System.Data.OracleClient” />

</connectionStrings>

Don’t forget to restart the SSRS service !

Step 2 Solve The error -> System.Data.OracleClient requires Oracle client software version 8.1.7

http://stackoverflow.com/questions/447983/system-data-oracleclient-requires-oracle-client-software-version-8-1-7

To fix the problem you have to give the Authenticated Users group privilege to the Oracle Home directory.

• Log on to Windows as a user with Administrator privileges.

• Start Window Explorer and navigate to the ORACLE_HOME folder.

• Choose properties on the ORACLE_HOME folder.

• Click the “Security” tab of the “Properties” window.

• Click on “Authenticated Users” item in the “Name” list.

• Un-check the “Read and Execute” box in the “Permissions” list under the “Allow” column.

• Re-check the “Read and Execute” box under the “Allow” column.

• Click the “Advanced” button and in the “Permission Entries” verify that “Authenticated Users” are listed with permission = “Read & Execute”,

and Apply To = “This folder, subfolders and files”.

If not, edit that line and make sure that “Apply To” drop-down box is set to “This folder, subfolders and files”.

This should already be set properly but it is important that you verify it.

• Click the “Ok” button until you close out all of the security properties windows.

The cursor may present the hour glass for a few seconds as it applies the permissions you just changed to all subfolders and files.

• Reboot, to assure that the changes have taken effect.

SSRS Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed.

Solution:

I had to downgrade the ODBC driver version that was compatible to the old version of Oracle 8.1.7

See next article how to solve the System Error Code 126

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: