Issue with Creating Linked Server from Azure SQL Managed Instance to On-Premises SQL Server

GP 16

Hi,

Interestingly, I was able to create a linked server in the reverse direction—from the on-premises SQL Server to my Azure SQL MI—using a remote login and password for authentication.

However, when I attempt to establish the link from Azure SQL MI to the on-premises SQL Server, it consistently fails.

Additional Details:

I have an Azure VM within the same VNET as the SQL MI. From this VM, I successfully tested connectivity to the on-premises SQL Server using both ping and telnet.

Despite verifying inbound and outbound rules for the SQL MI in Azure (ensuring the necessary ports and IPs are open), the connection fails with the following errors:

Error 1:

Data Source: tcp:DB1.xx,1433

Provider: MS OLEDB Driver for SQL Server

TCP Provider: No such host is known.

OLE DB provider "MSOLEDBSQL" for linked server "MYLINKED" returned message "Login timeout expired."

OLE DB provider "MSOLEDBSQL" for linked server "MYLINKED" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server."

Error 2:

Data Source: DB1.xx

Provider: MS OLEDB Driver for SQL Server

Named Pipes Provider: Could not open a connection to SQL Server [53].

OLE DB provider "MSOLEDBSQL" for linked server "MYLINKED" returned message "Login timeout expired."

OLE DB provider "MSOLEDBSQL" for linked server "MYLINKED" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible."

Error 3:

Data Source: Directly specifying the linked server name

Named Pipes Provider: Could not open a connection to SQL Server [53].

OLE DB provider "MSOLEDBSQL" for linked server "DB1.xx" returned message "Login timeout expired."

OLE DB provider "MSOLEDBSQL" for linked server "DB1.xx" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible."

Given these errors, I am wondering if this configuration is truly possible or supported.

Any insights, suggestions, or guidance would be greatly appreciated.

Thanks!

GP 16 Reputation points

2025-01-10T15:58:24.1866667+00:00
Hi @Vijayalaxmi Kattimani

Thank you for your response.

Yesterday, I set up an Azure VM on the same network as our managed instance. When I tested connectivity from this Windows VM to the on-prem SQL Server using Telnet and Ping, they worked without any issues.

I also referred to the script shared by Niko in this blog post. I have attached the results for your review—it seems to indicate a failure.

As a result, the linked server connection to our on-prem SQL Server is failing.

Here's what we have verified so far:

The firewall team confirmed that all necessary ports are open for the connection between Azure SQL MI and the on-prem SQL Server.

The cloud team confirmed that the NSG has the required inbound and outbound rules set to access the on-prem SQL Server.

Given these validations, could you suggest any additional checks or configurations we might need to address this issue?

Thank you for your assistance!
Vijayalaxmi Kattimani 905 Reputation points Microsoft Vendor

2025-01-13T09:16:07.0033333+00:00
Hi @GP,

It sounds like you have covered the networking basics thoroughly. To further investigate and resolve the linked server connection issue, please follow the below mentioned troubleshooting steps.

Ensure that the authentication type configured for the linked server is supported and matches the on-prem SQL Server's configuration. For example, if you are using SQL authentication, confirm that the credentials are correct and have necessary permissions.

Test name resolution for the on-prem SQL Server FQDN from the Azure SQL MI. Sometimes, custom DNS configurations might not resolve the on-prem hostname properly.

Run a basic query using the OPENQUERY function to test the linked server directly. This can provide more detailed error messages.

Check whether a private endpoint or service endpoint has been set up incorrectly, interfering with Azure SQL MI's connectivity to on-premises resources.

Ensure that all required protocols (e.g., TCP/IP for SQL Server) are enabled on the on-prem SQL Server.

Use SQL Server extended events or the error logs on both SQL MI and the on-prem server to identify if the issue lies in handshake failures, routing, or incorrect configurations.

For Windows authentication, validate that Service Principal Names (SPNs) are correctly registered and Kerberos is functioning properly across your environment.

Verify whether a proxy or network gateway might be limiting traffic between Azure SQL MI and the on-prem SQL Server.

If the above steps do not resolve the issue, If you have a support plan could you please file a support ticket for deeper investigation. In case if you don't have a support plan please let us know here.

Thank you,
Vijayalaxmi Kattimani 905 Reputation points Microsoft Vendor

2025-01-15T08:31:31.5866667+00:00

Hi @GP,

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 with more details and we will try to help.
GP 16 Reputation points

2025-01-15T15:20:17.9666667+00:00

Hi @Vijayalaxmi Kattimani , Thank you for your prompt responses on this. Apologies for the delay, I was out sick for a couple of days and just returned to work today. I’ll review your comments, and as soon as I have a resolution or additional questions, I’ll follow up here.I’ll also need to check on the support plan we have regarding this. Thank you so much for your assistance. I’ll keep you updated.
GP 16 Reputation points

2025-01-15T16:53:33.89+00:00
Hi @Vijayalaxmi Kattimani , Please see my responses below for your suggestions.

Could you check on the highlighted Bold item below (item 2) ?

There are few things that I have asked our cloud/ n/w team to check and once I hear back from them I will update here. thanks

Thanks

Ensure that the authentication type configured for the linked server is supported and matches the on-prem SQL Server's configuration. For example, if you are using SQL authentication, confirm that the credentials are correct and have necessary permissions. => Verified and ok.

Test name resolution for the on-prem SQL Server FQDN from the Azure SQL MI. Sometimes, custom DNS configurations might not resolve the on-prem hostname properly. => Do you have a script to check this from Azure MI or is it from the link : How-to test TCP connectivity from a SQL Managed Instance ?

Run a basic query using the OPENQUERY function to test the linked server directly. This can provide more detailed error messages = > Linked server creation is failing so not possible to run OPENQUERY at the moment

Check whether a private endpoint or service endpoint has been set up incorrectly, interfering with Azure SQL MI's connectivity to on-premises resources => Our cloud team will check on this

Ensure that all required protocols (e.g., TCP/IP for SQL Server) are enabled on the on-prem SQL Server => Yes

Use SQL Server extended events or the error logs on both SQL MI and the on-prem server to identify if the issue lies in handshake failures, routing, or incorrect configurations => Extended events not tried yet, but I don’t see any kind of failure messages on the SQL Logs of both SQL MI and on-prem SQL Server. So seems like not even reaching the on-prem server.

For Windows authentication, validate that Service Principal Names (SPNs) are correctly registered and Kerberos is functioning properly across your environment => Not Windows using SQL Auth.

Verify whether a proxy or network gateway might be limiting traffic between Azure SQL MI and the on-prem SQL Server => I have asked cloud team to check. Will get back on this.
Vijayalaxmi Kattimani 905 Reputation points Microsoft Vendor

2025-01-16T16:44:28.83+00:00

Hi @GP,

Thank you for sharing the update on our suggestions.

Regarding your second point, could you please refer to this link https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/resolve-private-domain-names?view=azuresql&tabs=azure-portal

I hope this information helps. Please do let us know if you have any further queries.
GP 16 Reputation points

2025-01-16T17:04:08.3133333+00:00

Hi @Vijayalaxmi Kattimani , thanks you for sharing this, I will check it out, and keep you posted. thanks
Vijayalaxmi Kattimani 905 Reputation points Microsoft Vendor

2025-01-17T16:50:34.1333333+00:00

Hi @GP,

Have you been able to resolve your issue, and have you received any updates from the cloud team? Please update us if your issue has been resolved.

If you have any further questions or require additional assistance, please let us know.

2 answers

Vijayalaxmi Kattimani 905 Reputation points Microsoft Vendor

2025-01-10T15:49:46.0666667+00:00

Hi @GP,

Welcome to the Microsoft Q&A Platform! Thank you for asking your question here.

We would like to inform you that, it is possible to create a Linked Server from an Azure SQL Managed Instance to an on-premises server. To achieve this, you must ensure that the necessary network paths are open to the on-premises server and verify that outbound NSG rules and DNS resolution are correctly configured for the Managed Instance subnet. Additionally, ensure that any firewalls on the on-premises server are configured to allow traffic from the Managed Instance.

The feasibility also depends on the configuration of your on-premises network. It is recommended to test DNS resolution and connectivity (e.g., using ping) from the SQL Agent to confirm that networking is functioning as expected. Once connectivity is verified, you can proceed with setting up the linked server.

Please refer to the below mentioned links for more information.

https://techcommunity.microsoft.com/blog/azuresqlblog/how-to-test-tcp-connectivity-from-a-sql-managed-instance/3058458

https://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine?view=sql-server-ver16&source=recommendations

I hope this information helps. Please do let us know if you have any further queries.

If the answer is helpful, please click "Accept Answer" and "Upvote it".
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Javier Villegas 900 Reputation points MVP

2025-01-11T23:59:48.3366667+00:00

Hello

have you tried by creating an Alias in SQL Server Configuration Manager for MYLINKED?

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/create-or-delete-a-server-alias-for-use-by-a-client?view=sql-server-ver16

Regards

Javier
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

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

Share via

Issue with Creating Linked Server from Azure SQL Managed Instance to On-Premises SQL Server

2 answers

Your answer