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: http://dotnetjunkies.com/WebLog/rtgurskevik/archive/2005/01/19/45958.aspx . 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.