Cannot get Linked Servers to work in SQL Azure

Question

We are using a trial version of Azure. We are trying to perform cross-server queries from our SQL 2012 in-house.

We seem to have our local 2012 linked with Azure. When I go into Server Object -> Linked Servers in management studio, I see our Azure database.

But if I try to open the catalog and tables, I get an error message saying

Reference to the database and/or server name in 'Perseus.sys.sp_tables_rowset2'  is not supported in this version of SQL Server

** Perseus is the name of our catalog in Azure SQL.

Running a query from a local connection :

SELECT *  FROM [azureDBServer].[Perseus].[dbo].[accounts]

result is:

OLE DB provider "SQLNCLI11" for linked server "azureDBServer" returned message 

"Unspecified error". Msg 40515, Level 16, State 2, Line 1 Reference to database and/or

 server name in 'Perseus.sys.sp_tables_info_90_rowset' is not supported in this version of

 SQL Server. 

This same in house SQL 2012 Server is able to connect to our in-house 2008 by cross server queries and by viewing its structure through Linked Servers.

I know from this article Azure supports Linked Servers.

So I'm lost about what is wrong. Our Admin thinks it may be that we have a Web-Sql account vs a business SQL account. This Azure Web vs Business SQL outdated Stack link implies that SQL version is NOT the problem, but pre-dates when Azure offered Linked Servers.

So, I'm trying to understand if

a) we didn't set up something right to provide SQL Linking?

b) we are limited by trial?

c) are we limited by Web SQL version?

d) anything else? 

Answer 1

We are not given the option to set default database while adding a linked server from SQL Management, so do it in this way:

EXEC sp_addlinkedserver

@server='name for referring locally', -- here you can specify the name of the linked server

@srvproduct='',     

@provider='sqlncli', -- using SQL Server native client

@datasrc='AzureMachineName.database.windows.net',   -- add here your server name

@location='',

@provstr='',

@catalog='yourdatabasename'