I need to add a linked server to a MS Azure SQL Server

Question

I have tried and tried, and can not get linked. I can connect to the server using SSMS, but can not link to it from a local server. Here is my script (replacing things in brackets with pertainent information):

EXEC master.dbo.sp_addlinkedserver
    @server     = N'[servername].database.windows.net',
    @srvproduct = N'Any',
    @provider   = N'MSDASQL',
    @datasrc    = N'Azure_ODBC1'
GO

EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname  = N'[servername]',
    @useself     = N'False',
    @locallogin  = NULL,
    @rmtuser     = N'[username]',
    @rmtpassword = '[password]'

GO

Error Message

Philippe · Accepted Answer · 2017-01-13 15:35:48Z

28

As specified in ckarst second link, there is a solution that works. I am posting it here to save you the trouble to search for it. As suggested by JuanPableJofre in this page Azure feedback :

Using SQL 2014, I was able to do a distributed query between a local SQL server and a SQL Azure. First, I created a Linked-Server:

Linked Server (name): LinkedServerName
Provider: Microsoft OLE DB Provider for SQL Server
Product name: (blank)
Data Source: azure-db.database.windows.net
Provider string: (blank)
Location: (blank)
Catalog: db-name

In security options: (*)

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

In SSMS entered the following test query:

use [Local_DB] 
go

Select *
from [LinkedServerName].[RemoteDB].[dbo].[Remote_Table]

It worked beautifully !!

To summarize, the linked server is created on your local database. The catalog (database name) is important as Azure might not let you specify it in a query (ie: use azureDBName will not work on Azure), so the database name has to be in the catalog.

edited Jan 13, 2017 at 15:35

answered Dec 14, 2016 at 15:04

Philippe

5589 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Rick Over a year ago

yep works great. the trick was to fill in the ssms dialog (as Phillippe describe) and not let it take on its default values - something I never thought to try before .. great tip, thanx Phillpipe for sharing this!

Mihail Shishkov Over a year ago

azure-user-name referes to what username? I've tried with the server administrator and it can't connect. It fails with "Cannot open database "XXX" requested by the login. The login failed.". I guarantee that the credentials are correct because I am able to connect directly using those credentials, however the system administrator's name is not sa and the password contains special characters like ?<~. Any idea what's going on?

Philippe Over a year ago

@Mihail Shishkov azure-user-name is your azure sql username. Make sure that you entrered the azure database name in the Catalog field.

Mihail Shishkov Over a year ago

@Philippe you are correct I was misspelling the database name (Catalog). Just to inform others that may read this: you can also use the "SQL Server Native Client" instead of "Microsoft OLE DB Provider"

jessieloo Over a year ago

I kept receiving the message 'Server name cannot be determined.' To fix, I had to enter azure-user-name@azure-db as the remote log-in, in which azure-db is the first part of the data source: azure-db.database.windows.net

|

ckarst · Accepted Answer · 2015-02-26 18:57:21Z

7

Unfortunately, Linked Server is not supported by SQL Azure DB. https://msdn.microsoft.com/en-us/library/azure/ee336281.aspx

However, as you can see from the forum link below, Microsoft is aware of the scenario and the customer feedback has been heard. http://feedback.azure.com/forums/217321-sql-database/suggestions/402636-cross-database-reference

edited Feb 26, 2015 at 18:57

answered Feb 26, 2015 at 17:36

ckarst

7615 silver badges11 bronze badges

1 Comment

N8allan Over a year ago

It is possible to establish a linked server to SQL Azure from SQL Server Enterprise Edition (or dev edition of course). See the comments in the second link you posted.

Stuart Ozer · Accepted Answer · 2015-11-04 01:45:29Z

2

While Azure DB doesn't support defining Linked Servers, you can use the recently previewed Elastic Query feature to define an "External Data Source" that is just another Azure DB database, and define "External Tables" that are references to tables in that external database. Then you can query these as though they are local objects. This is very similar to the Linked Server concept and is described in detail here.

answered Nov 4, 2015 at 1:45

Stuart Ozer

1,3847 silver badges7 bronze badges

1 Comment

N8allan Over a year ago

According to this article, Linked Servers are supported for Enterprise Edition: blogs.msdn.microsoft.com/sqlcat/2011/03/07/…

Fahim · Accepted Answer · 2019-10-30 11:19:33Z

-- Make a link to the cloud
EXEC sp_addlinkedserver   
     @server='[servername].database.windows.net',   -- specify the name of the linked server    
     @srvproduct=N'Azure SQL Db',
     @provider=N'SQLNCLI', 
     @datasrc='yourservername',             -- add here your server name   
     @catalog='FCS';
GO

--Set up login mapping
EXEC sp_addlinkedsrvlogin 
    @rmtsrvname = '[servername].database.windows.net',   
    @useself = 'FALSE', 
    @locallogin=NULL,
    @rmtuser = 'username',
    @rmtpassword = 'password'
GO

This does create a linked server in my envirnoment, however it doesn't connect to the catalog that I have specified (FCS). It connects to a default for some reason. Is there something i am doing wrong

Collectives™ on Stack Overflow

I need to add a linked server to a MS Azure SQL Server

4 Answers 4

6 Comments

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related