Windows Azure SQL Database : How to manage your firewall rules using SQL Commands

  • [アーティクル]

 

You can manage your Windows Azure SQL Database firewall rules using SQL Commands.

For a general overview of securing your Windows Azure SQL Database please check my previous article : Secure Your Windows Azure SQL Database.

There are two types of Windows Azure SQL Database firewall rules

1. Server-Level Firewall Rules

2. Database-Level Firewall Rules

You can manage Server-Level or Database-Level using SQL Commands.

You can run these commands using :

  • SQL Server Management Studio
  • Visual Studio
  • Application or Web Site
  • Windows Azure SQL Database Management Portal
  • etc

 

You can read my previous article How to create Windows Azure SQL Database for a quick overview of how you can connect to Windows Azure SQL Database using SQL Server Management Studio or Visual Studio.

 

Depending to firewall rule type you should run different types of scripts in different databases.

 

Server-Level Firewall Rules : These rules are effective for all your databases in your database server. You can allow IP addresses allowed to connect to your server. It is also possible to allow your Windows Azure cloud services to access to your database server. These rules are stored in master database.

In order to create, delete or view Server-Level firewall you should run your commands in master database with a server-level principal login.

 

 

 -- view firewall settings
select * from sys.firewall_rules

-- enable Windows Azure connections
exec sp_set_firewall_rule N'Allow WA','0.0.0.0','0.0.0.0'

-- create new firewall setting for only IP 192.168.1.1
exec sp_set_firewall_rule N'SingleIPFW','192.168.1.1','192.168.1.1'

-- create new firewall setting for IP Range 192.168.1.1 - 192.168.1.255
exec sp_set_firewall_rule N'IPRangeFW','192.168.1.1','192.168.1.255'

-- create new firewall setting for access to all
exec sp_set_firewall_rule N'AllFW','0.0.0.0','255.255.255.255'

-- update firewall setting to also allow IP 192.168.1.2
exec sp_set_firewall_rule N'SingleIPFW','192.168.1.1','192.168.1.2'

-- Remove 'SingleIPFW' firewall setting
exec sp_delete_firewall_rule N'SingleIPFW'

Database-Level Firewall Rules : Database-Level Firewall rules enable clients to access your databases individually. These rules are created per database and stored in the database itself including master database.

These scripts should be run in the database itself and syntax is almost the same with Server-Level Firewall Rules.

 

 -- view database-level firewall settings
select * from sys.database_firewall_rules

-- create new database-level firewall setting for only IP 192.168.1.1
exec sp_set_database_firewall_rule N'SingleIPDBFW','192.168.1.1','192.168.1.1'

-- update database-level firewall setting to also allow IP 192.168.1.2
exec sp_set_database_firewall_rule N'SingleIPDBFW','192.168.1.1','192.168.1.2'

-- Remove database-level firewall setting
exec sp_delete_database_firewall_rule N'SingleIPDBFW'

 

 

Start using your free Windows Azure trial Windows Azure Free Trial