unable to connect to the data source using an encrypted connection

Thanks Lydia,

I checked with the Azure Admin and they said that SSL was configured properly so we dug a bit deeper and got it to work with an encrytped connection on my Laptops Power BI Desktop connecting to a SQL Server database on a VM in Azure.   There are a few steps to this which I hope I have summarised correctly below and in the correct order.

1. On the VM server in Azure that has your database Create a self signed certificate in powershell
2. Open the personal Certificate store for the computer account and look for the certificate created
3. Right click on it, then select “All tasks”>”Manage private keys”
4. Click on Add, type “NT Service\MSSQLSERVER” (account running sql server), then OK, Select MSSQLServer and click OK again
5. Remove full control and click OK
6. Right click again on the certificate, “All tasks”>”Export”
7. Accept the defaults and click Next 3 times, Click browse, and select a location and file name to export the certificate, click save, and then next, Finish and OK
8. In the Certificate manager window, expand to Trusted Root certification Authorities > Certificates, right click on certificates > all tasks > import
9. Click Next, browse to the file created in step 7, click open, accept defaults and click Next twice, Finish and OK
10. Open SQL Server configuration Manager, go to SQL Server Network Configuration > Protocols for MSSQLSERVER, right click on properties
11. Click on the certificate tab, select the certificate, click on Ok
12. Restart SQL Server (MSSQLSERVER) and SQL Server Agent (MSSQLSERVER) services

This completes the set up on the VM Server in Azure but now need to set up the computers with Power BI Desktop that need access to the database.  I would expect this to be very few as most users would be coming through the Power BI service and the gateway.

1. Copy the certificate file you created above from the VM to your computer.
2. Open your computers certificate manager and import the certificate into the Trusted Root Certification Authority->Certificate folder.
3. In Windows->System32->Drivers->etc folder locate your Hosts file and edit it to map the IP of your VM to the server name.
4. Assuming you need to VPN to your VM Server to create a connection then do this prior to attempting the PBI Desktop connection.
5. Standard process of Get Data -> SQL Server Database
6. The server connection is ServerName,port number - note the comma eg dev_srvr,1433 - server name is in the hosts file you just edited.
7. To log in I created an user on sql server that uses Sql Server authentication - didn't try windows or anything else, use this to log in and you are good to go.

All of this was required to work for us in our environment.

Hope it helps some one else.

Regards

Binway