The sqlncli10 provider is not registered on the local machine. Failed to connect to the server.

When you configure the data source of your Office 2010 Excel Power Pivot sheet, you can get the message:
Failed to connect to the server. Reason: The sqlncli10 provider is not registered on the local machine.

This can happen if you want to connect to a SQL 2008 (R2) server. On the client you need to have installed the Microsoft SQL Server Native Client (SQL Server Native Client). This is a single dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC driver.

Solution: Install the sqlncli10 provider on your machine.

The sqlncli10 provider for SQL 2008 R2 can be downloaded from here.

X86 package.
X64 package
IA64 package.


In case you have a SQL 2012 server and have received a Power Pivot sheet which want to use the SQLNCLI10 provider, you can change the data provider to use to data provider for SQL 2012: SQLNCLI11

1. Press the Existing connection button in the Power Pivot Window
2. Select the PowerPivot Data Connection
3. Press Edit
4. Specify the SQL server name
5. Specify the Database name
6. Press the advanced
7. Select Provider:  SQL Server Native Client 11.0  

In case you can't selected the SQL Server Native Client 11.0. The provider is not installed on your client. The SQLNCLI11 provider for SQL 2012 can be downloaded from here:
X86 package
X64 package

After installation it should be possible to configure your datasource, to be able to update your Excel Power Pivot sheet with all data.