Is it no longer possible to connect a free or basic tier app service to a basic tier SQL database?

Question

I've been experimenting with a dotnet core MVC app and a small SQL database on my local. I now would like to push it to an app service and SQL database using my free Visual Studio $50/month credit. It doesn't look like it's possible.

All of the things I describe as doing from the console I also tried by script with similar results.

I first tried creating a SQL database and setting allowing public access, as described here. On the networking tab I've checked off every combination of "Public endpoint" and "Allow Azure services and resources to access this server" when creating the server. When I then click create it creates the database and server, but the firewall rule inevitably fails with "Reason: Unable to create or update firewall rules since Deny Public Network Access is set to Yes." with a helpful link to https://learn.microsoft.com/azure/azure-sql/database/connectivity-settings#deny-public-network-access.

So I went to the db server ,created a private endpoint for it, and linked it to the server. I could then go in to the firewall tab and set "Deny public access" to "No" (and save successfully -- "Successfully updated server firewall rules"), However, the change didn't stick -- if I then tried adding my client IP it failed, as did trying to toggle "Allow Azure services and resources to access this server".

"Fine", I think, and begin to execute the really simple scripts to set them manually:

$SecureString = ConvertTo-SecureString "passwordhere" -AsPlainText -Force  

Set-AzSqlServer -ServerName Mydbserver -ResourceGroupName My-East-Dev -SqlAdministratorPassword $SecureString -PublicNetworkAccess "Enabled"  

the resulting data included "PublicNetworkAccess : Disabled" -- so again, the change didn't stick, as indicated by (Get-AzSqlServer -ServerName Mydbserver -ResourceGroupName My-East-Dev).PublicNetworkAccess returning "Diabled". (I tried via Bash as well, but omit the script for brevity)

OK, I figured, Microsoft just doesn't want to allow public access anymore. I get it, it's a security thing. So I created a basic tier app service and tried to get it to connect to the database server, but was unable to get it to work. Free and basic tier app services can't be put into vnets, so I can't figure out a way to get them to communicate.

I've also experimented with the free and basic tiers of "App + Database" and got similar errors.

Any suggestions? Or is it just impossible to do what I want to do with the $50/month Visual Studio allowance?

Answer 1

Check how the App Server resolves the FQDN of the Azure SQL Server. To perform this check, we need to open Console access of the App Service and run command nameresolver <azure-sql-server-fqdn>.

Once you create the private endpoint, the Azure SQL uses a private IP address, It no longer has a public IP address. Are you trying to connect with that private IP address?

Once the private endpoint has been created, you need to enable the virtual network integration in the App Service. Once we open the desired App Service, in the left pane under the settings, we can find Networking.

When we open the Networking section, the first option to configure is VNet Integration. The process is straightforward and just need to select an appropriate virtual network and empty subnet. Then, we just need to add two parameters more in the App Service configuration to have a fully functional App Service. By default, App Service routes only RFC1918 traffic into your VNet and cannot work with the Azure DNS Private Zones. If we want to route all of your outbound traffic into your VNet and use the Azure DNS Private Zones, we need to add the following settings in App Service configuration:

• WEBSITE_DNS_SERVER with value 168.63.129.16
• WEBSITE_VNET_ROUTE_ALL with value 1

Once you have implemented those changes, you can disable public access to the Azure SQL Server, by setting Deny public network access on YES and Allow Azure services and resources to access this server to NO. Now, you can run the command nameresolver <azure-sql-server-fqdn> from App Service console again, to see what result will be.

The App Service should now communicates with the Azure SQL Server using the private IP address, assigned to the Azure SQL Server as a private endpoint.

To my knowledge, you need a PremiumV2-tier or higher app service as requirement.

Answer 2

Thanks for the response, Alberto. Sorry I wasn't clear in my post, but the issue is connecting a basic or free-tier app service to the database. App services at that tier can't be put in a VNet, so that won't actually work. Also, the issue isn't disabling public access to the SQL server, it's enabling it in the first place. That appears to be 100% impossible, from when I create it from console or command line, or afterwards by updating.