Adding your Client IP to the Azure SQL server firewall
One of the first things that need to be done when you create a Azure SQL database is adding the Client IP address to the firewall of the Azure SQL server. By default all traffic between Azure services are allowed but traffic from another machine is disallowed. Connecting to the server with for example SQL Management Studio will result in the following error:
“Cannot open server [servername] requested by the login. Client with IP address [IP Address] is not allowed to access the server.”
Steps
When you open the Azure SQL Database within Visual Studio it will prompt you the question if you would like to add your Client IP to the firewall in order to access the database. If you prefer another tool like SQL Server Management studio you will have to configure this within the Azure Portal (as mentioned in the error):
- Open the Azure Portal: https://portal.azure.com.
- Click on Resource Groups and then the resource group of the SQL server.
- In the Resource Group blade click on the SQL server.
- Within the “Security” Category click on “Firewall”.
- Add your Client IP within this blade.
- Click on save to save the settings.
Until today I navigated to the website: https://www.whatismyip.com/ to retrieve my external IP and add it via the steps mentioned above but today I noticed another option (I think it already was there for quite some time). Within the firewall blade you can click on “Add client IP” to add your current client IP.
it worked!, very good article. Thanks!
Thanks man! Worked for me.
the public IP address did not work for me. i opened SSMS and it popped up a screen, with an IP address listed, saying add this IP address to the allowed IP list. When I added this particular IP, I was able to connect. This IP was different than my public IP address!! Any thoughts? I, for the life of me, not able to figure out where did SSMS get this IP address from. It is not showing up in my ipconfig /all too. Look forward to hear your thoughts.
It all depends on how the SQL system is setup. Besides that is can also be VPN related.
Well, let me throw this one out there becauses nothing I can find.
External access using MFA? I find the firewall setting in Azure for SQL PaaS overrides this but Microsoft documentation is so poor I cant find a definitive answer to this query. MFA is working on my network but not external. It goes straight in. If I remove the IP whitelist from the firewall it prompts me to add the IP and then fails.
I really wish Microsoft documentation was better so I searched Google and found nothing about this but from my testing it sounds like it is expected behaviour. I just cant rely on the likes of MS to verify.
Is there any way to add IPs from the azure command line?