Showing posts with label Cannot connect to Azure SQL Server due to firewall issues. Show all posts
Showing posts with label Cannot connect to Azure SQL Server due to firewall issues. Show all posts

Friday, 28 July 2017

Cannot connect to Azure SQL Server due to firewall issues

Cannot connect to Azure SQL Server due to firewall issues




Today, when I tried to connect to an existing SQL Azure database (you can check my previous post here where I have showed how to create an Azure SQL Database) from my local SSMS(SQL Server Management Studio), I started getting the below error-
Cannot connect to Azure SQL Server due to firewall issues

Upon doing some research, I got to know that you need to add firewall rules in order to enable your Azure SQL Database for remote connections.


You need to follow the below steps to do that step by step-

1)      Log in to portal.azure.com.

2)      Once you are logged in to the Portal successfully then at the left hand side, you will see the button for SQL Databases-> click that

setting firewall rules in Azure SQL database

3)      Select the database for which you want to set the firewall rules and click overview-

setting firewall rules in client and portal for Azure SQL database






4)      You will be presented with all the details like the Resource Group, Status, Server name, Location, Subscription name, pricing tier etc of your Azure SQL Database. On the top of the screen, you will see “Set Firewall rules” option. You need to click on that option to set the firewall rules.

5)      Upon clicking “Set firewall rules” option you will be presented with the below screen-

set firewall rules in Azure SQL Database












6)      You need to give a rule name in the “Rule Name” Box and specify start IP and End IP of the client or clients from whom you want the connection to be established. If you want that only one IP should get access to connect to the Azure SQL Databases, then you can just keep the Start IP and End IP same. Otherwise you can specify a range for example- “X.X.X.1 to X.X.X.10”.

7)      Once you have entered the Start IP and End IP, you can click on Save button. Please note that it may take up to 5 minutes for the changes to take effect. You may receive a similar error which is copied below-
“TITLE: Connect to Server
------------------------------
Cannot connect to sqldbserver11.database.windows.net,1433.
------------------------------
ADDITIONAL INFORMATION:
Cannot open server 'sqldbserver11' requested by the login. Client with IP address '59.160.69.66' is not allowed to access the server.  To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range.  It may take up to five minutes for this change to take effect. (Microsoft SQL Server, Error: 40615)
Besides this, you will also need to enable SQL Port in your firewall setting. By default the SQL Server listens to port number 1433 but in some cases due to security reasons you can also see a port other than the default one. Below is the example to add 1433 and 1434 ports in Windows Firewall settings-
Microsoft Windows [Version 10.0.14393]
(c) 2016 Microsoft Corporation. All rights reserved.
C:\WINDOWS\system32>netsh advfirewall firewall add rule name="SQL Server Browser" protocol=UDP dir=in localport=1434 action=allow
Ok.
C:\WINDOWS\system32>netsh advfirewall firewall add rule name="SQL Server Engine" protocol=TCP dir=in localport=1433 action=allow
Ok.
8)      After adding the IP of my client machine in the firewall settings, I tried to connect Azure SQL Database from my local SSMS and I was able to connect to it.



So, We have seen that by adding firewall rules both in the portal and windows (client) we can connect to an Azure SQL database locally as well.