Is it possible to create a Linked Server from Azure Managed Instance to our On-Premise SQL Server?

Cooling, Steve 20

Is it possible to create a Linked Server from Azure Managed Instance to our On-Premise SQL Server? I've successfully created a linked server from our On-Premise SQL Server to our Azure Managed Instance but have now been asked to do it the other way. Can it be done?

1 answer

GeethaThatipatri-MSFT 29,502 Reputation points Microsoft Employee

2023-03-06T18:20:29.92+00:00

Hi @Cooling, Steve Welcome to the Microsoft Q&A platform, thanks for posting the question

Yes, it is possible to create a Linked Server from Azure Managed Instance you need to ensure that network paths are open to your on-prem server and make sure that outbound NSG rules and DNS resolution are set correctly on the MI subnet, also any firewalls are removed from on-prem server that would block traffic.

It also depends on how you set up your on-prem network, you can test DNS resolution and ping from SQL Agent to make sure that connectivity (networking) is OK, then set up a linked server.

this blog can be useful for testing connectivity

Please let me know if you find any issues.

Regards

Geetha
Please sign in to rate this answer.
Cooling, Steve 20 Reputation points

2023-03-07T09:06:14.54+00:00

Hi @GeethaThatipatri-MSFT thank you for the information. I'm not sure if I'm doing something wrong but when I run the script to test connectivity on port 443 I get the below error message. Any ideas please?

Msg 40515, Level 15, State 1, Line 16

Reference to database and/or server name in 'msdb.dbo.sysjobs_view' is not supported in this version of SQL Server.

GeethaThatipatri-MSFT 29,502 Reputation points Microsoft Employee

2023-03-10T14:21:56.46+00:00

Hi @Cooling, Steve If you are connecting to Azure SQL Managed Instance you need port 1433 and you need to target the IP address, to troubleshoot more I would recommend raising the support case.

Regards

Geetha

GeethaThatipatri-MSFT 29,502 Reputation points Microsoft Employee

2023-03-13T14:55:56.89+00:00

@Cooling, Steve We haven't heard back from you. Just wanted to check if you had the chance to check my response

Cooling, Steve 20 Reputation points

2023-03-27T16:04:27.3+00:00

Hi @GeethaThatipatri-MSFT , apologies, I've been off on leave for a fortnight. Since posting this question I've discovered that we don't have an Azure SQL managed instance but instead we have the older version which I'm told is just a basic Azure SQL instance and I've also been told that creating a linked server from this instance to our on-prem instance is not possible. We have now deployed an always on SQL Cluster in Azure and will be working with that going forward. Thanks for all your help.

GeethaThatipatri-MSFT 29,502 Reputation points Microsoft Employee

2023-03-27T16:13:28.5733333+00:00

@Cooling, Steve Great to know that you figured it out, appreciate your response and providing the solution here with the community

Thank you for visiting the Microsoft QA forums! Have a great day!

Regards

Geetha
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Is it possible to create a Linked Server from Azure Managed Instance to our On-Premise SQL Server?

1 answer

Your answer