Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Azure SQL Server firewall not allowing operation to connect even though option is set to yes in firewall settings

I am trying to import my local SQL server database into Azure and I have all the requirements (storage, bacpac file, etc). When I try to import the db, I am getting the error below.

The Azure SQL Server firewall did not allow the operation to connect. To resolve this, please select the "Allow All Azure" checkbox in the Sql Server's configuration blade.

I have already checked yes on the Allow Azure services and resources to access this server option in the firewall settings and added my client IP. Is there something behind the scenes preventing it from allowing access? I am running my SQL server on a Docker container.

Imported bacpac file Local DB Azure Data Studio imported bacpac file

Import Operation Azure Import DB Operation Azure

Import Error Import Error

Firewall Settings Firewall Settings

like image 385
MPagan Avatar asked Mar 21 '21 23:03

MPagan


People also ask

Can't connect to Azure SQL Database?

If the application persistently fails to connect to Azure SQL Database, it usually indicates an issue with one of the following: Firewall configuration. The Azure SQL database or client-side firewall is blocking connections to Azure SQL Database.

Can't connect to Azure SQL managed instance?

If you are unable to connect to SQL Managed Instance from an Azure virtual machine within the same virtual network but a different subnet, check if you have a Network Security Group set on VM subnet that might be blocking access.

How do I configure my Azure SQL Database firewall?

By default, access through the Azure SQL Database firewall is disabled for all Azure services. Choose ON on this page to enable access for all Azure services. Select Save. Port 1433 is now open on the server and a server-level IP-based, firewall rule is created for your current IP address.

How to configure firewall for Azure SQL database?

To connect to your Azure SQL database, you need to create a firewall rule i.e. you have to specify your IP address in the firewall. If you want to configure the server-level firewall using the Azure portal, you can follow the below steps: Open the Azure portal and navigate to your database. In the database Overview, click on Set server firewall.

Where are firewall rules stored in azure firewall?

The rules are stored in the master database. You can have a maximum of 128 server-level IP firewall rules for a server. If you have the Allow Azure Services and resources to access this server setting enabled, this counts as a single firewall rule for the server.

Why can't I connect to Azure SQL database?

If the address isn't within a range that's in any of the database-level or server-level IP firewall rules, the connection request fails. To access Azure SQL Database from your local computer, ensure that the firewall on your network and local computer allow outgoing communication on TCP port 1433.

What is a server-level IP firewall rule in azure?

(A server-level IP firewall rule must first be created by a user who has Azure-level permissions.) By default, during creation of a new logical SQL server from the Azure portal, the Allow Azure Services and resources to access this server setting is set to No. Database-level IP firewall rules enable clients to access certain (secure) databases.


1 Answers

After a week of trial and error, the database imported fine with no problems so I'll answer my question. What is interesting is that I don't have a concrete answer to my question since I don't know exactly why it did, but I'll give two tips anyway.

  1. It might have been the cache on Azure's side. I got in contact with an Azure rep recently and they stated that the cache may have not updated yet. Clearing your cache could be the source of the problem as well. To clear the cache see this document.
    DBCC FLUSHAUTHCACHE;    
    
  2. Creating a new rule that spans from 0.0.0.0 to 255.255.255.255 in your firewall settings.

Feel free to provide more solutions in the answers. Like I said, it was likely the cache on their side. It was really odd that it didn't work for a while, even with the firewall settings configured correctly.

like image 129
MPagan Avatar answered Oct 17 '22 04:10

MPagan