Can't connect to Datamart over Excel or Azure Data Factory or SSMS

[Anonymous]

Hello Community,

I have created a small testing Datamart based on this brand-new functionality. Works really well in the service/browser. 

I am facing problems when it comes to connecting to it over it's SQL endpoint. I have tried the following:

1. Open in Excel Feature: I get the following error message that a connection was established but an error occured during the pre-login handshake. (provider: SSL Provider, error 0: The wait operation timed out)

2. SSMS v18.7.1

Tried using the connection string from datamart, and get the same error as below. 

I also tried adding a port number to the connection string - both the standard port number 1433 as well as 1978 (as shown in Intro video MS) but neither seemed to help. Although, adding the port number to the connection string did change the error message to The server was not found or was not accessible.

I tried with Azure Data Factory as well, but face the same issues there as well. I have also reviewed this link Datamart-SQL-Server-Connection-String-Details-Port-number-Share but couldn't find a resolution?

I have also confirmed that we are not on Private Endpoint. So I am not sure what else to try at this point?

Any help will be appreciated. Thank you

@marekr 

[waltheed]

Hi, 

I had the same issue.

I got it to work after selecting Azure Active directory ... as the authentication type.

Both in SSMS and Azure Data Studio.

[otravers]

I'm able to connect from Power BI Desktop, Excel Desktop, and the latest Azure Data Studio just using the connection string without specifying the port. I was also able to connect from a Power BI dataflow. If you can't connect from a Power BI dataflow then that would rule out local SSL connectivity issues and would suggest there's something going on with your datamart.

[Anonymous]

Thanks for the pointer @otravers .

I tried connecting with the Dataflow through and I was able to connect without any issues.

It seems that the issue only occurs when I try to connect from any local source such as PBI Desktop, SSMS, or Excel.

I just tried connecting from Power BI Desktop and got the same error message:

[v-easonf-msft]

Hi, @Anonymous 

It may have something to do with the firewall.

You can try turning off the firewall and try again.

Best Regards,
Community Support Team _ Eason