SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
10,935 questions
Connecting to Azure Linux VM SQL server through ADF
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(236.8, 87%, 32%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMK%3C/text%3E%3C/svg%3E)
Madhavi Kareddy
0
Reputation points
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.
{count} votes
-
Amira Bedhiafi 4,081 Reputation points2023-09-19T15:48:51.5533333+00:00 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 (e.g.,
mylinuxvm.westus.cloudapp.azure.com) in theServerfield. 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.
-
Madhavi Kareddy 0 Reputation points
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 4,081 Reputation points
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
ufworfirewalld) 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
Serverfield.- Use SQL authentication to provide the username and password. - Specify the database name.
- Test the connection.
- Azure SQL Server Configuration:
-
Madhavi Kareddy 0 Reputation points
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 4,081 Reputation points
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.
Sign in to comment