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 Select the correct answer from above options

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/