Pushing EF Migrations to Azure SQL from GitHub Actions

The underlying problem for deploying Azure SQL EF migrations is a firewall issue. The big problem with this particular firewall is that GitHub has a YUUUGE list of IPs that the runners run on — and whitelisting GitHub’s gazillion IP addresses in Azure seems… awful. Sure, you can host and run your own deployment runner, but that is a nightmare in and of itself. I suppose I could have also tried to figure out how to create and deploy DACPAC. At a previous gig, the dev team had to manually connect to DEV/PROD and run the migration from their local machine 😱 — yeah no thanks.

My first pass was to look for existing GitHub actions — and I found a nice, albeit older, action in haythem/public-ip@v1.3. And it worked mostly. Up until the point it didn’t. The problem with this action — and a whole bunch of similar actions — is that they all seem to rely on the https://api64.ipify.org public API. Again, not a huge problem…. that is until the API stops responding or hits some sort of throttling limit.

So, I thought — how hard can it be? Just host a web page that returns the remote address and call it a day. Long story short, that’s what I did.

Requirements

You need someplace where you can host a server-side script. I have several websites hosted on WordPress, so I just chose PHP.
Enough access in Azure Entra access to give your deployment identity a SQL Server role
Your GitHub action YAML file.

Create a Server-Side Script that Returns the Remote IP Address

On one of my WordPress boxes, I’m running a simple PHP script on my marketing web server running WordPress.

Just plop this code into a stand-alone PHP page at the root. Something like /get-remote-ip.php

<?php

$remoteAddr = $_SERVER['REMOTE_ADDR'];

// Cloudflare puts the actual remote address in the HTTP-X-FORWADED-FOR Header
if (isset($_SERVER['HTTP_X_FORWARDED_FOR'])) {
    $remoteAddr = $_SERVER['HTTP_X_FORWARDED_FOR'];
}

echo $remoteAddr;

Super simple – it literally just echo‘s back the IP address.

Get the Runner’s External IP Address into a Variable

The big assumption here is that when you were creating your Azure Web app, that you said “yes” to allowing GitHub to publish the web app. If not, I leave it up to you to find out how to create a Managed Identity in Azure to publish your web app.

Assuming that you have this Managed Identity, click the Managed Identity in your Azure portal, then click Azure role assignments in the left navigation. The Managed Identity should already have the Website Contributor role for your App Service. You want to additionally give it the SQL Server Contributor role for your SQL server.

Note, that this may give your closest security engineer or Azure administrator heartburn. Tell them you can run the migration from your local machine instead. Watch them sweat that one out.

Get/Read the Runner’s External IP Address

Honestly, this was the hard part. CI/CD is not my everyday forte, and neither is YAML.

One of the other missing bits of information was how to set the output so that the rest of the build script could see it. Fortunately, there were two posts that provided hints: this one at StackOverflow and this one from GitHub.

In the end, my GitHub action yaml looked like this. I put my IP check near the top so that the action fails fast at the beginning if it can’t resolve the IP address.

      - name: Get GitHub Runner IP
        id: ip
        run: |
            ipv4=$(curl -s --request GET \
            --url https://URLTOMYIPSCRIPT/get-remote-ip.php); echo "ipv4=$ipv4" >> $GITHUB_OUTPUT

# BUILD STEPS HERE

      - name: Whitelist GitHub Runner IP
        id: whitelist-ip
        uses: azure/cli@v1
        with:
          inlineScript: |
            set -eu
            az sql server firewall-rule create \
              --name GITHUBDEPLOYIP \
              --resource-group ${{ secrets.RESOURCE_GROUP }} \
              --server ${{ secrets.SQL_SERVER }} \
              --start-ip-address ${{ steps.ip.outputs.ipv4 }} \
              --end-ip-address ${{ steps.ip.outputs.ipv4 }}
            sleep 30

      - name: Run EF migrations
        run: | 
          dotnet tool install --global dotnet-ef
          dotnet tool restore
          dotnet ef database update \
            --verbose \
            --project YOUR-PROJECT-WITH-DBCONTEXT \
            --startup-project YOUR-PROJECT-STARTUP \
            --context YourAppApplicationDbContext \
            --connection "${{ secrets.CONNECTION_STRING }}"

      - name: Remove GitHub Runner IP
        if: steps.whitelist-ip.outcome == 'success'
        uses: azure/cli@v1
        with:
          inlineScript: |
            set -eu
            az sql server firewall-rule delete \
              --name GITHUBDEPLOYIP\
              --resource-group ${{ secrets.RESOURCE_GROUP }} \
              --server ${{ secrets.SQL_SERVER }}

tada 🥳

keithmenor.dev