0

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:enter image description here

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.

Improve this question
1
  • Two hints: 1) Use connection string with protocol, server name and port, something like tcp:db.database.windows.net,1433 and 2) Add the IP address of your DWH server as a firewall rule in Azure. Commented Sep 19, 2023 at 6:43

1 Answer 1

Reset to default
0

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

enter image description here

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:

enter image description here

  1. 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:

enter image description here

  1. 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:

enter image description here

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

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you, This is what I have done. What settings did you update or change in Azure? Is there any other authentication methods that need to be enabled?
I have used SQL server authentication along with that I Added IP address of DWH to firewall rule to add it once connect Azure SQL database in SSMS.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.