Creating linked server between DWH and Azure

Question

How do I create a linked server between DWH SQL Server (which is in a service providers environment) and MS Azure.

What type of settings must I make sure is on Azure and what is the string that I should be using on the Linked server option on DWH SQL?

I keep getting this error even though I fill in all the details:

We connecting via an IPsec tunnel which is working as there are other Azure engineers using it to transfer data from DWH to Azure, I am trying to accomplish the opposite. To consume D365 data from Azure and bring it into DWH.

I am new to Azure and familiar with how the linked server option works. I feel I may be missing one setting on Azure to successfully connect to it.

I have SSIS packages that are using an AD account to link to Azure and extract data. problem is that I have to manually kick these packages off. Those same settings are applied to the Linked server settings in SQL Server objects. This is how I know that it is something I am missing in Azure.

Answer 1

When creating a linked server for Azure SQL server, I encountered a similar error as shown below:

I created a linked server for Azure SQL server by following the procedure below:

1. On DWH, navigate to the server object, right-click on Linked Server, and select New Linked Server, as shown below:

2. Provide the Azure SQL server name, i.e., <serverName>.database.windows.net, and the database in the General section of the linked server, as shown below:

3. Go to the Security tab and select Be made using the security context. Provide the username and password for the Azure SQL server, as shown below:

4. Click OK. If all information is correct, the linked server will be tested and created successfully, as shown below: