Cannot load data from OnPrem to SQL Managed Instance

Question

Cannot load data from OnPrem to SQL Managed Instance

Onkar Belgamwar 1

Hi Team,

Our requirement is to transfer data from on-prem SQL Server to Azure SQL Managed instance, using ADF.

We have 2 Azure SQL managed instance resources. First one is created around 4 months back (MI1), and 2nd one is created in Last Week as a Development Environment (MI2). Linked Services of all these 3 servers are successfully connected.

When we are transferring data from On-Prem to MI2, its failing. We can preview data from Source (On-Prem) as well as Sink (MI2). But when we debug the copy activity, it fails with below error.

But when I transfer data from On-Prem to MI1, I am able to do so. I tried various ways as below.

On-Prem to MI1 - Succeed
On-Prem to MI2 - Failed
On-Prem to Azure SQL DB - Succeed
Azure SQL DB to MI2 - Succeed
On-Prem to MI2 with Staging Enabled in Copy Activity - Succeed
MI2 to MI2 - Succeed
MI1 to MI2 - Succeed

Please note, we compared both the MIs, and it seems to be same.

Kindly advise if we can try any further with any different approach.

Thanks in Advance.
Onkar

0 comments

1 answer

Your answer

Answer 1

HimanshuSinha 19,637 Microsoft Employee Moderator

Hello @Onkar Belgamwar ,
Thanks for the question and using MS Q&A platform.
As we understand the ask here is how to get the other managed instance to work . , please do let us know if its not accurate.
I really appreciate the details ask , it always helps . Since you hmentioned

On-Prem to MI2 with Staging Enabled in Copy Activity - Succeed| and
On-Prem to MI2 - Failed

Also the last line of the error points to be issues on the target side .

I am inclined to to beleive that the managed instances are on VNET and that M11 & M12 are not on the same VNET , Can you please check and let us know ?
if thats the case M11 does have a firewall setting with posrt 1433 open .

Please do let me if you have any queries.
Thanks
Himanshu

Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
Want a reminder to come back and check responses? Here is how to subscribe to a notification
- If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Onkar Belgamwar 1 Reputation point

2022-04-12T03:33:17.47+00:00

Thanks Himanshu for the Answer.

Yes, you are correct, my question is how to get other MI to work.
Both are at different V-Net.

Regarding your ans - "if thats the case M11 does have a firewall setting with posrt 1433 open ."

MI1 - this is working. So do you mean we have to check this setting for MI2.? If yes, we already have below ports open. Please check.

OutBound Rule -

InBound Rule -

I have compared both VNet (MI1 and MI2). For both VNet we have exactly identical rules set.

Please let me know if I have to check anything further.

Thanks & Regards,
Onkar
HimanshuSinha 19,637 Reputation points Microsoft Employee Moderator

2022-04-12T19:54:12.8+00:00

Hello @Onkar Belgamwar ,
I think you need tyo work with MS support as they can have a live session and looks into all the settings , if you have a support plan you may file a support ticket, else could you please send an email to azcommunity@microsoft.com with the below details, so that we can create a one-time-free support ticket for you to work closely on this matter.
Subscription ID:
Subject : Attn Himanshu
Please let me know once you have done the same.
Thanks
Himanshu
HimanshuSinha 19,637 Reputation points Microsoft Employee Moderator

2022-04-20T18:59:39.89+00:00

Hello @Onkar Belgamwar ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .In case if you have any resolution please do share that same with the community as it can be helpful to others . Otherwise, will respond back with the more details and we will try to help .
Thanks
Himanshu

Share via

Cannot load data from OnPrem to SQL Managed Instance

1 answer

Your answer