ADO access to Azure SQL

Hi Gent_Panajoti,

Here's a brief and structured response to help you resolve the issue with ADO access to Azure SQL.

Problem Analysis

The error indicates that the Azure hosted agent's dynamic IP addresses are not allowed to access your Azure SQL Server. Since the IP addresses change frequently, your current network security group (NSG) rules might not cover all potential IPs.

Steps to Resolve

1. Azure SQL Server Firewall Rules:

   • Add an Azure SQL Server firewall rule to allow Azure services access.
     • Navigate to your Azure SQL Server in the Azure Portal.
     • Go to Firewalls and virtual networks.
     • Enable the option Allow Azure services and resources to access this server.
     • This setting allows Azure-hosted agents to connect regardless of their changing IP addresses.

2. Network Security Group (NSG) Rules:

   • Ensure that your NSG rules allow traffic from the Azure hosted agent's dynamic IP ranges.
     • Inbound Rule: Should allow AzureCloud to SQL.EastUS on port 1433.
     • Outbound Rules: Ensure they are correctly configured but focus primarily on the inbound rules for SQL access.

3. Private Endpoint Configuration:

   • Ensure that the private endpoint's subnet is properly configured to allow traffic from the required sources.

Example Inbound NSG Rule for AzureCloud

Source: AzureCloud
Destination: Sql.EastUS
Port: 1433
Protocol: TCP
Action: Allow

Verify and Test

1. Verify Changes:

   • After making changes, verify the settings in the Azure Portal.
   • Make sure the NSG is correctly associated with the subnet of the private endpoint.

2. Run Pipeline:

   • Re-run your pipeline to check if the issue is resolved.

Additional Resources

• https://docs.microsoft.com/en-us/azure/azure-sql/database/firewall-configure
• https://docs.microsoft.com/en-us/azure/devops/pipelines/database/sql-azure-pipelines?view=azure-devops

By following these steps, you should be able to resolve the connectivity issue with your Azure DevOps pipeline.