I'm deploying an Azure Container Instance (ACI) and an Azure SQL Database in the same virtual network but different subnets. The database has a private endpoint in a third subnet. I've configured the ACI to communicate with the database using the FQDN of the Azure SQL server ( arc-azure-postgresql-server.database.windows.net ). However, my Python application within the ACI times out when attempting to write to the database. The subnets have the appropriate service endpoints and delegations, and I've verified the database server name. I suspect a networking or DNS resolution issue between the ACI and the SQL Database due to the private endpoint. How can I correctly configure my network layout to ensure smooth communication between the ACI and the database? Infrastructure as code can be provided if helpful.

Hi, I can't quite tell from your post if it's timing out from being unable to reach, or permissions. So I'll detail both ways I would get it working. There should be a Private DNS for 'privatelink.database.windows.net' of which there is a Virtual network link to your virtual network where the SQL and ACI are located. Is that setup and associated? Bicep IaC for this is here: https://video2.skills-academy.com/en-us/azure/templates/microsoft.network/privatednszones/virtualnetworklinks?pivots=deployment-language-bicep For permissions, check you have enabled the system-assigned managed identity on the ACI https://video2.skills-academy.com/en-us/azure/container-instances/container-instances-managed-identity then you can look to add the managed identity of the ACI into the PostgreSQL with the appropriate permissions to write. SET aad_validate_oids_in_tenant = off; CREATE ROLE myuser WITH LOGIN PASSWORD 'CLIENT_ID' IN ROLE azure_ad_user; Client_id being the MS Entra Enterprise Application - Application Id guid. More information here on that setup: https://video2.skills-academy.com/en-us/azure/postgresql/single-server/how-to-connect-with-managed-identity#creating-a-postgresql-user-for-your-managed-identity Bicep IaC is here: https://video2.skills-academy.com/en-us/azure/templates/microsoft.containerinstance/containergroups?pivots=deployment-language-bicep You'll want configure this section to enable system-assigned MI on the ACI if it's not already from the code above: identity: { type: 'SystemAssigned' } With these two setup and verified, things should work for you. If this was helpful, feel free to mark as an accepted answer.

How to best design secure network setup where ACI must write to SQL Server

Accepted answer

Dan Rios 1,900 Reputation points MVP

2023-10-25T15:30:02.5366667+00:00
Hi,

I can't quite tell from your post if it's timing out from being unable to reach, or permissions. So I'll detail both ways I would get it working.

There should be a Private DNS for 'privatelink.database.windows.net' of which there is a Virtual network link to your virtual network where the SQL and ACI are located. Is that setup and associated?

Bicep IaC for this is here: https://video2.skills-academy.com/en-us/azure/templates/microsoft.network/privatednszones/virtualnetworklinks?pivots=deployment-language-bicep

For permissions, check you have enabled the system-assigned managed identity on the ACI https://video2.skills-academy.com/en-us/azure/container-instances/container-instances-managed-identity then you can look to add the managed identity of the ACI into the PostgreSQL with the appropriate permissions to write.

SET aad_validate_oids_in_tenant = off; CREATE ROLE myuser WITH LOGIN PASSWORD 'CLIENT_ID' IN ROLE azure_ad_user;

Client_id being the MS Entra Enterprise Application - Application Id guid.

More information here on that setup: https://video2.skills-academy.com/en-us/azure/postgresql/single-server/how-to-connect-with-managed-identity#creating-a-postgresql-user-for-your-managed-identity

Bicep IaC is here: https://video2.skills-academy.com/en-us/azure/templates/microsoft.containerinstance/containergroups?pivots=deployment-language-bicep

You'll want configure this section to enable system-assigned MI on the ACI if it's not already from the code above:

identity: { type: 'SystemAssigned' }

With these two setup and verified, things should work for you. If this was helpful, feel free to mark as an accepted answer.
Please sign in to rate this answer.
Richard Haigh 45 Reputation points

2023-10-25T15:42:39.9733333+00:00

Hi Dan

Thank you for the assistance. In answer to the question re the DNS, the configuration is:

Virtual Network: All resources are enclosed within a single virtual network, with specific subnets tailored for different purposes:

SQL Subnet: This is exclusively for the Azure SQL Database.

ACI Subnet: The Azure Container Instance (ACI) is hosted here. This container runs a Python application, which is designed to write data to the Azure SQL Database. It targets the database using its Fully Qualified Domain Name (FQDN), specifically arc-azure-postgresql-server.database.windows.net.

Private Endpoint Subnet: Here, the private endpoint for the Azure SQL Database is provisioned.

Azure SQL Database Configuration: The database resides in its designated SQL Subnet and has a server name of arc-azure-sql-server. To enhance security and limit public access, we've connected this database to our VNet using a private endpoint situated in the Private Endpoint Subnet, making it accessible only from within the VNet. I've opted for Private Link as my preferred connectivity method, instead of service endpoints or public endpoints, to ensure private connectivity to the database. Azure Container Instance (ACI) Configuration: The container is hosted in the ACI Subnet and is configured to reach the Azure SQL Database by targeting its FQDN. Within the container environment variables, we have a DB_HOST that points to the SQL server's FQDN, which is expected to resolve to the private IP address of the SQL server due to our Private DNS setup. The container's application tries to communicate with the database over this private address. Private DNS Zone Integration: To ensure name resolution within the VNet, a Private DNS Zone for 'privatelink.database.windows.net' is set up. This DNS zone, associated with the primary virtual network, makes sure that any request to the SQL server's FQDN gets resolved to the private endpoint's IP address and not the public one.

With this architecture, my aim is for the ACI, residing in its dedicated subnet, to communicate securely with the Azure SQL Database via the private endpoint, with the Private DNS facilitating the correct name resolution. Should the Python app in the ACI trying to connect directly to arc-azure-sql-server.database.windows.net is correct given the private endpoint setup? Or should the connection string point somewhere else?

I haven't enabled the system-assigned managed identity on the ACI. I believe this could potentially be the next thing to explore, given your recommendation. I will proceed to enable it and then associate the identity with the SQL with the appropriate permissions.

Dan Rios 1,900 Reputation points MVP

2023-10-25T16:04:30.1833333+00:00

Thanks for the information. Yes it should use the FQDN to communicate, then this will resolve to the private DNS zone A record that is setup for the private endpoint, and know where to route for the internal private address of the virtual network IP, so that is good.

I think once you have the ACI managed identity enabled, you can look to add the managed identity's application ID from the entra enterprise app IP with the role. Double check my post, I edited as I pasted incorrectly the SQL command but the link to the docs details it anyway.

Then it should work, although I'm not proficient my Python so you may need to tweak the connectivity mechanism in the Python to use the MSI auth to the FQDN for it to work. Take a look at this: https://video2.skills-academy.com/en-us/azure/app-service/tutorial-connect-msi-azure-database?tabs=postgresql%2Csystemassigned%2Cpython%2Cwindowsclient#3-modify-your-code

and: https://video2.skills-academy.com/en-us/python/api/overview/azure/identity-readme?view=azure-python

Under the Python tab, Azure Database for PostgreSQL you will see some sample code to configure it to use a managed identity, hope this helps.

Alfredo Revilla - Upwork Top Talent | IAM SWE SWA 27,446 Reputation points

2023-11-10T00:46:41.87+00:00

Hello @Richard Haigh , let us know if you need additional assistance. If the answer was helpful, please accept it and rate it so that others facing a similar issue can easily find a solution.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How to best design secure network setup where ACI must write to SQL Server

0 additional answers

Your answer