unable to connect to the data source using an encrypted connection

[Binway]

Hi Folks,

I have a SQL Server 2016 database sitting on a VM in Azure with some data that that I am trying to access via Power BI Desktop.  I can connect to the database if I select  "OK" to use unencrypted data when this screen appears using the exact same connection details further down.

But I am not sure of the setting to use if we want to keep the encryption.  From this forum I have seen some information that says to use the ip Address,PortNumber so thought to add TrustServerCertificate=True into the connection string because it is on Azure so I have configured the database so the user logs in using SQL Server Authentication:

With these options selected:

I have included the Trust Server Certivcate option in the PBI Connection:

But the connection gives me an error if I use the semi colon in the connection, or if I use a comma I still get the "connect using unencryption".

Does anyone know how to keep the encryption when connecting in these circumstance?

Thnaks

Binway

[Johag]

This is an old post. But a much simpler solution that worked for me when I had the same issue was to create a environment variable. 

1. Got o Control Panel >  Edit environment variables for your account. Click "New".

2. Add following: 

3. Restart Power BI and conenct to yout SQL Server. Voila. 

[v-yuezhe-msft]

@Binway,

Have you configured SSL encryption for your SQL Server instance? If SSL encription is configured properly in your data source, you can go to File->Options and Settings -> Data Source Settings in Power BI Desktop, select your SQL data source and tick "Encrypt connections".



Regards,
Lydia

[Binway · Accepted solution]

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