Connecting to Azure Linux VM SQL server through ADF

Question

Connecting to Azure Linux VM SQL server through ADF

Anonymous

Hi,

I've Azure Linux Virtual Machine with MS-SQL server installed. I would like to access this sql server through ADF to read and write data. Could you help me on this since i couldn't install self-hosted integration runtime on linux vm.

Anonymous

2023-09-19T18:18:06.84+00:00

Thanks for your reply but i don't want to use any other windows vm and want to use only Linux VM for implementation. Is there anyway, we can do that without installing self-hosted IR to access Linux VM SQL server?
Amira Bedhiafi 42,941 Reputation points MVP Volunteer Moderator

2023-09-19T20:22:49.4566667+00:00
You can follow these steps :

Azure SQL Server Configuration:

Ensure that SQL Server on your Linux VM is configured to allow remote TCP/IP connections.

Typically, SQL Server listens on port 1433 by default, but you should verify this and ensure it's open and accessible.

Create a SQL authentication login if you haven't done so already.

Network Security Groups (NSG) & Firewall:

Ensure the NSG associated with your VM's network interface or subnet has an inbound rule allowing traffic on port 1433 (or your custom SQL port) from Azure Data Factory's service tags or a specific IP range.

On the Linux VM, make sure the firewall (like ufw or firewalld) is configured to allow incoming traffic on the SQL Server port.

Managed Private Endpoint (Azure Private Link): This is the most secure method. Azure Private Link allows you to connect to a service (like SQL Server) over a private endpoint in your Virtual Network, making it appear as though the service is part of your VNet. This way, ADF can securely access SQL Server without exposing it to the public internet.

Navigate to your Azure Data Factory instance in the Azure portal.

Under the 'Security + networking' section, click on 'Managed private endpoints'.

Create a new private endpoint targeting the SQL Server.

Once set up, in your ADF pipeline or dataset, use the private endpoint as the server name to connect to the SQL Server.

Direct Public Access (Less Secure): This method involves directly accessing the SQL Server on your Linux VM via its public IP. This is simpler but less secure than using Private Link.

In the ADF Copy Data tool or your specific pipeline, select the source as SQL Server.

Enter the public IP or DNS name of your VM in the Server field.
- Use SQL authentication to provide the username and password.

Specify the database name.

Test the connection.
Anonymous

2023-09-20T14:16:08.65+00:00

Thanks for the inputs and it helped to progress on this. But here SQL server is standalone that is MS SQL Server but not Azure SQL Server which you are pointing. Because of this, I couldn't create private end point as there is no option to select resource as standalone MS SQL Server. Could you please check and let me know whether we are aligned on this.
Amira Bedhiafi 42,941 Reputation points MVP Volunteer Moderator

2023-09-20T18:28:57.7066667+00:00

You can still use Azure Private Link with a custom DNS and IP Forwarding setup, but it can be more complex. Make sure that the NSG rules for your VM allow the port SQL Server is running on (typically 1433). Then create a Private DNS Zone in Azure and then link it to your virtual network
You will need an Azure Private Link to create a Private Endpoint for any resource that supports.And I assume you will be using Azure Load Balancer with a VM behind it,

Finally in your ADF pipeline or dataset, use the custom DNS name (from the private DNS zone) as the server name when connecting to the SQL Server.
Harishga 6,005 Reputation points Microsoft External Staff

2023-09-26T11:53:16.8433333+00:00

Hi@Anonymous ,
It is a little bit tricky what you are trying to do but let's try to find a workaround :

W1 : You may need to set up a VPN between your on-premises network and Azure can create a secure connection. Once the VPN is set up, Azure resources can directly communicate with resources in your on-premises network.

Deploy an Azure VPN Gateway in the Virtual Network.

Set up a Site-to-Site (S2S) VPN connection between Azure and your on-premises network.

Once the VPN is established, Azure Data Factory can communicate with your on-premises SQL Server as though it's part of your local network.

**W2 :**If setting up a VPN is not feasible, you can use a jump server or a bastion host:

Deploy a Windows VM in your on-premises network.

Install Self-Hosted IR on this Windows VM.

Make sure that this Windows VM can communicate with the SQL Server on Linux VM.

Use this Self-Hosted IR to connect Azure Data Factory to your SQL Server.

W3 : Azure Hybrid Connections can be another way to solve this problem. It's a feature of Azure Relay and doesn't require VPN or opening firewall ports.

Set up an Azure Hybrid Connection.

Install the Hybrid Connection Manager on a Windows VM in your on-premises network.

Use this connection to link Azure Data Factory and the SQL Server.

W4: (I don't recommend this, especially in PROD environments) try to expose your SQL Server to the internet and connect directly from Azure Data Factory. This approach is risky and should only be used temporarily or in testing. Ensure you have strong security measures in place, such as firewall rules, encryption, and authentication.

I hope this information helps you. Let me know if you have any further questions or concerns.
Harishga 6,005 Reputation points Microsoft External Staff

2023-09-27T11:54:32.3733333+00:00

Hi@Madhavi Kareddy ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Harishga 6,005 Reputation points Microsoft External Staff

2023-09-29T16:57:30.0233333+00:00

Hi @Anonymous ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

1 answer

Your answer

Anonymous

2023-09-19T18:18:06.84+00:00

Thanks for your reply but i don't want to use any other windows vm and want to use only Linux VM for implementation. Is there anyway, we can do that without installing self-hosted IR to access Linux VM SQL server?
Amira Bedhiafi 42,941 Reputation points MVP Volunteer Moderator

2023-09-19T20:22:49.4566667+00:00

You can follow these steps :

Azure SQL Server Configuration:

Ensure that SQL Server on your Linux VM is configured to allow remote TCP/IP connections.

Typically, SQL Server listens on port 1433 by default, but you should verify this and ensure it's open and accessible.

Create a SQL authentication login if you haven't done so already.

Network Security Groups (NSG) & Firewall:

Ensure the NSG associated with your VM's network interface or subnet has an inbound rule allowing traffic on port 1433 (or your custom SQL port) from Azure Data Factory's service tags or a specific IP range.

On the Linux VM, make sure the firewall (like ufw or firewalld) is configured to allow incoming traffic on the SQL Server port.

Managed Private Endpoint (Azure Private Link): This is the most secure method. Azure Private Link allows you to connect to a service (like SQL Server) over a private endpoint in your Virtual Network, making it appear as though the service is part of your VNet. This way, ADF can securely access SQL Server without exposing it to the public internet.

Navigate to your Azure Data Factory instance in the Azure portal.

Under the 'Security + networking' section, click on 'Managed private endpoints'.

Create a new private endpoint targeting the SQL Server.

Once set up, in your ADF pipeline or dataset, use the private endpoint as the server name to connect to the SQL Server.

Direct Public Access (Less Secure): This method involves directly accessing the SQL Server on your Linux VM via its public IP. This is simpler but less secure than using Private Link.

In the ADF Copy Data tool or your specific pipeline, select the source as SQL Server.

Enter the public IP or DNS name of your VM in the Server field.
- Use SQL authentication to provide the username and password.

Specify the database name.

Test the connection.
Anonymous

2023-09-20T14:16:08.65+00:00

Thanks for the inputs and it helped to progress on this. But here SQL server is standalone that is MS SQL Server but not Azure SQL Server which you are pointing. Because of this, I couldn't create private end point as there is no option to select resource as standalone MS SQL Server. Could you please check and let me know whether we are aligned on this.
Amira Bedhiafi 42,941 Reputation points MVP Volunteer Moderator

2023-09-20T18:28:57.7066667+00:00

You can still use Azure Private Link with a custom DNS and IP Forwarding setup, but it can be more complex. Make sure that the NSG rules for your VM allow the port SQL Server is running on (typically 1433). Then create a Private DNS Zone in Azure and then link it to your virtual network
You will need an Azure Private Link to create a Private Endpoint for any resource that supports.And I assume you will be using Azure Load Balancer with a VM behind it,

Finally in your ADF pipeline or dataset, use the custom DNS name (from the private DNS zone) as the server name when connecting to the SQL Server.
Harishga 6,005 Reputation points Microsoft External Staff

2023-09-26T11:53:16.8433333+00:00

Hi@Anonymous ,
It is a little bit tricky what you are trying to do but let's try to find a workaround :

W1 : You may need to set up a VPN between your on-premises network and Azure can create a secure connection. Once the VPN is set up, Azure resources can directly communicate with resources in your on-premises network.

Deploy an Azure VPN Gateway in the Virtual Network.

Set up a Site-to-Site (S2S) VPN connection between Azure and your on-premises network.

Once the VPN is established, Azure Data Factory can communicate with your on-premises SQL Server as though it's part of your local network.

**W2 :**If setting up a VPN is not feasible, you can use a jump server or a bastion host:

Deploy a Windows VM in your on-premises network.

Install Self-Hosted IR on this Windows VM.

Make sure that this Windows VM can communicate with the SQL Server on Linux VM.

Use this Self-Hosted IR to connect Azure Data Factory to your SQL Server.

W3 : Azure Hybrid Connections can be another way to solve this problem. It's a feature of Azure Relay and doesn't require VPN or opening firewall ports.

Set up an Azure Hybrid Connection.

Install the Hybrid Connection Manager on a Windows VM in your on-premises network.

Use this connection to link Azure Data Factory and the SQL Server.

W4: (I don't recommend this, especially in PROD environments) try to expose your SQL Server to the internet and connect directly from Azure Data Factory. This approach is risky and should only be used temporarily or in testing. Ensure you have strong security measures in place, such as firewall rules, encryption, and authentication.

I hope this information helps you. Let me know if you have any further questions or concerns.
Harishga 6,005 Reputation points Microsoft External Staff

2023-09-27T11:54:32.3733333+00:00

Hi@Madhavi Kareddy ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Harishga 6,005 Reputation points Microsoft External Staff

2023-09-29T16:57:30.0233333+00:00

Hi @Anonymous ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Answer 1

First you need to ensure that your Azure SQL Server instance on the Linux VM allows traffic from Azure services. This is typically controlled by Network Security Group (NSG) rules and firewalls.

Then for SQL Server Configuration, ensure that it is configured to allow TCP/IP connections and it is listening on the correct port, typically 1433 for default installations.

When it comes to connectivity, in the ADF Copy Data tool or your specific pipeline. First, select the source as SQL Server. Then enter the fully qualified domain name (FQDN) of the VM (exple mylinuxvm.westus.cloudapp.azure.com) in the Server field. Don't forget to input the SQL Server's username and password. You need to specify the database name and then test the connection.

A more secure way to allow ADF to access resources in a VNet is to use Private Link which will setup a Managed Private Endpoint in your Data Factory. By setting this up, ADF can securely and privately access the SQL Server on the VM without requiring the VM's SQL Server to be exposed to the public internet. This will also eliminate the need for Self-hosted IR.

a. Navigate to your Azure Data Factory instance in the Azure portal

b. In the left menu, under the 'Security + networking' section, click on 'Managed private endpoints'

c. Click on 'New'

d. Follow the prompts to create a new private endpoint for the SQL Server. Ensure you target the correct resource type and resource.

Once the private endpoint is approved and connected, use the private endpoint as the server name in your ADF pipeline.

Since you can't install a self-hosted integration runtime on the Linux VM, you can use another Windows machine either on-prem or on Azure. Once you install the self-hosted integration runtime on this machine, make sure it has network access to your SQL Server on the Linux VM. Then, configure ADF to use this IR to connect to your SQL Server.

Share via

Connecting to Azure Linux VM SQL server through ADF

1 answer

Your answer