Class not registered error after restoring an Analysis Services database that uses the SQL Server 2005 SQL Native Client.
After restoring an Analysis Services database that uses the SQLNCLI.1 SQL Native Client (SNAC) on a server which has never had SQL Server 2005 installed, an error similar to the following occurs during processing operations:
Internal error: The operation terminated unsuccessfully.
Internal error: The operation terminated unsuccessfully.
OLE DB error: OLE DB or ODBC error: Class not registered.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'Adventure Works DW', Name of 'Adventure Works DW'.
Errors in the OLAP storage engine: An error occurred while processing the 'Internet_Sales_2001' partition of the 'Internet Sales' measure group for the 'Adventure Works' cube from the Adventure Works DW database.
Server: The operation has been cancelled.
During execution of queries that access data contained in ROLAP storage, an error message similar to the following is returned:
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Adventure Works DW', Name of 'Internet Sales Order Details' was being processed.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'Adventure Works DW', Name of 'Adventure Works DW'.
OLE DB error: OLE DB or ODBC error: Class not registered.
Server: The operation has been cancelled.
Opening the Property Sheet for the Data Source from SQL Server Management Studio (SSMS) or the Data Source Designer in Business Intelligence Development Studio (BIDS) and attempting to edit the connection string returns the following error from Connection Manager:
The specified provider is not supported. Please choose different provider in connection manager
Checking the connection string from either SSMS or BIDS, it is similar to the following:
Provider=SQLNCLI.1;Data Source=MyServer;Persist Security Info=True;User ID=sa;Initial Catalog=AdventureWorksDW
Changing the connection string to use the native OLE DB provider, similar to the following:
Provider=SQLOLEDB.1;Password=Olapolap1;Persist Security Info=True;User ID=sa;Initial Catalog=AdventureWorksDW;Data Source=MyServer
Likewise, modifying the connection string to use the SQL Server 2008 SQL Server SNAC, similar to the following.
Provider=SQLNCLI10.1;Integrated Security="";Persist Security Info=False;User ID=sa;Initial Catalog=AdventureWorksDW;Data Source=MyServer;Initial File Name="";Server SPN=""
The issue occurs because the SQLNCLI.1 provider does not ship with SQL Server 2008 and the provider name for SNAC connections changed in SQL Server 2008. The SNAC Version Provider names for SQL Server 2005 and SQL Server 2008 are:
SQL Server 2005 SQLNCLI.1
SQL Server 2008 SQLNCLI10.1
To workaround this issue, any of the following options can be used:
- Edit the Data Source(s) for the database and switch from the SQLNCLI.1 provider to the SQLOLEDB.1 provider
- Edit the Data Source(s) for the database and switch from the SQLNCLI.1 provider to the SQLNCLI10.1 Native Client provider.
- Download and install the SQL Server 2005 SQL Native Client from the following link: https://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&DisplayLang=en
John Desch
Microsoft SQL Server Escalation Engineer