SQL Server Management Studio

How to avoid Men in Middle attack when you try establishing a new connection to SQL Azure via SSMS

Standard

Fact: All connections with SQL Azure are SSL encrypted. No exception.

Then what am I talking about? Why do I need to worry about Men in Middle attack now?

Turns out there’s a way, Men in Middle attack can happen – Not on your established connection But when the client first tries to establish a new connection. And in this blog post, we are going to see how to avoid Men in Middle attack when you first try establishing a new connection via SSMS.

BTW: I have not researched on how to do it from developers perspective, but if any developer is reading this and has figured it out – it would be great if you can share it with us via any communication means of your choice. (Thanks a lot – if you do so).

[Update: See Bottom of the post]

Any-who. Back to SSMS.

All you got to do to avoid this improbable situation to happen is just check the “Encrypt connection”in the connection property when you try establishing a *new* connection to SQL Azure.

That’s it.

if the client requests encryption from the beginning then our connection is not susceptible to Men in Middle attach while the client is negotiating with the server for encryption.

Categorize under “Best practice” if  you wish to.

 

Update: Developers, When you connect to SQL Azure using ADO.Net – please do not forget to set 1. Encrypt = TRUE and 2. TrustServerCertificate = False  to avoid man in middle attack.

 

And Thanks to Herve Roggero for his response on my (poorly framed) question on StackOverflow. I am glad, he understood what I meant!  Thanks sir 🙂

My article on “Tuning SQL Azure databases – Part 2/2” got published in SolidQ Journal’s september edition

Standard

Part 2/2 of “Tuning SQL Azure Databases” got published. The aim of the series was to show you the options that are available to tune a SQL Azure database. In part two, I  discuss on how to use information made available through dynamic management views (DMV’s) available in SQL Azure; while the first part focused on examining execution plans and tuning the database based on that information. Here are the links to download the magazine:

Part 2: http://www.solidq.com/sqj/Pages/2011-September-Issue/The-SolidQ-Journal-September-2011.aspx

Part 1: http://www.solidq.com/sqj/Pages/2011-July-Issue/The-SolidQ-Journal-July-2011.aspx

Top two ways you can screw up the established SQL Azure connection from SSMS 2008 R2 running locally

Standard

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.

image

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:

image

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”

image

So remember:

image

Now I enable “ALL IPs” to access the server and here I try reconnecting to SQL Azure:

image

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:

image

Note that to connect to SQL Azure DB, we should make sure that port 1433 (TCP IP) is open for outbound access

Thus remember:

image

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://social.technet.microsoft.com/wiki/contents/articles/sql-azure-connectivity-troubleshooting-guide.aspx

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