How to allow Python Script in Azure Data Factory to access Azure SQL Server with private endpoint (no public access)

11-4688 61 Reputation points
2023-10-27T18:48:52.9466667+00:00

I have a python script in batch service as an element of Azure Data Factory (like here: https://learn.microsoft.com/en-us/azure/batch/tutorial-run-python-batch-azure-data-factory ).

The ADF Pipeline has only one element - the mentioned script. The script connects to Azure SQL Database, fetches the data, preprocesses it and inserts into the table in the same Azure

SQL Database like below:


import urllib

import sqlalchemy

import pandas as pd

sql_query = 'SELECT TOP (1000) * FROM [dbo].[Table]'

server = '<servername>.database.windows.net'

database = '<dbname>'

username = '<username>'

password = '<password>'

driver = '{ODBC Driver 17 for SQL Server}'

odbc_str = 'DRIVER='+driver+';SERVER='+server+';PORT=1433;UID='+username+';DATABASE='+ database + ';PWD='+ password

connect_str = 'mssql+pyodbc:///?odbc_connect=' + urllib.parse.quote_plus(odbc_str)

engine = sqlalchemy.create_engine(connect_str)

df = pd.read_sql(sql_query, engine)

engine.dispose()

However, it can't access the SQL Server because it has Public Access Disabled and I can not mark 'Allow azure resources and services to access this server', there are only private access options. Everything works fine when I do mark this option for testing purposes.

How can I make this ADF Pipeline (one element - python script) access the SQL Server? The ADF resource is related to this network, pipelines based solely on SQL scripts work fine, the issue is related only to the Python script and I'm not even sure where to start - is it related to Integration Runtime or network settings of Batch Account related to the ADF?

I would really appreciate some help.

Azure Batch
Azure Batch
An Azure service that provides cloud-scale job scheduling and compute management.
305 questions
Azure Virtual Network
Azure Virtual Network
An Azure networking service that is used to provision private networks and optionally to connect to on-premises datacenters.
2,158 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,762 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,606 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Amira Bedhiafi 15,676 Reputation points
    2023-10-28T18:36:30.44+00:00

    You have the option Allow azure service or resources to access this server, you can enable it.

    Otherwise, you can create an Azure integration runtime and enable Data Factory Managed Virtual Network.

    On the Integration runtime setup page, choose what integration runtime to create based on required capabilities.

    And under Virtual network configuration (Preview), select Enable.

    Screenshot that shows enabling a new Azure integration runtime.

    https://learn.microsoft.com/en-us/azure/data-factory/tutorial-copy-data-portal-private