A day with .Net

My day to day experince in .net

Windows Azure Connect – Connect to local SQL Server database from azure using virtual network

Posted by vivekcek on April 1, 2012

What is Azure Connect?

By using azure connect you can create a virtual network between your local computer and web role running in azure.
My Situation.I want my web role to fetch data from my local sql server.I don’t want to use sql azure.

Steps

1. Prepare sql server to allow remote connection using TCP.

2. Install the azure connect software on your PC.

3. Get the activation Token.

4. Create a Web Role project.Configure virtual network connection for this role with activation token.

5. Publish the web role project to azure.

6. Configure Endpoint.

1. Prepare sql server to allow remote connection using TCP.
——————————————————————-
Open the SQL Server management studio.Login to your server.Please note that your sql server configured for sql server authentication.If sql server authentication is not configured, Login using windows authentication then enable login for ‘sa’. Now open the sql server configuration manager.Select SQL Server Network configuration.Then select protocols for MSSQLSERVER.Enable TCP.Under the tab ‘IP Addresses’ scroll to bottom and set port to 1433 for IPAll.

Sample Database.

Allow Remote Connections.

Opening SQL Server configuration manager.

Enable TCP For port 1433.

Double click on TCP.

Add FireWall rule(inbound) to allow connection to 1433 port.

Open Firewall Config.

Select inbound rule.And add new rule.

Select Port and click next.

Select TCP and Specific port 1433.

2. Install the azure connect software to your PC.
———————————————————

Now go to azure portal.Click on virtual network then click on install end point.Which will install a small software in your PC.This software contain some encrypted information for setting up virtual network.So you cant save this software and install future.That means each time you have to download it from azure portal.

Click on Install End Point.

From the pop up copy link to Internet Explorer which will download and install endpoint client.

Endpoint software is installing.

After installation an icon is shown in your task bar.

Now in portal you can see your system name is listed under virtual network.

3. Get the activation Token.
————————————-
Click the activation token link in azure portal.Save the token some where for future use.

4. Create a Web Role project.Configure virtual network connection for this role with activation token.
————————————————————————————————————-

In the Default.aspx add the code.

protected void Page_Load(object sender, EventArgs e)
        {
            string cnstr = @"Data Source=MYSOFT,1433;Initial Catalog=MyDB;Persist Security Info=True;User Id=sa;Password=vivek;MultipleActiveResultSets=True;";
            SqlConnection cn = new SqlConnection(cnstr);
            SqlCommand cmd = new SqlCommand("select * from Employee", cn);
            cn.Open();
            SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            GridView1.DataSource = dr;
            GridView1.DataBind();
            
        }

5. Publish the web role project to azure.
—————————————–

Create a package and upload that to azure portal.

6. Configure Endpoint.

After your role instance is successfully deployed and in ready state you can find your role is listed in virtual network section as shown below.

Now create a Group.

Refresh the endpoint client to check configuration is successful.

Final Result

SOURCE CODE

https://skydrive.live.com/redir.aspx?cid=12c9d813342f227a&resid=12C9D813342F227A!132&parid=12C9D813342F227A!131&authkey=!AEN5EqNeXbhP2YY

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s