1

We're using Azure Data Factory and have an Azure hosted Integration Runtime as well as a SQL Managed instance where our databases live.

I'm having trouble getting a data set from our Managed Instance - when I configure the Linked Service, I'm getting a 10060 error when testing the connection.

I don't have the public endpoint open but I don't believe it's an issue. I can connect to SQL managed instance OK from my own PC (using SSMS) if I connect to our virtual lan via VPN, but for some reason my data factory's linked service doesn't see the server.

Any idea where I should start looking?

Matthias Güntert
  • 2,358
  • 11
  • 38
  • 58
R2Bleep2
  • 29
  • 6

1 Answers1

0

From the error code you have provided (10060) I'd guess that's a firewalling issue:

An error has occurred while establishing a connection to the server. When connecting to SQL Server, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (Microsoft SQL Server, Error: 10060)

I have found this within the Azure Data Factory documentation

To access the SQL Managed Instance public endpoint, you can use an Azure Data Factory managed Azure integration runtime. Make sure that you enable the public endpoint and also allow public endpoint traffic on the network security group so that Azure Data Factory can connect to your database.

So it seems you need to open and properly configure your SQL MI's public endpoint. More information on this can be found here: Configure public endpoint in Azure SQL Managed Instance.

If your are willing/able to migrate your database from SQL MI to a SQL DB you can also make use of a managed private endpoint. It would establishe a private link to your database and keep your data on Microsoft's backbone. Unfortunately Azure SQL Managed Instances are currently not supported.

Matthias Güntert
  • 2,358
  • 11
  • 38
  • 58
  • Thanks, but I find it bizarre that I have to expose my SQL Server to the internet to get an Azure service (Datafactory) to communicate with it.... wow! – R2Bleep2 Aug 28 '20 at 12:00
  • I have updated my answer accordingly. – Matthias Güntert Aug 28 '20 at 12:09
  • @R2Bleep2 you can use a self-hosted integration runtime which is running in an Azure VM which has access to your SQL MI. Or if you get into the new preview of ADF managed VNET you can use managed private endpoints and the managed Azure integration runtime. Those are alternatives to opening up your SQL MI firewall. – GregGalloway Aug 30 '20 at 13:47