Create Linked Server to SQL Azure Database not working

Question

I have follwed the below step from the question that was answered in relating to the linked server for Azure DB

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

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

score 1 · Answer 1 · answered Oct 31 '19 at 02:40

Here's a simple example explaining how to connect to a Azure SQL Database using distributed queries:

-- Configure the linked server  
-- Add one Azure SQL DB as Linked Server  
EXEC sp_addlinkedserver  
  @server='myLinkedServer', -- here you can specify the name of the linked server  
  @srvproduct='',       
  @provider='sqlncli', -- using SQL Server Native Client  
  @datasrc='myServer.database.windows.net',   -- add here your server name  
  @location='',  
  @provstr='',  
  @catalog='myDatabase'  -- add here your database name as initial catalog (you cannot connect to the master database)  

-- Add credentials and options to this linked server  
EXEC sp_addlinkedsrvlogin  
  @rmtsrvname = 'myLinkedServer',  
  @useself = 'false',  
  @rmtuser = 'myLogin',             -- add here your login on Azure DB  
  @rmtpassword = 'myPassword' -- add here your password on Azure DB  

EXEC sp_serveroption 'myLinkedServer', 'rpc out', true;  

-- Now you can use the linked server to execute 4-part queries  
-- You can create a new table in the Azure DB  
EXEC ('CREATE TABLE t1tutut2(col1 int not null CONSTRAINT PK_col1 PRIMARY KEY CLUSTERED (col1) )') at myLinkedServer  
-- Insert data from your local SQL Server  
EXEC ('INSERT INTO t1tutut2 VALUES(1),(2),(3)') at myLinkedServer  

-- Query the data using 4-part names  
SELECT * FROM myLinkedServer.myDatabase.dbo.myTable

Please follow the official example and test again.

Reference: Add a Azure SQL Database as a Linked Server For Use With Distributed Queries on Cloud and On-Premises Databases

Hope this helps.

score 0 · Answer 2 · answered Oct 30 '19 at 12:43

0

You are using the @server as you should use the @datasrc. The @server is just the name of your linked server on your SQL Server (on-premise) instance, while the @datasrc should be the name of your Azure SQL Database logical server.

Please read this article for a complete example of how to create a linked server.

answered Oct 30 '19 at 12:43

Alberto Morillo

13,893
2
24
30

I think you have done the opposite of what I asked for. I want to create The linked server of SQL Azure DB on my local database. At the moment the issue I have is the conneting to the DB which I have specified in @catalog and currentley It is connecting to the default where I don't have any data – Fahim Nov 04 '19 at 14:36

Create Linked Server to SQL Azure Database not working

2 Answers2