I am able to connect to SQL Azure database through SSMS (SQL server Management studio) 2008 R2 running on my machine. What I want to do is to tinker with the configuration and create a situation where I am not able to connect to SQL Azure DB through SSMS 2008 R2. Simple, eh?! Destructive – oh yeah!
Now that I know I can connect to SQL Azure through SSMS, let’s tinker it so that I am not able to connect to SQL Azure.
First thing we are going to do is block our very own machine through the great wall of china SQL Azure Firewall. So connect to the Master database and let’s see the firewall rules:
you can see that, my SQL Azure DB allows connection from all possible IP ranges. FYI: this is my test account. In big bad world, it’s not a recommended practice. be careful. Anyways, since we do not want ourselves to get connected to the SQL Azure DB, we will go and delete the firewall rule named “all” that allowed IP range 0.0.0.0 to 255.255.255.255 to connect to SQL Azure DB.
so I ran this command on my Master database via principal server login:
exec sp_delete_firewall_rule N’all’;
go
Now, try connecting to the SQL Azure DB again. you will get an error message: “cannot connect to [randomstring].database.windows.net”
So remember:
Now I enable “ALL IPs” to access the server and here I try reconnecting to SQL Azure:
It runs! Now, let’s do some other type of tinkering and see what are the other ways in which we can spoil our connection to SQL Azure DB from SSMS.
Open, SQL server configuration manager –> SQL server network configuration –> Disable TCP/IP.
When you alter the status from “enabled” to “disabled”, you are then asked to restart the SQL server. Please do so!
Now, try reconnecting to the database – and thou shall be greeted with an error:
Note that to connect to SQL Azure DB, we should make sure that port 1433 (TCP IP) is open for outbound access
Thus remember:
There’s couple of more ways:
TURN OFF YOUR INTERNET CONNECTION. Just kidding!
And Uninstall the latest version of “recommended” SSMS and install the SSMS from pre-R2 era. Just kidding again!
Note:
*THE STUNTS ARE PERFORMED BY EXPERTS – PLEASE DO NOT TRY THIS AT HOME PRODUCTION ENVIRONMENT*
BTW the goal of the article was to show you that to connect to SQL Azure, you should have
1) The recommended version of SSMS
2) Correct SQL Azure firewall rule settings
3) TCP/IP enabled on local machine
Related Posts:
http://parasdoshi.com/2011/07/06/adding-deleting-updating-the-sql-azure-firewall-rules-via-ssms/
SQL Azure Firewall: An unique security paradigm of SQL Azure << Paras Doshi
Bravo! Excellent article. I love the touch of humor.
I have not been been guilty of using the two ways you outline to shut myself out but I have done one that is just as effective: forget to give my user permissions to any objects in the database!
haha! That’s true – it’s one more way!
Excellent post I wish I could find a way to change the firewall settings in an automatically created SQL Azure database (as the result of creating an Access 2013 web app). The sys.database_firewall_rules and a few others are locked. In addition the sys.sp_set_firewall_rule is not present.