Unable to connect to the data source using an encrypted connection when creating a dataflow

[olimilo]

I'm trying to use a SQL On-prem gateway when creating a new dataflow query. However, when I try to set up the query, it is asking me to uncheck encrypted connections but there is no option in the setup connection dialog box nor any pop-ups asking me to use unencrypted connections:

We were unable to connect to the data source using an encrypted connection. To use an unencrypted connection, uncheck "Use encrypted connection" and try again.

No option to use unecrypted connections:

Is there some way I can use this gateway?

[RATG]

This article might help:

SQLingRAT: Microsoft Fabric Data Pipeline Connection Error - Created Using Copy Assistant

[v-jianpeng-msft]

Hi, @olimilo 

I'm happy to answer your questions. By default, the Encrypt connection is enabled. We can turn it off manually. The reason for this problem is that 1. encryption is configured on the SQL server side 2. encryption option is not turned off in the power bi data source 3. encrypted credentials option is not turned off in the power BI setting. You can refer to the following links for more info:

https://community.fabric.microsoft.com/t5/Desktop/connect-to-SQL-DB-prompt-quot-The-data-source-you-...

https://community.fabric.microsoft.com/t5/Desktop/unable-to-connect-to-the-data-source-using-an-encr...

https://stackoverflow.com/questions/27813333/power-query-error-in-connecting-to-data-source-using-an...

SSL Security Error with Data Source | Microsoft Power BI Blog | Microsoft Power BI

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[olimilo]

This is actually for a dataflow published on the service, not as a query on the application. Is there a way for me to use an encrypted gateway connection with a dataflow?

[v-jianpeng-msft]

hi, @olimilo 

Do you think our solution is helpful? If you have any comments or suggestions, please feel free to contact us.

[v-jianpeng-msft · Accepted solution]

Hi, @olimilo 

I'm happy to answer your questions. If you want to connect to SQL Server on Power BI Service, we need to provide the relevant credentials to connect to your SQL Server through the gateway. I've tried to connect SQL to create a dataflow. The main process can be seen in the picture below:

The first step is to copy our SQL Server name:

The second step is to enter our server, which is the one that was copied in the first step, and then select an already online gateway and enter the login credentials:

You can then see that you can connect to the database normally:

If you can't connect to SQL Server when you create a dataflow in Power BI Service, I recommend the following steps to troubleshoot:

1. Try connecting to your SQL Server with Power BI Desktop and see if you can read the data. If the encrypted connection is displayed in this step, the problem is on the SQL Server side.
2. If the power bi desktop can connect to the SQL server normally, then check the running status of the gateway and the gateway version, and you can export the logs on the gateway to see the error message.
3. If the gateway is working, check the gateway status and connection credentials in Power BI Service >>Manage connections and gateways>>. Check the gateway status as well as the connection credentials.

If the above suggestions help you find the problem, that's great.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Isabel_Zhang]

Dear Jianpeng,

May I ask one more question based on your solution?

When I select the gateway under "Connection", the gateway I want is avaiable, which is created by my company IT gays.

But if I choose "Create new connection" , the gateway show as below is the one I created by myself before, which need local app to support it to run, but my company don't allow this kind of gateway then it is in use. I can't find the gateway I want as above.

May I know if there is some restriction on the dataflow for gateways?

Thanks.

Best Regards,

Zhang Jia