Lesson Learned #87: Connecting from SQL Server 2008/R2 to Managed Instance using Linked Server.

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

 

 

Hello Team,

 

As you know, the support of SQL Server 2008 and SQL Server 2008 R2 is approaching to the end and some customers are using Linked Server to connect to Azure SQL Managed Instance for sending or retrieving data. We found a particular error message here and we explain how to fix it.

 

In this situation our customer tried to send data from SQL Server 2008 to Azure SQL Managed Instance using linked server but they got the error messages at the moment of the definition, using multiple providers Microsoft OLE DB Provider for SQL Server or SQL Server Native Client 10.0

 

  • Server name not found
  • Server name needs to be the same in the connection string
  • Server name cannot be determined. It must appear as the first segment of the server's dns name). etc..

 

Capture.PNG

 

The workaround used to be able to connect was specifying the connection string of Azure SQL Managed instance in the provider string section of the linked server definition without password parameter.

 

Enjoy!!!

One Reply to “Lesson Learned #87: Connecting from SQL Server 2008/R2 to Managed Instance using Linked Server.”

  1. Not sure I follow. I am facing the same issue. I am trying to connect from SQL 2008 R2 to SQL MI, and I get the error. Any thoughts on how to resolve this?

    TITLE: Microsoft SQL Server Management Studio
    ——————————

    The linked server has been created but failed a connection test. Do you want to keep the linked server?

    ——————————
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ——————————

    Server name cannot be determined. It must appear as the first segment of the server’s dns name (servername.database.windows.net). Some libraries do not send the server name, in which case the server name must be included as part of the user name (username@servername). In addition, if both formats are used, the server names must match. (Microsoft SQL Server, Error: 40531)

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.