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: http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&DisplayLang=en
Microsoft SQL Server Escalation Engineer