question

HarshalAwasthi-8978 avatar image
0 Votes"
HarshalAwasthi-8978 asked KranthiPakala-MSFT commented

Azure Data Flow Unable to connect to Managed Instance in Private Cloud

Hello Experts,

I have Managed Instance under Express Route peering Subnet, Opened Public endpoint, and provisioned port 3342 to allow public endpoint access, Linked Services are working as expected and copy activity is working great, however, when using dataflow sink is throwing following error

StatusCode":"DFExecutorUserError","Message":"Job failed due to reason: at Sink 'Group': java.lang.RuntimeException: Cannot connect to SQL database: 'jdbc:sqlserver://<servername>:3342;database=<dbname>', 'User: <username>'.[SQL Exception]Error Code:0, Error Message: The TCP/IP connection to the host <servername>.database.windows.net, port 3342 has failed. Error: \"Connection timed out: no further information.. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.\"., error stack:shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:228)\nshaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:285)\nshaded.msdataflow.com.microsoft.sq

I'm using Memory Optimized Azure Hosted IR and As far as I understand the Spark cluster is unable to see the database server/database. As I have mentioned above linked service test connection is green.

Please provide your expert advice to overcome this challenge.

Thanks in advance

azure-data-factory
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Open the data flow in the designer and click "Test Connection" inside the data flow in the Source or Sink, wherever you are using the SQL MI dataset, and click the "Test Connection" button there. That will test from the Spark cluster whereas testing from the Linked Service tests from the Copy infrastructure.

0 Votes 0 ·

Thanks, Mark for your suggestion, still getting the same error. Please suggest how I can make this work?

Cannot connect to SQL database: 'jdbc:sqlserver://<SQL MI Database Server Name>.database.windows.net:3342;database=<UserName>', 'User: Username'.[SQL Exception]Error Code:0, Error Message: The TCP/IP connection to the host <SQL MI Database Server Name>.database.windows.net, port 3342 has failed. Error: "Connection timed out: no further information.. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall."., error stack:shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:228)





0 Votes 0 ·

1 Answer

HarshalAwasthi-8978 avatar image
3 Votes"
HarshalAwasthi-8978 answered KranthiPakala-MSFT commented

Got the solution, maybe someone else will be benefited

Issue:
How to open and connect to the public endpoint of a SQL MI and connect to a data factory for copy/dataflow activity.

Cause:
Linked Service network connection uses "Data Factory IP's/service tag" while Data Flow uses spark cluster in the backend and they have a dynamic IP which is not part of Data Factory Service Tag.

Suggested solutions:
Due to this limitation, there are 2 options to use SQL MI in Data Flow:
- Open Port 3342 to the Source Internet and Destination Virtual Network in MI Subnet NSG
- Stage the Data in Data Flow to Azure Blob/Table Storage Copy activity to another source (SQL or Blob) and then continue with Data Flow

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Hi @HarshalAwasthi-8978, Glad to know that you were able to figure out a solution and thanks much for sharing it here with community and helping them. In addition to above info please note that Azure SQL Managed Instance connector in Mapping Data Flow is currently available as public preview. You can connect to SQL Managed Instance public endpoint but not private endpoint yet. SQL MI is in public preview because we do not yet have the ability to leverage private endpoints in Azure SQL MI, which is the mechanism used by the Azure Integration Runtime to provide vnet security for your data sources in ADF. Therefore only supporting public endpoints with SQL MI in data flows until that feature is released in Azure SQL MI. Once that happens, we can support private Azure SQL MI connections and support the connector as Generally Available.

Please feel free to accept your answer as it would help other to find this thread helpful.

Thank you

0 Votes 0 ·