error when using Data flow with managed Indentity to connect to SQLMI

Question

I am trying connecting data factory to SQLMI using Managed identity.

I was able to create the linked service. when I test the connection it shows that the connection is successful.
Even when I create a dataset I am able to preview the data.

The problem occur when I use Data Flow/Data Flow debugger the below error message is displayed:
"Please check the linked service configuration is correct, and make sure the SQL database firewall allows the integration runtime to access".

the SQLMI public endpoint is enabled.
when we use a SQL Authentication (as opposed to managed identity) linked service we have no problem and Data Flow works normally.

Answer 1

I got the same below issue when trying dataflow in ADF and trying to connect SQL managed instance.

Cannot connect to SQL database: 'jdbc:sqlserver://inst-sqlmaninst-gabriel.public.7923021abf1b.database.windows.net:3342;database={ITAnalyticsSandbox}', 'User: [UserNameRemoved]'.[SQL Exception]Error Code:0, Error Message: The TCP/IP connection to the host inst-sqlmaninst-gabriel.public.7923021abf1b.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.

Issue:
Port 3342 was opened only for DataFactory and couple of static IPs only

Fix:

Inside Network security group

Earlier 3342 port was only allowed for DataFactory but

I allowed 3342 port for AzureCloud instead of DataFactory and connection worked fine. Still need to figure out minimum service needed for dataflow to allow 3342 but for time being if anyone need quick resolution, this should work for them

Answer 2

I am having the same issue, except I cannot connect using data flows with either a Managed Identity OR a SQL Authentication. The connection tests work great everywhere else, and I can utilize the data sets in normal pipelines. It is only when I try to use the SQL MI dataset in my Data Flow that I can't connect. This is the error I get:

Cannot connect to SQL database: 'jdbc:sqlserver://inst-sqlmaninst-gabriel.public.7923021abf1b.database.windows.net:3342;database={ITAnalyticsSandbox}', 'User: [UserNameRemoved]'.[SQL Exception]Error Code:0, Error Message: The TCP/IP connection to the host inst-sqlmaninst-gabriel.public.7923021abf1b.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)
shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:285)
shaded.msdataflow.com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2478)
shaded.msdataflow.com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:641)
shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2245)
shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1921)
shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1762)
shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1077)
shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:623)
com.microsoft.dataflow.store.mssql.CustomSqlServerDriver$$anonfun$3.apply(CustomSqlServerDriver.scala:48)
com.microsoft.dataflow.store.mssql.CustomSqlServerDriver$$anonfun$3.apply(CustomSqlServerDriver.scala:48)
scala.util.Try$.apply(Try.scala:192)
com.microsoft.dataflow.store.mssql.CustomSqlServerDriver.connect(CustomSqlServerDriver.scala:48)
org.apache.spark.sql.execution.datasources.jdbc.DriverWrapper.connect(DriverWrapper.scala:45)
org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$createConnectionFactory$1.apply(JdbcUtils.scala:64)
org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$createConnectionFactory$1.apply(JdbcUtils.scala:55)
com.microsoft.dataflow.transformers.store.JDBCCharacteristic$class.newConnection(JDBCStore.scala:38)
com.microsoft.dataflow.store.mssql.MSSQLCharacteristic.newConnection(MSSQLStore.scala:78)
com.microsoft.dataflow.store.mssql.MSSQLStore$$anonfun$connect$1.apply$mcV$sp(MSSQLStore.scala:453)
com.microsoft.dataflow.store.mssql.MSSQLStore$$anonfun$connect$1.apply(MSSQLStore.scala:452)
com.microsoft.dataflow.store.mssql.MSSQLStore$$anonfun$connect$1.apply(MSSQLStore.scala:452)
scala.util.Try$.apply(Try.scala:192)
com.microsoft.dataflow.store.mssql.MSSQLStore.connect(MSSQLStore.scala:452)
com.microsoft.dataflow.transformers.StoreDelegate.connect(StoreDefinition.scala:83)
com.microsoft.dataflow.DataflowJobFuture$$anonfun$connect$2.apply(DataflowJobFuture.scala:973)
com.microsoft.dataflow.DataflowJobFuture$$anonfun$connect$2.apply(DataflowJobFuture.scala:970)
scala.concurrent.impl.Future$PromiseCompletingRunnable.liftedTree1$1(Future.scala:24)
scala.concurrent.impl.Future$PromiseCompletingRunnable.run(Future.scala:24)
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
java.lang.Thread.run(Thread.java:748)

Answer 3

Hello @Hammad, Rami ,

Just to know if you have gone through the below steso called out here
Let me know if this helps .

*Managed identities for Azure resources authentication
A data factory can be associated with a managed identity for Azure resources that represents the specific data factory. You can use this managed identity for Azure SQL Database authentication. The designated factory can access and copy data from or to your database by using this identity.
To use managed identity authentication, follow these steps.
Provision an Azure Active Directory administrator for your server on the Azure portal if you haven't already done so. The Azure AD administrator can be an Azure AD user or an Azure AD group. If you grant the group with managed identity an admin role, skip steps 3 and 4. The administrator has full access to the database.
Create contained database users for the Azure Data Factory managed identity. Connect to the database from or to which you want to copy data by using tools like SQL Server Management Studio, with an Azure AD identity that has at least ALTER ANY USER permission. Run the following T-SQL:
SQL
Copy
CREATE USER [your Data Factory name] FROM EXTERNAL PROVIDER;
Grant the Data Factory managed identity needed permissions as you normally do for SQL users and others. Run the following code. For more options, see this document.
SQL
Copy
ALTER ROLE [role name] ADD MEMBER [your Data Factory name];
Configure an Azure SQL Database linked service in Azure Data Factory.

Thanks Himanshu
Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members