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 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 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

Need to execute below mentioned three stored procedures to add SQL Azure. Using below these stored procedure I was able to query SQL azure.

EXEC sp_addlinkedserver
@server='PROD',
@srvproduct='',     
@provider='sqlncli',
@datasrc='azureserver.database.windows.net',
@location='',
@provstr='',
@catalog='database name'


EXEC sp_addlinkedsrvlogin 
@rmtsrvname = 'PROD',
@useself = 'false',
@rmtuser = 'Azure login',
@rmtpassword = 'password'

EXEC sp_serveroption 'PROD', 'rpc out', true