In the ever-evolving world of SharePoint integration, adapting to changes in technology can be a daunting task. Recently, our team faced a significant challenge when Microsoft discontinued the DotNetAssembly type for Business Data Connectivity (BDC) models in SharePoint On-Prem Server (note that BDC is no longer supported for SharePoint Online). This change forced us to rethink our approach to connecting our SharePoint external lists to an Oracle database. In this blog post, I’ll walk you through our journey of developing a new OData-based solution and overcoming compatibility issues with SharePoint’s BDC. The solution provided here is applicable to SharePoint 2016 onwards, including the latest SharePoint Subscription Edition.
One of our clients relies on a SharePoint farm (with three front-end and three application servers, running SharePoint 2016) to provide external lists that display employee data from an Oracle database view (USER.Employee_UPDATE_V). Historically, they have used a DotNetAssembly BDC model, which allowed them to write custom .NET code to connect SharePoint to an Oracle database. This model was straightforward: it leveraged .NET Framework assemblies to define data operations (ReadList, ReadItem) and integrate seamlessly with SharePoint’s Business Connectivity Services (BCS).
However, Microsoft’s discontinuation of the DotNetAssembly type in newer SharePoint versions (2016 and beyond) left them in a bind. Without this model, the existing BDC solution was obsolete, and they needed a new way to connect their SharePoint external lists to the Oracle database. The recommended replacement was the OData type BDC model, which supports modern web standards for data access. This shift introduced an urgent need to develop a new solution, and fast, to maintain their employee data integration in SharePoint.
Step 1: Building an OData Web API for Oracle Connectivity
To address the discontinuation of DotNetAssembly, we decided to create a new ASP.NET Core 8.0.17 Web API, named ODataWebAPI, to serve as an OData endpoint for our Oracle database. For our initial proof of concept, we made this API run as a Windows Service on AppServer1 with the endpoints at http://localhost:5000, providing a modern, scalable interface for SharePoint’s BDC model to consume.
Developing the Web API
We built the Web API using .NET Core, leveraging the Microsoft.AspNetCore.OData package to expose OData V4 endpoints. The API queried the USER.Employee_UPDATE_V view in our Oracle database, returning employee data such as PRSN_ID, LAST_NAME, EMAIL, and other fields. Here’s a simplified version of our PersonsController:
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.OData.Query;
using Microsoft.AspNetCore.OData.Routing.Controllers;
using Oracle.ManagedDataAccess.Client;
using OracleECT;
using System.Linq;
namespace ODataWebAPI.Controllers
{
public class PersonsController : ODataController
{
private readonly IConfiguration _config;
public PersonsController(IConfiguration config)
{
_config = config;
}
[EnableQuery]
public IQueryable<Person> Get()
{
var list = new List<Person>();
var connStr = _config.GetConnectionString("OracleDb");
using (var conn = new OracleConnection(connStr))
{
conn.Open();
// Query USER.Employee_UPDATE_V
// Populate list with Person objects
return list.AsQueryable();
}
}
}
}
The API was deployed to F:\ODataService\Release\net8.0 and exposed endpoints like http://localhost:5000/odata/persons. We configured the BDC model in SharePoint to point to this endpoint, expecting a smooth integration.
Step 2: The OData V4 Roadblock
Our initial excitement was short-lived. While the Web API worked perfectly, returning OData V4-compliant JSON data, SharePoint’s BDC model failed to recognize the external content type (OracleECT.Person) in Central Administration. The fields (PRSN_ID, LAST_NAME, etc.) were not appearing, rendering the external list unusable. After digging into SharePoint’s logs and testing the API, we discovered the root cause: SharePoint 2019’s BCS only supports OData V2/V3, not V4.
SharePoint’s OData connector expects metadata in the older OData V2/V3 format (e.g., JSON Light with specific conventions like odata.metadata and quoted numeric keys). Our Web API, built with Microsoft.AspNetCore.OData, defaulted to OData V4, which uses a different metadata structure and JSON format. This incompatibility meant SharePoint couldn’t parse the metadata or data correctly, leaving our external content type fields invisible.
Step 3: Creating a Custom Metadata Controller for OData V3
To bridge this gap, we needed to make our Web API compatible with OData V3, which SharePoint supports. Instead of rewriting the entire API, we added a custom metadata controller to serve OData V3 metadata and data alongside the existing V4 endpoints. This approach allowed us to maintain our modern V4 API for future use while providing a V3-compatible endpoint for SharePoint.
Implementing the Custom Metadata Controller
We created a CustomODataController to handle OData V3 requests, ensuring the metadata and data formats matched SharePoint’s expectations. Here’s a simplified version:
using Microsoft.AspNetCore.Mvc;
using Microsoft.OData.Edm;
using Microsoft.OData.Edm.Csdl;
using System.Xml;
namespace ODataWebAPI.Controllers
{
[Route("odata/custom")]
public class CustomODataController : ControllerBase
{
[HttpGet("$metadata")]
public IActionResult GetMetadata()
{
var edmModel = BuildODataV3Model();
using var writer = XmlWriter.Create(Response.Body, new XmlWriterSettings { Indent = true });
CsdlWriter.TryWriteCsdl(edmModel, writer, CsdlTarget.OData, out var errors);
return Ok();
}
private IEdmModel BuildODataV3Model()
{
var builder = new ODataConventionModelBuilder();
builder.EntitySet<Person>("Persons");
return builder.GetEdmModel();
}
}
}
We also modified the PersonsController to support V3 JSON formatting (e.g., quoting decimal keys like "55" for PRSN_ID) at the endpoint http://localhost:5000/odata/custom/persons. This involved adjusting the serialization to include odata.metadata and odata.type annotations, as required by SharePoint’s BCS.
Updating the BDC Model
We updated the BDC model (OracleODataModel.xml) to point to the new V3 endpoint:
<LobSystem Name="TestUserOData" Type="OData">
<LobSystemInstances>
<LobSystemInstance Name="TestUserODataInstance">
<Properties>
<Property Name="ODataServiceUrl" Type="System.String">http://localhost:5000/odata/custom</Property>
</Properties>
</LobSystemInstance>
</LobSystemInstances>
<Entities>
<Entity Namespace="OracleECT" Name="Person">
<Methods>
<Method Name="ReadList">
<!-- Map to /odata/custom/persons -->
</Method>
<Method Name="ReadItem">
<!-- Map to /odata/custom/persons("id") -->
</Method>
</Methods>
</Entity>
</Entities>
</LobSystem>
After redeploying the API and updating the BDC model, SharePoint Central Administration finally recognized the external content type fields. The external list displayed employee data correctly, and operations like ReadList and ReadItem worked as expected.
Step 4: Securing Oracle Credentials with Windows Credential Manager
Why Windows Credential Manager?
Windows Credential Manager stores credentials securely in the Windows Credential Vault, encrypted with DPAPI, and is accessible only by the authorized account (e.g., DOMAIN\UserAccount). Unlike a local XML file (e.g., OracleDbCredential.xml), it eliminates file-based storage, reducing exposure.
Storing Credentials
We stored the Oracle credentials as a generic credential in Windows Credential Manager using Windows GUI but it is also possible using PowerShell.
Updating the Web API
We installed the CredentialManagement NuGet package (dotnet add package CredentialManagement) and updated PersonsController.cs to retrieve credentials from Windows Credential Manager:
using CredentialManagement;
private string GetConnectionString()
{
var credTarget = _config["OracleDbCredentialTarget"] ?? "OracleDbCredentials";
using (var credential = new Credential { Target = credTarget, Type = CredentialType.Generic })
{
if (!credential.Load())
{
_logger.LogError("Failed to load credentials from Windows Credential Manager for target {CredTarget}", credTarget);
throw new InvalidOperationException($"Failed to load credentials for target {credTarget}");
}
var username = credential.Username;
var password = credential.Password;
var connStrTemplate = _config.GetConnectionString("OracleDb");
return string.Format(connStrTemplate, username, password);
}
}
We also updated appsettings.json to remove the plain-text password:
{
"ConnectionStrings": {
"OracleDb": "Data Source=ORCL;User Id={0};Password={1};"
},
"Logging": {
"LogLevel": {
"Default": "Information"
}
}
}
Deployment and Testing
We republished the Web API to F:\ODataService\Release\net8.0, restarted the Windows Service (net stop ODataWebAPI; net start ODataWebAPI), and tested the endpoints:
Invoke-WebRequest -Uri "http://localhost:5000/odata/custom/persons(55)"
The external list in SharePoint displayed data correctly, and logs (F:\Logs\ODataWebAPI-*.log) confirmed successful database queries.
Lessons Learned
Conclusion
Transitioning from a DotNetAssembly BDC model to an OData-based solution was a challenging but rewarding journey. By building a .NET Core Web API, implementing a custom OData V3 metadata controller, and securing credentials with Windows Credential Manager, we successfully restored our SharePoint external list functionality. This experience highlights the importance of adapting to platform changes, leveraging modern standards like OData, and prioritizing security in enterprise integrations.
If you’re facing similar challenges with SharePoint BCS, consider reaching out to us to transition you to the OData model.