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

7
votes

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

1
asp.net.netsql-serverasp.net-mvcazure
do you import the database in code level? – Leon Yue
@MPagan thanks for sharing. I haven't met this situation for now. Really sorry! – Leon Yue
This is very strange because with same setting(without adding 0.0.0.0 IP) I took backup of dbs on blob storage before 4 month ago and was successfully completed the tasks, Today I start doing this and it gives me the same error, ? any reason, – Saad Awan
@MPagan I have 8 Databases under same database server, and all have same IPs I allowed under them with no difference, concerned is that all are working fine before 3, 4 months back but yesterday they do problem. Interesting thing is that After another attempt backup succeed:) . – Saad Awan
@MPagan I don't think this is related to IP restriction as far as I understand from your screenshot. Do you have any SQL users inside your database that you try to import? If so, please delete them from your local, then create the bacpac and try importing new backpac again. This might fix your problem. Let me know the good news! :) – Mehmet Taha Meral

1 Answers

1
votes

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.