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.

SQL SERVER - How to Create Linked Server to SQL Azure Database? azure-ls-01

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

SQL SERVER - How to Create Linked Server to SQL Azure Database? azure-ls-02

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

SQL SERVER - How to Create Linked Server to SQL Azure Database? azure-ls-03

Then on “Security” tab, I used below.

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

SQL SERVER - How to Create Linked Server to SQL Azure Database? azure-ls-04

Once done, hit OK.

Here is the information via linked server

SQL SERVER - How to Create Linked Server to SQL Azure Database? azure-ls-05

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)

, , ,
Previous Post
SQL SERVER – Remove Duplicate Chars From String – Part 2
Next Post
SQL SERVER – Convert Decimal to Time Format in String

Related Posts

6 Comments. Leave new

Leave a Reply

Menu