Forum Discussion

Gent_Panajoti's avatar
Gent_Panajoti
Copper Contributor
Jun 05, 2024

ADO access to Azure SQL

I have a pipeline in ADO that uses Microsoft hosted agent. The pipeline invokes a sql command that is a simple query against a database in Azure SQL server. I have a private endpoint set up in Azure SQL (under Networking). The subnet, from which one of its IPs is assigned to this private endpoint, has a network security group. In that network security group, I have one inbound rule and two outbound rules. The inbound rule has AzureCloud (as service tag) as source and Sql.EastUS (as service tag) as destination (since my Azure SQL is set in East US region). The target port is '1433' and protocol is 'TCP'. The two outbound rules are as follows:

first outbound rule - Sql.EastUS as source and AzureCloud as destination. Target port is '8080' and protocol is 'Any'.

second outbound rule - Sql.EastUS as source and AzureCloud as destination. Target port is '443' and protocol is 'TCP'.

When pipeline runs, it has succeeded only a couple of times but most of the times it fails with the following error:

Invoke-Sqlcmd : Cannot open server '......' requested by the login. Client with IP address '20.57.74.195'
is not allowed to access the server. To enable access, use the Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.

 

Every time it fails, I have found out that the client IP address (which is different for every time it fails) is always an address that it belongs to AzureCloud in one of the regions of United States. My thought is that the rules mentioned above should cover for all these IPs that belong to Azure Cloud. 

 

Please advise.

 

Thank You

Gent

2 Replies

  • DTB's avatar
    DTB
    Iron Contributor
    Jun 25, 2024

    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.

    • Gent_Panajoti's avatar
      Gent_Panajoti
      Copper Contributor
      Jun 28, 2024

      Hi DTB,

       

      Thank you for your response!

      I do have a concern with enabling the option Allow Azure services and resources to access this server. As per Microsoft documentation (https://learn.microsoft.com/en-us/azure/azure-sql/database/network-access-controls-overview?view=azuresql), enabling this option creates a firewall rule that is added to Azure SQL and it has IP range of 0.0.0.0 to 0.0.0.0 which opens the inbound traffic of every Azure service to this Azure SQL instance. That would present a security risk for that Azure SQL instance. Also, from that article, it is unclear the priority assigned to this rule by Azure. 

       

       

      Thank You,

       

      Gent

Resources