Steps to connect to Sql server on Windows Azure Virtual Machine from local machine

Here are the detailed steps to connection Sql instance in a Windows Azure Virtual Machine, from your local machine, through SQL Server Management Studio.

Assume you have already had Sql Server installed on the VM, and SSMS installed on your local machine.

1, Configure your virtual machine’s firewall to allow connection to port 1433

a)      Go to windows firewall, launch Advanced Settings;

b)      Select Inbound rules on the left, and click “New Rule…” on the right;

c)       Select Port in New Inbound Rule Wizard, click Next;

d)      Make sure TCP is selected, and put 1433 in Specific local ports text box, click Next;

e)      Select Allow the connection, click Next;

f)       Select Domain, Private and Public, click Next;

g)      Put whatever name in the Name field, click Finish;

2, Create an endpoint for the VM, to forward port 1433 to 1433

a)      To go the new Windows Azure portal, select the VM;

b)      Click “ADD ENDPOINT” button on the bottom;

c)       Select Add endpoint and click Next;

d)      Put following parameters in the Specify endpoint details dialog:

NAME: <whatever you want>

PROTOCOL:TCP

PUBLIC PORT:1433

PRIVATE PORT:1433

e)      Click the tick button to finish;

3, Connect to Sql Server via Sql Server Management Studio

a)      Launch Sql Server Management Studio(ssms);

b)      In the Server Name, input your virtual machines’ DNS, like:yourvmname.cloudapp.net

c)       Select proper Authentication, user and password. You probably need to enable Sql Server authentication in you virtual machine.

d)      Click Connect;

Troubleshooting:

1. Make sure Sql Server has enabled TCP/IP, in SQL Server Network Configuration. This can be done in Sql Server Configuration Manager.
2. Make sure Sql Server had enabled remote connection. You can double check by:

a)      Double click the server in SSMS, and select properties;

b)      Select Connections on the left;

c)       Make sure the “Allow remote connections to this server” is checked.

1. Instead of opening connection to port 1433, create an inbound rule, to allow sqlservr.exe through the firewall. Sqlservr.exe usually located under C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn;

from : http://huberyy.wordpress.com/2012/08/03/steps-to-connect-to-sql-server-on-windows-azure-virtual-machine-from-local-machine/