question

HammadRami-4126 avatar image
0 Votes"
HammadRami-4126 asked msantosa-1265 commented

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

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.

azure-data-factory
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.

HimanshuSinha-MSFT avatar image
0 Votes"
HimanshuSinha-MSFT answered HammadRami-4126 commented

Hello @HammadRami-4126,

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


· 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.

This has been done already.

I can preview the data and create a normal copy activity in a pipeline.

it only fails when I use data flow (either using the debugger to preview the data or running the data flow).

2 Votes 2 ·
adambartoszek-3402 avatar image
0 Votes"
adambartoszek-3402 answered

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)

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.

PuranJoshi-0833 avatar image
0 Votes"
PuranJoshi-0833 answered msantosa-1265 commented

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

125398-image.png






image.png (4.6 KiB)
· 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, i also got the same error message but I'm using regular Azure DB (port 1433). When I'm trying to connect using Managed Identity it failed, but if I'm using SQL Authentication it works. Any idea if there's any workaround for non-SQLMI environment? thanks!

0 Votes 0 ·