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

Answer 1

Using SQL 2014, you should be able to do a distributed query between a local SQL server and an SQL Azure. 

First, create 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: (*)

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 works fine.

Moreover, you can refer this article for more information: 

https://www.sqlshack.com/create-linked-server-azure-sql-database/