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

Cooling, Steve 0

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 27,017 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 0 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 27,017 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 27,017 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 0 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 27,017 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