Cannot connect to SQL DB w/PBI Desktop - Can with SSMS and ADS

[ptimmerm]

Hi all. I must be making a simple mistake, but here's my situation.

I spun up a new Azure SQL DB. It allows for Entra Authentication and SQL Server Authentication. Firewall rule has been setup to allow for connection from my local IP.

From SSMS and ADS I can connect without issue. In SSMS, I have successfully connected with the Entra Admin account (using Microsoft Entra MFA authentication), with a separate Entra account I added (using Microsoft Entra Integrated authentication) and with a SQL Server authentication account I have created. Everything works as expected.

When I launch Power BI Desktop (latest version) and attempt to connect to this Azure SQL DB, using the same types of authentication that work for SSMS, I get this:

Power BI Desktop is on the same machine as SSMS and ADS, so this is not a server firewall issue. I see no scenario where it is a network latency issue. 

I should add that I see this same error when I use the .pbids file downloaded right from the server in Azure.

What am I missing? Why is Power BI Desktop unable to make this connection? It feels "encryption-y" but I cannot figure out where.

Thanks in advance!

[ptimmerm]

I am finding all of these other ways to connect to Azure SQL DB, but I cannot make the connection through the SQL Server connector in Power BI Desktop. Very odd and very frustrating.

[FarhanJeelani]

Hi @ptimmerm ,

The error message suggests an issue with the pre-login handshake, often related to encryption, TLS settings, or a timeout due to network settings. Since SSMS and Azure Data Studio (ADS) can connect without issue, but Power BI Desktop cannot, here are some possible solutions:

1. Check Power BI Desktop Version
Ensure that you're using the latest version of Power BI Desktop. Older versions may not support certain authentication methods or TLS settings.

2. Use the Correct Connection String
When connecting in Power BI, try the fully qualified server name (<yourserver>.database.windows.net).
If using Microsoft Entra Authentication, ensure you select the correct method (Microsoft Entra ID - MFA or Microsoft Entra ID - Integrated).
For SQL Server Authentication, ensure the credentials match exactly as used in SSMS.

3. Enable Encryption & Trust Server Certificate
In Power BI Desktop, go to Data Source Settings → Edit Permissions → Encryption Level and try setting it to "Encrypt connections" or "Trust server certificate".
You can also try adding TrustServerCertificate=True to the connection settings manually.

4. Check TLS Settings
Since SSMS and ADS use different network libraries than Power BI, try ensuring your machine supports the required TLS versions:

Open regedit.exe and navigate to:
sql

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\

Ensure TLS 1.2 is enabled (if necessary, update your Windows TLS settings).

5. Run Power BI as Administrator
Some network or encryption settings might require elevated permissions. Try running Power BI Desktop as Administrator.

6. Try a Different Connection Mode
If using Import Mode, switch to DirectQuery or vice versa and check if the issue persists.

7. Clear Power BI Cache & Reset Data Source Permissions
Go to File → Options and settings → Options → Data Load.
Click Clear Cache.
Then go to Data Source Settings, remove the stored credentials, and re-enter them.

8. Check Firewall & Proxy Settings
Ensure your machine isn't blocking Power BI's network requests.
If behind a corporate proxy, you might need to configure Power BI Desktop to use it correctly.

Please mark this post as solution if it helps you. Appreciate Kudos.

[ptimmerm]

Hello @FarhanJeelani 

Thank you so much for the detailed response. I tried a number of your suggestions and have had no luck.

I am confident there is no traffic being blocked. I think I might have a driver issue of some sort. If I create and ODBC DSN, using the most current ODBC driver, I get all of the Entra ID options for authentication. I can reference the ODBC as the data source in Power BI Desktop and utilize the connection. This is not ideal but is a new data point.

What I notice is that when I go to setup just the SQL Server connection on Power BI Desktop, the connection configuration in the app does not show any of the Entra options or being able to set the encryption connection to Strict, like I do in SSMS, and like what works when I set up the ODBC. It feels like something is missing. I am running the most current version of the app, February 2025. When I click on SQL Server for the data source, I see the following. Is this what you see/what I am supposed to see?

Nowhere do I have the Entra related options I see in other tools. 

Thoughts?

[tharunkumarRTK]

@ptimmerm 

If you want to change the encryption option from power bi desktop, open datasource settings, (it appears under "Transform data" drop down. Click on global datasources (Considering you have have not establisshed the connection successfully) and then find your sql server and then right click on it and then in the menu that appears click on edit 

There you will find a check box, Encrypted, un check that box and see if that solves the problem

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[ptimmerm]

Thanks for the reply. Unfortunately that option appears to just be binary, "encrypt or not". It is not the same level of configuration available in, for example, the ODBC driver setup I referenced.

[tharunkumarRTK]

@ptimmerm 

have you tried unchecking the Encrypted? if yes, then were you able to connect to you sql server?

[ptimmerm]

I did try that and still no luck connecting.

[tharunkumarRTK]

Ok, May I know where this SQL server is hosted?
what is the port number that you are using to connect to it from sql server or ADS?

if it is not 1433 then can you try appending the port number to the hostname?
like <Hostname::1433> 

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[ptimmerm]

It is in Azure North Central US. It is listening on 1433.

There has to be something internal to my work network blocking it, but I have no idea what that would be since I can hit it via SSMS and ADS. Might be a GPO thing. Does not seem lie a firewall thing anymore.

I can hit it just fine from Power BI Desktop installed on my personal laptop.

[tharunkumarRTK]

@ptimmerm 

Hmm. May you can use tools like wireshark and see the network trace?

create one trace while you are connecting from SSMS and one another trace when you are trying to connect from power bi desktop and see if that gives any information.

Also, If you are using your organiztion network then you can ask your network team to unblock port 1433 for your ID. As far as I know some organizations might have user/region/system specific network rules

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[ptimmerm · Accepted solution]

Just wanted to say thank you to everyone for their assistance. We have resolved the issue. Turns out it was a firewall thing, but not a straight blocking of 1433 outbound. There were some AppIDs that were inconsistent/misconfigured.

[ptimmerm]

Appreciate the reply. I do not think this is a port issue as I am able to connect with SSMS and ADS, both of which would also be going out on 1433.

I tried in Excel and ran into the same issue. My current hypothesis is that there is something in our M365 tenant that is preventing our M365 apps from making data connections to Azure.

[ptimmy]

Hi all. I must be making a simple mistake, but here's my situation.

I spun up a new Azure SQL DB. It allows for Entra Authentication and SQL Server Authentication. Firewall rule has been setup to allow for connection from my local IP.

From SSMS and ADS I can connect without issue. In SSMS, I have successfully connected with the Entra Admin account (using Microsoft Entra MFA authentication), with a separate Entra account I added (using Microsoft Entra Integrated authentication) and with a SQL Server authentication account I have created. Everything works as expected.

When I launch Power BI Desktop (latest version) and attempt to connect to this Azure SQL DB, using the same types of authentication that work for SSMS, I get this:

Power BI Desktop is on the same machine as SSMS and ADS, so this is not a server firewall issue. I see no scenario where it is a network latency issue. 

I should add that I see this same error when I use the .pbids file downloaded right from the server in Azure.

What am I missing? Why is Power BI Desktop unable to make this connection? It feels "encryption-y" but I cannot figure out where.

Thanks in advance!