When I tried to connect from PowerPivot to Azure I got this message: "A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate's CN name does
not match the passed value.)"
Before connecting from PowerPivot, I already connected from SQL Server management studio and SSIS without any problems.
By default ‘from Microsoft SQL Azure’ use .net provider, and ‘From SQL Server’ use
SQL Server Native Client 10.0.
But you can change the provider you want to use. Regarding to performance, it’s hard to say, they are both very mature and have been heavily invested in over the years. For most cases, variance of how you implement a solution will be the dominant factor
for perf and not the raw performance of the drivers. Personally, I don’t think you can tell the differences of performance between the two providers.
Note: we recommend that using .Net Data Source for SQL Server for PowerPivot, more information, you can refer to: