Confusing PowerBI message about unencrypted connection

[raandersnw]

I have a user connecting PowerBI to a SQL Server which has an SSL cert and is set to force encryption. He entered the server name without the FQDN and it displayed the message shown below. He claimed that he was able to click OK to the question about continuing using an unencrypted connection without problem. I doubted it, but just tested it myself. I get the same prompt but am able to query the SQL server even after clicking OK.

So, either this is a bug and SQL Server is allowing the connection without encryption (doubful, I checked the status of connections to the server and they were all encrypted) or PowerBI somehow negotiates an encrypted connection in some situations despite this message.

 Can someone with a little deeper knowledge of this shed some light on it? I'd like to be sure that all the connections to our SQL Server are encrypted. Thanks.

[raandersnw]

So nobody has ever encountered this or knows what it means? I have a hard time believing I'm the only one. 

[amitchandak]

@raandersnw , refer if these can help

https://powerbi.microsoft.com/en-ca/blog/ssl-security-error-with-data-source/

https://community.powerbi.com/t5/Power-Query/Certificate-Chain-Not-Trusted-Error-Effecting-PowerBI-O...

https://www.youtube.com/watch?v=9ahvdfp4ydo

[raandersnw]

I appreciate your attempt to help but those pages describe issues with certificate chains and that has nothing to do with my question. 

[satyrin]

Do you have the solution for this? I am facing the same issue and landed up here tryna find a solution...

[raandersnw]

No, unfortunately, I have never found a definitive explanation for what's happening here. My assumption (until someoone can confirm otherwise) must be that SQL Server encryption of traffic is broken and untrustworthy in some fundamental way. At least on the surface, it appears to allow non-encrypted connections even though the option to force encryption is turned on at the server. I don't understand what's going on.

[satyrin]

So..there is no workaround for this issue? Was thinking if DBA could help it out with 🙂

[raandersnw]

If you're seeking a workaround, the way we got rid of this message was the add the FQDN to the connection string. Because there's an SSL cert in place, the connection must include the same domain as the cert. That part was easy to fix and not really my question.

What bothered me was that the user reported he could choose to connect anyway (clicking OK in the dialog) which leads me to believe it's allowing non-secure connections despite the settings on the server supposedly forcing encryption. Just not sure what to make of that.