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
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 YouGent Read More