Connecting to SQL Azure using PowerPivot

Question

Hi,

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.

 

Anyone an idea?

 

Thx

Nico

Answer 1

Two things one is the perhaps directly related to certificate syntax and second

the login name should be entered as login@<Server_name>

---

mysorian

Answer 2

Hi Nico,

Could you try the two options in PowerPivot to connect to SQL Azure: ‘From SQL Server’ and ‘From other Sources – Microsoft SQL Azure’? Do you get the same error message?

 

Also, Click ‘Advanced’ button, set the property ‘TrustServerCertificate’ to Ture and then click Test Connection again.

 

Hope this helps,

Raymond

---

Raymond Li - MSFT

Answer 3

Hey Raymond-Lee

 

Thx.  With adding the TrustServerCertificate to true it works.

 

Grtz,

Nico

Answer 4

Raymond lee,

 

Is there a performance difference between the 'from SQL Server' and 'SQL azure' option?

 

Grtz,

Nico

Answer 5

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:

http://blogs.msdn.com/b/sqlazure/archive/2010/06/23/10029164.aspx

 

Regards,

Raymond

 

---

Raymond Li - MSFT