Refresh PowerPivot data failed on the SQL Server Denali machine

I recently encountered an issue. When I try to refresh data for a PowerPivot Excel Spreadsheet on the SQL Server “Denali” machine, I got the ‘SQLNCLI10’ provider is not registered on the local machine error as below:

Issue:

The PowerPivot Excel Spreadsheet was created on a SQL 2008 R2 machine and was loaded with data from a remote SQL 2008 server by using the SQL Native Client 10 provider originally. When I open it on a SQL “Denali” machine that has no SQL Native Client 10 installed, it failed to refresh the data.  The Denali machine only has SQLNCLI11 provider installed.

Solution:

Convert the connection string to use Microsoft OLE DB provider

• Open Excel and click on the PowerPivot window to open the PowerPivot Datasheet

• Go to the Design tab and click on the Existing Connections button to open the Existing Connections window

• Select the PowerPivot Data Connections that used by the datasheet and click on the Edit button.

• On the Edit Connection window, click on the Advanced button

• Change the Providers from SQL Server Native Client 10.0 to Microsoft OLE DB provider for SQL Server from the Drop down menu. 

• Click the OK button and Save the connection change.

Now I could refresh the PowerPivot data on the machine that has SQL Server Denali installed. The Microsoft OLD DB provider for SQL Server should be the best choice to use in order to share the worksheet in different environments.

Note: I tried to install the SQL Server Native Client 10.0 provider and use it on the SQL Server Denali machine or tried to use SQL Server Native Client 11.0 provider showing as below, both methods did not work.