OLE DB provider missing from SQL Server Analysis Services 2022

Products

• Information Centric Analytics

Issue/Introduction

After installing Information Centric Analytics (ICA) in an environment running Microsoft SQL Server 2022 (Enterprise Edition or Developer Edition), the following error is logged while running either the nightly RiskFabric Processing or RiskFabric Intraday Processing jobs:

The OLE DB provider "SQLNCLI11" has not been registered. (Microsoft SQL Server, Error: 7403)

This message refers to the provider used by Microsoft SQL Server Analysis Services (SSAS) to connect to the RiskFabric relational database hosted by Microsoft SQL Server (MSSQL) during cube processing. If you attempt to edit the data source provider using SQL Server Management Studio (SSMS), the following error is displayed:

Connection Manager

The specified provider is not supported. Please choose different provider in connection manager.

Environment

Version : 6.x

Component : Microsoft SQL Server

Cause

From the Microsoft document SQL Server Native Client:

The SQL Server Native Client (often abbreviated SNAC) has been removed from SQL Server 2022 (16.x) and SQL Server Management Studio 19 (SSMS). The SQL Server Native Client (SQLNCLI or SQLNCLI11) and the legacy Microsoft OLE DB Provider for SQL Server (SQLOLEDB) are not recommended for new application development. Switch to the new Microsoft OLE DB Driver (MSOLEDBSQL) for SQL Server or the latest Microsoft ODBC Driver for SQL Server going forward.

Resolution

Change the cube’s data source provider to Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) by following this procedure:

1. Open SQL Server Management Studio (SSMS)
2. Connect to the Analysis Services server hosting the RiskFabric cube
3. In Object Explorer, navigate to Databases > RiskFabric > Data Sources
4. Right-click the RiskFabric data source and select Properties
   The Data Source Properties - RiskFabric window will open
5. Under the General heading, select the Connection String and click the ellipsis to the right of the string
   The Connection Manager window will open
6. From the Provider dropdown menu, select Microsoft OLE DB Driver for SQL Server
7. From the Server name dropdown menu, select the name of the server hosting SQL Server and the RiskFabric database
8. From the Select or enter a database name dropdown menu under the Connect to a database heading, select the RiskFabric database
9. Click the OK button to save your settings and close the Connection Manager window
10. Click the OK button to save your settings and close the Data Source Properties - RiskFabric window