SQL SERVER – How to Create Linked Server to SQL Azure Database?

If you take the normal approach to create a linked server to SQL Azure Database, then you would end up in below error message.

Here is the text of the error message

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

Reference to database and/or server name in ‘sqlauthority.sys.sp_tables_rowset2’ is not supported in this version of SQL Server. (Microsoft SQL Server, Error: 40515)

SOLUTION

For me, here is the configuration in Azure

Server name – sqlauthority.database.windows.net
Database name – sqlauthority

Here are the steps I have taken to create linked server. I went to new linked server and on “General”, I used below:

• Linked Server (name): LinkedServerName
• Provider: Microsoft OLE DB Provider for SQL Server
• Product name: (blank)
• Data Source: azure_db_server.database.windows.net
• Provider string: (blank)
• Location: (blank)
• Catalog: database name in Azure

Then on “Security” tab, I used below.

• Be made using this security context
  • Remote login: azure-database-user-name
  • With password: password

Once done, hit OK.

Here is the information via linked server

This is same what we saw when we were directly connected. I hope this blog can help you in fixing linked server errors to SQL Azure Database. Let me know if you have ever faced the same situation before. I would love to know your feedback in the comments.

Reference: Pinal Dave (https://blog.sqlauthority.com)