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;

Refer to https://www.windowsazure.com/en-us/manage/windows/common-tasks/sql-server-on-a-vm/

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

Assume you have already had Sql Server installed on your local machine, and SQL Server Management Studio installed on Windows Azure Virtual Machine.

1, Install Local Endpoint on both your local machine and Windows Azure VM, and add them to the same group. Steps can be found here.

2, Enable Sql Server authentication on your local machine;

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

b)      Select Security on the left;

c)       Under Server authentication, make sure “SQL Server and Windows Authentication mode” is select;

3, Open inbound connection to port 1433, on your local machine;

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;

4, Connect to Sql Server from VM

a)      Launch Sql Server Management Studio(ssms);

b)      In the Server Name, input your local machine’s name;

c)       Select proper Authentication, user and password (you probably need to select Sql Server authentication).

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;
  2. Currently local endpoint will fail if you install it on Windows 8. To workaround this, save the installer on you disk, open the property dialog, go the Compatibility tab, select “Run this program in compatibility mode for:”, and in the select Windows 7 in the dropdown list. Then run the installer.

Set up a FTP server in Windows Azure Virtual Machine with FileZilla (no coding)

Once you’ve setup a virtual machine in windows azure, you are likely need to transfer files between your local machine and the virtual machine in the cloud. You may need to upload software, tools or even database files to the VM.

The most convenient way to do that is to have a FTP server on the VM, so you can do uploading and downloading from anywhere.

However, set up a FTP server in the cloud is not as easy as it looks like. The key challenge is that FTP sessions need to establish two connections to transfer a file:

  • Control connection. Used to send control between server and client. Established when client connect to server on port 21;
  • Data connection. Used to transfer files. In active mode, client randomly picks a port and server will make the connection; in passive mode, server randomly picks a port, and client makes the connection.

As you know, VM does not have public IP address, to make the virtual machine accessible from outside of cloud, you need to create an endpoint for each port you want to connect, to forward the port from public gateway to your VM.

That means, if you want to connect to port 21 of the VM, you need to create an endpoint in the windows azure portal, to forward port 21 to vm’s port 21. That’s relative easy, what’s difficult is the data channel, because we don’t know the exact ports beforehand.

So we have to limit the ports that FTP server uses for data channel to a small range, and for each port in the range, create an endpoint, so that client and server can establish the data connection.

Also, don’t forget firewall. Make sure the firewall on the server allows connection on the ports (21 and ports for data channel).

Below are the detailed steps (by default the actions are done on windows azure virtual machine, not your local machine):

1, Download FileZilla server from http://filezilla-project.org/download.php?type=server;

2, Install FileZilla server, accept all the default options;

3, Add a user

3.1 Launch FileZilla Server interface;

3.2 go to menu Edit->Users;

3.3 Click the Add button in the users group on the right side;

3.4 Select Shared folders in the Page group, on the left;

3.5 In the Shared folders, click the Add button, select a folder that ftp user can access;

3.6 Click OK to close the dialog;

More info http://wiki.uniformserver.com/index.php/FileZilla_Server:_Basic_Configuration

Now you should be able connect to the FTP server from within the Virtual Machine, use the user you just created.   But it’s not accessible outside of the VM, like from your desktop.

4, Configure firewall to allow “FileZilla Server.exe”, not “FileZilla Server Interface.exe”, through;

5, Configure your virtual machine to allow ftp connection

5.1 Go to your windowsazure.com portal;

5.2 Select your virtual machine, and click ENDPOINTS on the top;

5.3 Click ADD ENDPOINT button on the bottom;

5.4 Put following parameter in to popped dialog

NAME:<whatever you want>

PROTOCAL:TCP

PUBLIC PORT:21

PRIVATE PORT:21

More info http://www.windowsazure.com/en-us/manage/windows/how-to-guides/setup-endpoints/

Now you should able to log in to the FTP server from anywhere, but still unable to retrieve directory listing, because you can’t establish the data connection.

6, Configure data channel port in FileZilla server

6.1 Launch FileZilla Server Interface;

6.2 Go to menu Edit->Setting;

6.3 Select Passive mode setting on the left

6.4 check “Use custom port range”, and input a port number greater than 1024 for both low and high number, let’s say put 60000. (Limit the port range because we don’t want to create hundreds or thousands of ENDPOINT in windowsazure portal);

6.5 Click OK to close the dialog;

7, Configure firewall to allow inbound connection from the port Configured in step 6;

8, Add another ENDPOINT in the windowsazure portal for the data channel port you configured in step 6, parameters:

NAME:<whatever you want>

PROTOCAL:TCP

PUBLIC PORT:60000

PRIVATE PORT:60000

9, In the IPv4 specific group, select Retrieve external IP address from:

filezilla_passive_setting

filezilla_passive_setting

Done!

Note: windows command line ftp.exe does not support passive mode, so recommend FileZilla client to connect from outside of the virtual machine.

Hello world!

Welcome to WordPress.com! This is your very first post. Click the Edit link to modify or delete it, or start a new post. If you like, use this post to tell readers why you started this blog and what you plan to do with it.

Happy blogging!