YouTube Video (Better Quality)
Information good as of 4/29/2015 and is subject to change!
We get questions regarding client access to someone’s Azure SQL Database. I wanted to go through and show what options you have to configure the firewall to allow access to known clients. To start with, you may see an error, like the following, when you try and connect.
Cannot open server ‘<servername>’ requested by the login. Client with IP Address ‘X.X.X.X’ 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)
Existing Portal
The existing portal is located at https://manage.windowsazure.com . From here we can go to SQL Databases. Then select the database we want to give access to, or the server directly. The firewall rules are actually on the server itself, but you can get there from either spot.
In the database, under quick glance, select Manage allowed IP Addresses .
This will take us to the Server – Configure Tab. This will list out any existing rules you have configured. This will also show you the IP Address you are coming from, and you can quickly add that by clicking Add to the Allowed IP Addresses . You can also add a range of IP addresses to allow.
Preview Portal
We also have the preview portal that we can use as well. This is located at https://portal.azure.com . You want to use the Preview Portal if you have a v12 database! From that portal, you can click on Browse, and then SQL Databases.
NOTE: Within the Preview Portal, everything expands to the right.
Then you can select the server you want to modify.
On the Server screen, you can either click on the Settings icon, or click on Show firewall settings .
If you clicked on Settings , just click on Firewall . This will bring you to the screen that Show firewall settings would bring you directly to.
The Firewall settings screen looks similar to the existing portal. However, it doesn’t show you the IP you are coming from. But you can add new rules here the same way you could in the other. Just provide a name and a Start Address along with an End Address. If it is just a single IP Address you will use the IP for both start and end. Otherwise you can add a range.
Then Save when you are done.
Stored Procedure
We can also used stored procedures to alter the firewall settings. We have documentation on how these work. These procedures will go against the Master database, so your account will need access to that, otherwise they won’t work.
sp_set_firewall_rule
https://msdn.microsoft.com/en-us/library/dn270017.aspx
Here are some examples of what this would look like:
--create a firewall rule for a single IP address
exec sp_set_firewall_rule N'DevComputer','206.63.251.3','206.63.251.3'--create a firewall rule for Microsoft services and Windows Azure services
exec sp_set_firewall_rule N'MicrosoftServices','0.0.0.0','0.0.0.0'--delete a firewall rule
exec sp_delete_firewall_rule N'DevComputer'--get a list of the firewall rules
select * from sys.firewall_rules
PowerShell
You can install the Windows Azure PowerShell components which will include the Azure SQL Database cmdlets. The following documentation says how to install this. This comes from the Web Platform Installer .
How to install and configure Azure PowerShell
http://azure.microsoft.com/en-us/documentation/articles/powershell-install-configure/
There are two PowerShell commands we can use to work with the Database Firewall rules.
Get-AzureSqlDatabaseServerFirewallRule
https://msdn.microsoft.com/en-us/library/dn546731.aspxNew-AzureSqlDatabaseServerFirewallRule
https://msdn.microsoft.com/en-us/library/dn546724.aspxRemove-AzureSqlDatabaseServerFirewallRule
https://msdn.microsoft.com/en-us/library/dn546727.aspxSet-AzureSqlDatabaseServerFirewallRule
https://msdn.microsoft.com/en-us/library/dn546739.aspx
You can also run the help command to get the list of these assuming everything was installed properly. Also, be sure you are using the Microsoft Azure PowerShell prompt which will load the cmdlets automatically for you.
get-help *-AzureSqlDatabaseServerFirewall*
We can then get a listing of the rules that are currently in place for our server.
Get-AzureSqlDatabaseServerFirewallRule -servername guyinacube | select -Property RuleName, StartIpAddress, EndIpAddress
You may only see the AllowAllWindowsAzureIps . It will have the IP Address of all zeros. This is to allow anything within the Windows Azure Datacenter access to the server. You can remove this if you want. This is just a Yes/No option within the portal.
We can then add a Firewall rule using New-AzureSqlDatabaseServerFirewallRule .
New-AzureSqlDatabaseServerFirewallRule -ServerName guyinacube -RuleName "MyClient" -StartIpAddress 206.63.251.3 -EndIpAddress 206.63.251.3
Find your IP Address
When adding a firewall rule, you will need to know the IP Address that is external facing. This is what will hit the Azure Data Centers. It probably won’t be the IP Address you see from an ipconfig output. To get this address, you can see it from the Existing portal. The preview portal doesn’t show it. There are websites you can go to that will tell you as well.
What is my IP
You can go to http://whatismyip.com and that will show you your IP address.
Speedtest
Another site you can use is http://speedtest.net . This is meant more to show you your upload and download speeds, but it will also show you your public IP address as well.
Adam W. Saxton | Microsoft SQL Support - Escalation Services
@GuyInACube
|
Mixes
|
YouTube
|
Facebook.com\guyinacube