2

We have a SQL instance in azure which until recently was publicly available with access restricted based on IP whitelisting. We have recently ticked the "Deny public access" checkbox and set up private endpoints for the server.

We also have build and release pipelines in azure devops which create a SQL entity framework migration file as part of the build and run it against SQL as part of the release using admin credentials, this means that we can apply migrations on the database without the app needing to have more than basic read/write access to the database.

The problem that we have encountered is that when the release gets to the point of needing to execute the SQL against the database it cannot connect, I assume because the build agent is outside of our vnet.

We are looking at creating our own build agent inside the vnet and using this for the build & release pipelines, we are confident that this will resolve the issue but would like other options if there are any?

5NRF
  • 401
  • 3
  • 12
  • Where are your build agents currently? Microsoft-hosted, or self-hosted? – Vince Bowdren Feb 23 '22 at 17:44
  • what about creating a managed identity for the sql resource, then in a pipeline task, have the script use that identity when logging in : https://learn.microsoft.com/en-us/azure/app-service/tutorial-connect-msi-sql-database – Ryu S. Feb 24 '22 at 00:39
  • Vince, we are currently using MS hosted Build agents. – 5NRF Feb 24 '22 at 09:34
  • 1
    Ryu, Its a connectivity issue rather than authentication, the SQL server is not publicly available. What we used to do is run a CLI command to add the build agents IP to the white list run the SQL and then run the CLI again to remove the IP - but we are moving away from whitelisting to completely blocking external access so we need something inside the Vnet to handle the releases - it looks like the only way to do it is the build agent VM as noted above. – 5NRF Feb 24 '22 at 09:36
  • @5NRF, ahh i see, well a MSFT person seems to recommend that exact solution https://stackoverflow.com/questions/62042593/connect-to-azure-sql-on-private-link-using-azure-devops so its probably the way to go – Ryu S. Feb 25 '22 at 21:01

1 Answers1

1

Thank you 5NRF and Ryu S.. Posting your discussions as answer to help other community members.

You could try anyone of these possible solutions.

  1. Create self-hosted agent on the SQL server VM which would be part of the VNet.
  2. Create a self-hosted agent on an Azure VM which is connected same VNet
  3. Create a self-hosted agent on your local machine. Using Express route, private peering or VPN tunneling to connect to the private endpoint.

run the build and pipelines on the self hosted agent by setting up the pool to your private agent pool which is hosts the self-hosted agent.

Reference: create self-hosted agent, Connecting from an on-premises environment over VPN,

Madhuraj Vadde
  • 1,099
  • 1
  • 5
  • 13