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

Answer 2

While adding linked server from SQL Management, you are not given option to set default database. So use something like below

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' 

I figured this works.

Answer 3

Did you actually setup connection to perseus database? By looking at the error message your are sending a query with 3 part or 4 part name to Azure which doesn't work as is in Azure. Please check your query and set it to use 2 part name and only three part name if it is connecting to the same database

Answer 4

This works for me:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mypassword';

CREATE DATABASE SCOPED CREDENTIAL MySecurity 
WITH IDENTITY = 'mylogin',
SECRET = 'mypassword';

CREATE EXTERNAL DATA SOURCE MyDbAccess
WITH (
    TYPE=RDBMS,
    LOCATION='server name',
    DATABASE_NAME='db_name',
    CREDENTIAL= MySecurity);

CREATE EXTERNAL TABLE MyExtTable (
    [Id] [int]  NOT NULL,
    [Name] [varchar(20)] NULL)
WITH
(DATA_SOURCE = MyDbAccess);

After that you can just use it:

SELECT * FROM MyExtTable

Answer 5

Just throwing it out there and I know this is an old post but I still stumbled across it and found it helpful so maybe it can still help others... Anyway, I got this error and my issue ended up being that I can't type. For SERVER.Database.dbo.tableName, I had a typo in the Database. Easy fix for a complicated sounded message :-)