Thursday, July 26, 2007

Another day, another SharePoint learning experience for me...This time one of my SharePoint colleagues at work, Viraf, was trying to connect to an Oracle database using the BDC. I'd had some previous success using database credentials authentication to a SQL Server-backed CRM application so he and I put our heads together to get this BDC Oracle connection working.

Because we didn't have Oracle installed anywhere initially, we began by mocking up one of the client's tables in a temporary SQL Server 2005 database and using the steps in my previous post to connect to it. Since those instructions weren't using Integrated Windows Authentication and used plain old SQL command text, we figured this would be a pretty good head start. The only major change to the BDC Schema App file was the line:

<Property Name="DatabaseAccessProvider" Type="System.String">SqlServer</Property>

had to change to:

<Property Name="DatabaseAccessProvider" Type="System.String">Oracle</Property>

Any table names referenced in the SQL Command text in the App schema had to be prefaced with the database schema name; ex:

SELECT Field1, Field2 FROM MySchemaName.TableName

Unfortunately although all was right with the BDC schema, there was an authentication error when trying this against the client's Oracle instance. The culprit was the Single Signon database which managed the connection.

In the SQL Server test database Viraf was using, the only fields required in the Single Signon Application Schema were User Id and Password. However, Oracle requires one more field (Field 3) to complete the Connection String: "Integrated Security".

After adding this, in the “Manage Single Signon” section, we went to “Manage Account Information for enterprise application definitions” and selected the application. We needed “\Domain Users” as the group that would authenticate using SSO (in other words, every SharePoint user would use this connection). Finally, we filled out the User Id field with the Oracle username, the Password field with the account password, and the Integrated Security field with the value "no".

Although we were able to get this running internally after Viraf set up an Oracle installation to prove this would work (it did), the deployment at the client site failed with the following error in the SharePoint log files:

System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.

Viraf found the following article on Roy Tore Gurskevik's blog that seems to explain it: . The good news is that to reach this point a connection has to be happening and SSO reports that the connection string is being retrieved. We're awaiting official confirmation but it looks like this is an environmental issue.


  1. I am receiving the same error. Did you guys ever get around this issue? I have tried the link on this post as well. Still same error.


  2. The client was unwilling to upgrade their client version of Oracle, so in this case we were unable to confirm. However all signs pointed to this being a problem with the client driver version which would have been fixed had they upgraded it.

  3. Thanks for the quick comment.

    Do we have to reboot all the servers or just the front-end server where the web part is failing?


  4. Just so I'm clear - you are getting this error when using the Business Data Catalog, right?

    If so, it's not the web part (BDC item or list view web part I presume you mean?) that is failing but the Oracle driver that BDC is trying to talk to.

    Not sure you have to reboot anything - it might just be a matter of updating or installing the appropriate Oracle client which may not require a reboot.


Note: only a member of this blog may post a comment.