When you start out with Azure DevOps and a lack of understanding or perhaps simply wanting to do a quick and dirty with Azure SQL database it’s not all that hard to set up. Create a pipeline for the build and then a release pipeline. Use some secret variables for the username and password and off you go. Now secure your database, you have no access to it, except via a bastion host, Express Route, or VPN.
Now try and connect from Azure DevOps. The rules have just changed in a major way. It’s not documented anywhere in Microsoft, or it’s hard to find, real hard. Thi exercise came about through the desire to migrate a small data warehouse onto Azure SQL and apply the best security. It’s part of a bigger Azure Data Platform
To me “Good Design” has some requirements, Microsoft Well-Architected doesn’t say much, it’s very high-level guidance, but I’m going to put together my “Good Design” and you can decide
In this article, you can see a rant about issues I encountered and the simple diagram of what I was creating at this point for database CI/CD
Steps
Create a VM
This is your build server, I had a VM already built for other purposes, but multipurpose use meant valuable cost savings while not compromising the other use. However, due to our VNets, it was on a different one to the Azure SQL database. In hindsight, I believe this is “Good Design”. I was about to learn a lot about Azure Networks. I can restrict the access of the VM to only what I want to allow through the peering with NSG. There are a lot of issues with configuring NSG with Private endpoints on VNets. They don’t appear to be compatible. I can restrict traffic with a NSG on the VM VNet.
Azure SQL
Create your Azure SQL database and configure the VNet with it. I set up a Service Endpoint to allow services on the same VNet to have access to the database. Then disable public access. It will recommend you create a private endpoint to allow connectivity. Do this.
Private Link
There’s a piece of information that’s not obvious about setting up a private endpoint. If you want to access it from another VNet, then you need to connect that VNet to the Private DNS related to the PAAS service you set up. Select Private DNS Zones and select the DNS associated with the Azure SQL database. Select Virtual Network Links and add the VNet the VM is connected to. Now your VM will be able to see the private IP associated with the Azure SQL database. At no point through the processes is this last step mentioned in the portal. It’s in the Private DNS documentation, but might not be where you otherwise would start looking. I mean why would you? You have peered your two VNets, it never gave a hint. You’ve created a Private End Point and never gave a hint. This is a huge and painful gap If under pressure it would be easy to give up and go with a simpler solution
VM Build Tools
You will need to deploy the visual studio build tools and SQL package or whatever you need to carry out a deployment to the database on the VM. I wanted to use a Service Principal and keep passwords away from the build pipeline. Unfortunately, after much PowerShell hacking, I had to declare defeat. It appeared to be due to a related issue with the AZ package, based on the error. I’ll leave that to someone.
I might try again at some point, but security costs money in Azure and Bastions and other privacy starts burning cash. It was my personal testing environment as I didn’t want to inadvertently break any security on the company domain. I chose to use a Key vault and a database connection string. Why aren’t there easier ways? Simpler is more secure. Why can’t azure DevOps attach to the database as a Managed Identity user for your Azure DevOps? Maybe I missed something, however, once again there’s no guidance that’s easy to find so people will make poor choices.
This will hopefully help someone set up a more secure environment and get past some of the issues that the document leaves you grasping to work out. This is faulty security in Azure as security is hard because of poor documentation and UI. That makes for the poor security that can and will happen by bad choices
See ya ’round
Peter
