MS Azure : Demo of Firewall and Authentication

Demo of Firewall and Authentication

To check the current firewall rules at Azure SQL Database level follow below steps:

Step 1 : Login to Azure Portal https://portal.azure.com with your credentials.

Step 2 : Once you Logged In successfully, portal will look like below snapshot.
From the left pane, you have to find out SQL Database on which you would like to check Firewall rule as highlighted with red block in below snapshot.

Once you clicked on firewall, portal will look like below snapshot:
There is No firewall rules configured.

In the same way, you can check Server level Firewall rules.

Step 3 : Now lets connect with Azure Database via SQL Server Management Studio
Connect with database via SSMS , Server Name : demo.database.windows.net Once clicked Connect

It will prompt New Firewall Rule window saying that
" Your client IP address doesn't have access to the server. Sign in to an azure account and create new firewall rule to enable access."
Click on Sign in...
Provide your access and it will connect to an Azure account.

Once Sign in, it will again prompt New Firewall Rule and give you two options:
1. Add my client IP
2. Add subnet IP range
As you can see in below snapshot: choosing to add my client IP and clicked OK

Finally we are connected to demo Azure SQL Instance through SQL Server Management Studio by adding client IP at SQL Server level.
To view the same in Azure Portal, go to SQL server and click on Firewall:
you can see the IP is added under Firewall rule.

Step 4 : Now lets create firewall rule at DB level by adding Client IP at database level.

Execute below command through SSMS to set firewall rule at database level:

use demodb
Go
EXEC sp_set_database_firewall_rule @name = N'ClientFirewallRule',
@start_ip_address = '10.xxx.xxx.115', @end_ip_address = '10.xxx.xxx.115'

Now added firewall rule at Server level and To view what all firewall rules are added at DB level, query below commands:

SELECT * FROM sys.firewall_rules

SELECT * FROM sys.database_firewall_rules

Now after adding firewall rule at database level then lets Delete firewall rule at server level to test the db level rule functionality.

Step 5 : Now lets first test the connectivity for master db by choosing database through Options from SSMS connection window.

Click on Options and change database to master as shown below

Now once you click to Connect Button, It doesn't allow to connect directly and prompt you to add New Firewall Rule for master db, as firewall rule were added only for demodb database.

Now lets finally test the connectivity for demodb:

Click Connect, it will directly connect you to demodb database.

That's it!! This is how you can keep the tight security and create firewall rule at DB and Server level.