Lesson Learned #222:Hands-On-Labs: Configure/Troubleshoot Linked Server Azure SQL Managed Instance

We used to have cases where our customers reported configuration and connectivity issue at the moment of the creation of Linked Server in Azure SQL Managed Instance. In this video below we going to provide us some insights about how to configure and troubleshoot Linked Server in Azure SQL Managed Instance.

Agenda:

• Define Linked server Managed Instance
• Connecting to?
  • Azure SQL Database, Azure SQL Managed Instance, Azure SQL Virtual Machine, Azure Synapse and SQL Server On-Premises.
• How to Connect?
  • Using Public Endpoint
    • Configure NSG vs Redirection vs Proxy
    • Why is important DNS Resolution
    • Which will be the IP that is connecting to?
  • Using Private Endpoint
    • Configure NSG vs Redirection vs Proxy
    • Why is important DNS resolution
    • Which will be the IP that is connecting to?
  • Main issues configuring a Linked Server.
  • References:
    • How-to test TCP connectivity from a SQL Managed Instance (microsoft.com)
    • Name resolution for resources in Azure virtual networks | Microsoft Docs
    • Synchronize virtual network DNS servers setting on SQL Managed Instance virtual cluster - Azure SQL Managed Instance | Microsoft Docs
  • Errors found in this video:
    • Named Pipes Provider: Could not open a connection to SQL Server [53] - OLE DB Provider "MSOLEDBSQL" for linked server "xyz" returned message "Login timeout expired"
    • TCP Provider: No such host is known  - OLE DB Provider "MSOLEDBSQL" for linked server "xyz" returned message "Login timeout expired"
    • Error 11001
    • An instance specific error ocurred while establishing a connection to SQL Server. Connection was denied since Deny Public Network Access is set to Yes - Error 47073.
    • TCP Provider: a connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed becvase cvonnected host has failed to respond - Error: 10060.

Enjoy!