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

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.