Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
olimilo
Continued Contributor
Continued Contributor

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

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:

 

olimilo_0-1705990499801.png

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:

 

olimilo_1-1705990563379.png

Is there some way I can use this gateway?

1 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:

5.png

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:

6.png

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

7.png

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.

View solution in original post

6 REPLIES 6
RATG
Regular Visitor
v-jianpeng-msft
Community Support
Community Support

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.

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?

hi, @olimilo 

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

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:

5.png

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:

6.png

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

7.png

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.

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.

Isabel_Zhang_0-1707447495319.png

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.

Isabel_Zhang_2-1707447763160.png

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

 

Thanks.

 

Best Regards,

Zhang Jia

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors