SSMS firewall rules error

Johnm 1

I am accessing my Azure host SQL db from my local machine using SSMS. I have a VPN and I have Firewall rules set on the Azure so evertime my IP adress changes which is daily i have to add a new firewall rule. SSMS does this in a pretty straightforward way an it has worked for years like this.

Yesterday this stoped working. SSMS would time out with a connection error after i had goen throuht eth tauthentication process and had confirmation via the browser that the request was authenticated.

When i log in to Azure I can see the IP address sent from SSMS as the next address to add but Azure add it. I now have to press add myslef. this means the process is much much more laborious.

Have you changed the process? Or is there an issue here?

Erland Sommarskog 116.9K Reputation points MVP

2024-11-22T22:18:32.0033333+00:00

Maybe your list of IP-address has exceeded some size? I would clean up that list, since else people who have the IP-address you had yesterday, could be able to access your server.

Myself, I always set up the firewall rules to only permit my current IP-address. Thankfully, my IP-address only changes every two weeks or so.
Johnm 1 Reputation point

2024-11-23T16:14:01.1533333+00:00

Vijayalaxmi Kattimani thanks for your reply but you didn't actually read my message. If you had you would see that I have had this Azure DB for several years and have been accessing it via SSMS daily. When I try to connect SSMS says it needs to add the IP address as a new rule and i get the MS authentication dialog in my browser, click click click and SSMS sends the new IP to Azure, Azures registers it and SSMS connects, all pretty easy and seemless, until the other day when this stopped working.

As of THursady 21st this no longer works. SSMS goes through the same process but after i click in the SSMS dialog to register the new IP address and after the browser has authenticated me SSMS times out saying it can't connect. When i log in to Azure i can see the new IP address waiting to be added as a new rule but Azure hasn't actually added it. When i add it from Azure it works, But this is massively inconvenient and Now i have to log into Azure when beofre i haven't needed to.

Something in either SSMS or Azure has changed.

Erland, thanks for the suggestion but I had already all the other firewall rules, but it doesn't work.
Erland Sommarskog 116.9K Reputation points MVP

2024-11-23T17:45:17+00:00

I did some experimenting. I went into the portal and dropped the firewall rule. I then tried connect with SSMS 21 preview 1 from a VM, which I think I've never connected to Azure from. This had me going around in circles like you described. I logged in, but the dialog to set the firewall rule maintained that I was not connected.

I then tried from my laptop with SSMS 20.2, and here everything worked smoothly. I was asked to login, and I think I had to do that twice. But I was able to connect.

So what is going on your end, I don't know. But it does not seem to be a general thing.

Then again, I am not sure that it matters. I understand that going into the portal everyday is a bit of a burden, but do you really want the person have the IP address you had yesterday or last week to be able to access your database? True, that person needs to know the server name, username and password. But nevertheless.

And if you truly don't care, you could just as well set up a firewall rule that covers the entire range where your address might be.
Vijayalaxmi Kattimani 1,330 Reputation points Microsoft Vendor

2024-11-25T13:07:03.08+00:00

Hi @Johnm,

Welcome to the Microsoft Q&A Platform! Thank you for asking your question here.

If you have a support plan could you please file a support ticket for deeper investigation and do share the SR# with us? In case if you don't have a support plan please let us know here.
Daniel Lewis 0 Reputation points

2024-11-25T13:08:28.5366667+00:00

This is happening for me also. If you have an owner role in the portal you can no longer add the IP address via SSMS
Vijayalaxmi Kattimani 1,330 Reputation points Microsoft Vendor

2024-11-26T08:57:47.73+00:00

Hi @Johnm,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Johnm 1 Reputation point

2024-11-26T10:21:36.52+00:00

@Vijayalaxmi Kattimani

I do not have a support plan so i cannot raise a ticket, unless i can do this in Azure but i'm not sure if this is possible, please advise.

The problem is not solved yet.
Mat Wood 5 Reputation points

2024-11-26T12:56:25.59+00:00

I am having the same problem since last week.

Easiest workaround I have found is to connect to same database using Azure Data Studio, this creates the firewall rule ok for me and then SSMS can connect again without needing to set it up.
Daniel Lewis 0 Reputation points

2024-11-26T14:17:41.4133333+00:00
Johnm 1 Reputation point

2024-11-26T15:48:12.93+00:00

This is a realtime screenshot of the process no longer working. AS you can see when i log in to Azure the new IP address is ready to be added but crucially it is pending a user intervention.

Why has this changed? It always used to work seemlessly.
Johnm 1 Reputation point

2024-11-26T15:50:37.1866667+00:00

@Mat Wood Thanks, I didn't even know ADS existed. I will ue this as a backup until MS can resolve the issue.
Erland Sommarskog 116.9K Reputation points MVP

2024-11-26T22:13:44.46+00:00

I note that the behaviour John and Daniel see are quite different. Although the underlying symptom could be the same. It seems that there is an issue with the gateway you are talking to. Which gateway that would be could depend on where you are located and maybe also how things are set up in SSMS.

ADS may have some different config here, which is why it works there.

For the record, I just tried this in another of my VMs with SSMS 20.2, and my IP address had actually changed, so I had reason to try the dialog. And it worked without problems for me.

Now, I will have to go into the portal and remove the old IP-address.
Mahesh Kurva 2,750 Reputation points Microsoft Vendor

2024-11-27T21:52:43.7233333+00:00

Hi @Johnm,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Johnm 1 Reputation point

2024-11-27T22:54:54.6266667+00:00

@Mahesh,

nothing has been resolved so really rather than you waiting on me I’d politely say to you that I’m waiting to hear from you something that I can try. I have explained and provided a screen grab showing what I am experiencing.

please tell me what how this can be fixed
Mahesh Kurva 2,750 Reputation points Microsoft Vendor

2024-11-28T23:24:31.8+00:00
Hi @Johnm,

Apologies that you're facing an issue.

Here are a few steps you can try to troubleshoot:

Make sure you have the latest version of SSMS installed.

Clear the cache in SSMS by navigating to %AppData%\Microsoft\SQL Server Management Studio and deleting the cache files.

While this is more laborious, manually adding the IP address as you have been doing can be a temporary workaround.

I hope this information helps. Please do let us know if you have any further queries.
ShaktiSingh-MSFT 16,241 Reputation points

2024-11-29T04:02:08.3166667+00:00

Hi Johnm •,

We are sorry about the inconvenience caused to you.

Until the issue is checked internally, we request you to use Azure Data Studio to connect to your Database.

If this does not help, could you please let us know in the private message with your subscription detail.

Appreciate your patience.

Thanks
Mahesh Kurva 2,750 Reputation points Microsoft Vendor

2024-12-02T16:53:14.7933333+00:00

Hi @Johnm,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Johnm 1 Reputation point

2024-12-02T17:52:15.9966667+00:00

Please stop asking me for a comment, you have not fixed the issue so I’m not sure what is there for me to say? The ball is in your court.
Erland Sommarskog 116.9K Reputation points MVP

2024-12-02T20:48:37.98+00:00

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Please stop these kind of useless posts. I am sure that if things starts to work for John as he wants it, he will let us now.
Steve Sterley 0 Reputation points

2024-12-09T10:35:20.9933333+00:00

I'm having the same problem. It started about two weeks ago. I didn't update my version of SSMS, one day it just simply stopped being able to add new IP addresses to our Azure hosted SQL server. This is likely a problem with Azure rather than SSMS.

It is happening to all of my colleagues in my office. We're using SSMS 20.2 and SSMS 18.9.2 and some even older.

We are connecting from the UK South East and our Azure SQL server is hosted in UK South

1 answer

Vijayalaxmi Kattimani 1,330 Reputation points Microsoft Vendor

2024-11-22T13:23:56.61+00:00
Hi @Johnm,

Welcome to the Microsoft Q&A Platform! Thank you for asking your question here.

We would like to inform you that, when you create a new server in Azure SQL Database a server-level firewall blocks all access to the public endpoint for the server. Connection attempts from the internet and Azure must pass through the firewall before they reach your server or database. Firewall rules can be of two types Server-level IP firewall rules and Database-level IP firewall rules.

Azure SQL Database creates a firewall at the server level for single and pooled databases. This firewall blocks connections from IP addresses that do not have permission. To connect to an Azure SQL database from an IP address outside of Azure, you need to create a firewall rule. You can use rules to open a firewall for a specific IP address or for a range of IP addresses.

Note:

Azure SQL Database communicates over port 1433. When you connect from within a corporate network, outbound traffic over port 1433 might not be permitted by your network firewall. This means your IT department needs to open port 1433 for you to connect to your server.

A firewall rule of 0.0.0.0 enables all Azure services to pass through the server-level firewall rule and attempt to connect to a database through the server.

Please refer to the below mentioned links for more information.

https://learn.microsoft.com/en-us/azure/firewall-manager/policy-overview

https://learn.microsoft.com/en-us/azure/azure-sql/database/firewall-create-server-level-portal-quickstart?view=azuresql

https://learn.microsoft.com/en-us/azure/azure-sql/database/firewall-configure?view=azuresql&source=recommendations

https://learn.microsoft.com/en-us/azure/azure-sql/database/troubleshoot-common-errors-issues?view=azuresql

https://learn.microsoft.com/en-us/sql/sql-server/install/configure-the-windows-firewall-to-allow-sql-server-access?view=sql-server-ver16

https://techcommunity.microsoft.com/blog/azurenetworksecurityblog/enhancements-to-the-azure-firewall-user-experience/4297129

I hope, This response will address your query and helped you to overcome on your challenges.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Please sign in to rate this answer.
Daniel Lewis 0 Reputation points

2024-11-26T10:29:13.1433333+00:00

This is an issue with SSMS adding the firewall rule automatically in SSMS not the portal
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

SSMS firewall rules error

1 answer

Your answer