Connecting to SQL Server Virtual Machine on windows Azure from another windows Azure virtual Machine or on-premise computer – Using simple Powershell Script

With the new offering of IaaS on Azure, you can provision SQL server on windows Azure virtual

machine. Once SQL Server virtual machine provisioned you need to complete the configuration steps mentioned here - http://www.windowsazure.com/en-us/documentation/articles/virtual-machines-provision-sql-server/#SSMS 

Now same link suggests using SSMS (SQL Server Management Studio) if you wish to connect from another computer to SQL Server Azure virtual machine. What if you don’t have SQL Server management studio installed? What if the computer you wish to connect is Azure Virtual Machine and you don’t wish to install management studio on it? In such scenario the best option is to use powershell script to check the connectivity to SQL Server Azure Virtual Machine. 

Below I have written a simple powershell script which you can run from any machine to check the connectivity with Azure Virtual Machine SQL Server.

First open the powershell from programs menu as shown below –

Then run following script to set execution policy. This will ask you Y or N. Select Y to proceed. 

#set execution policy

set-executionpolicy remotesigned 

After this copy and paste the following script in powershell window. Make sure that you replace the Server name below yellow marked and port number, user name and password. 

function IsSQLDBAvailable([string] $SQLServer)

{

            try

            {

                        $Connection = New-Object System.Data.SQLClient.SQLConnection

                        $Connection.ConnectionString = "server=mycloudservice.cloudapp.net,57500;Database=Master;User Id=myuser;Password=mypassword;"

                        $Connection.Open()

                        return $true;

            }

            catch [System.Exception]

            {

                        return $false;

            }

}

$SSODB = $False

while ($SSODB -eq $False)

{

$SSODB = IsSQLDBAvailable ("mycloudservice.cloudapp.net,57500")

            start-sleep -s 2

            write-host "Waiting for SQL DB ..."

}

write-host " Connected to SQL server - $SSODB `r`n"

 

If the connectivity to SQL server is successful then you will receive message as  -

Connected to SQL Server – True.

If the machine is not able to connect to SQL Server Azure Virtual  machine then you will keep receiving message as –Waiting for SQL DB….”

This is the simplest way by which you can check the connectivity to SQL Server on Azure Virtual Machine. And best part is you don’t have to install SSMS.

Note - If you are trying to connect to SQL Azure Virtual Machine from the computer which is part of your company’s or corporate network then for security purpose the non standard ports are blocked (like 57500 mentioned in the above configuration link.). In that case you may not be able to connect to SQL Server Virtual machine using above powershell or SSMS. Best way is configure the TCP endpoint of SQL Server Azure Virtual Machine on public port as 80 and private port as 1433. Usually port 80 is not blocked in any organization. Hence you will be able to test the connectivity of Azure Virtual Machine SQL Server on 80 port. 

Hope this helps.

Cheers…

Happy Connecting!!!

ASP.NET cookies in Windows 7

In ASP.NET we create cookies using following code –

 

//add cookie

            HttpCookie ssoCookie = new HttpCookie("SSOCookie");

            ssoCookie.Value = loginMainControl.UserName;

            ssoCookie.Domain = “.mydomain.com";

            ssoCookie.Expires = DateTime.Now.AddMinutes(5);

            Response.Cookies.Add(ssoCookie); 

Now when we redirect, where are the cookies stored in Windows 7 created in ASP.NET?

Before this let’s understand the cookie type we create above. When we specify the Expiry for a cookie means we are creating persistent cookie. That means my cookie will expire only after provided time span and not on my browser close. If I close my browser the cookie will still exist.

If we don’t specify expiry while cookie creation means by default the cookie will be deleted on browser close. This cookie is called as session cookie. Session cookies are always stored in browsers memory and hence they cannot be viewed on physical folder on Windows 7.

Now let’s you have create persistent cookie then where is the location of cookies in windows 7?

Open Run window and type – “shell:cookie” (without quotes).

Startup page or default page for Azure Web Role

Right now if you have a web role hosted on azure and you want your .aspx page to be displayed when user hits to root url of *.cloudapp.net then you don’t have any choice to do it from Windows Azure Management Portal.

However this setting can be configured from web config file before deployment to Azure. Under <configuration> section add following snippet tag –

<system.webServer>

    <defaultDocument>

      <files>

        <clear/>

        <add value="Login.aspx"/>

      </files>

    </defaultDocument>

  </system.webServer> 

That’s it! You are done. Now when user hits to root url of cloud application, he /she will be redirected to page you have set up.

Hope this helps.

Cheers…

Happy clouding!!!

Definitive steps to configure MSDTC on Azure virtual machines – without need of domain controller

MSDTC – Microsoft Distributed Transaction.

I had requirement to configure Azure Virtual machine with SQL Server 2008 R2 and One Azure Virtual machine as an application server having few sites hosted on it. Azure virtual machine app server hosted application should be able to connect to SQL server and run SQL queries with transactions. Unfortunately transaction queries were not working in my environment set up.

The problem is, for transaction to work between SQL server and application server you need to have MSDTC to be configured on both machines. Everywhere I read through many posts that for configuring MSDTC, settings related to AD DC (domain controller) is required. However my azure virtual machines were not in any domain neither any one of them was treated as DC.

Hence I tried to configure MSDTC on azure virtual machines without domain configuration and it was successful.

Following steps will guide through the steps required to enable MSDTC on Azure virtual machines. Please note that the azure virtual machines do not have any domain configured on them neither they are not part of any domain.

I have 2 azure virtual machines with following configuration –

1.     SQL Server 2008 R2

2.     Windows Server 2008 R2 – call it as app server

Now I need to configure MSDTC on SQL server and app server.

At first you need to configure Azure virtual network as per the guidelines mentioned at this post.

http://www.windowsazure.com/en-us/documentation/articles/create-virtual-network/ 

Then provision Azure virtual machines with above configuration using the steps mentioned at this post in the virtual network you created above.

http://www.windowsazure.com/en-us/documentation/articles/virtual-networks-add-virtual-machine/ 

Now RDP to both Azure Virtual machines from the steps mentioned in this post.

http://www.windowsazure.com/en-us/documentation/articles/virtual-machines-windows-tutorial/#logon 

Then on SQL server make configuration settings mentioned on this post - http://www.windowsazure.com/en-us/documentation/articles/virtual-machines-provision-sql-server/#SSMS

Then log in to App server and follow below steps.

***MSDTC configuration***

Select Open Control Panel -> System and Security -> Allow a program through windows firewall option as shown below.

Automated deployment of .rdl files to SQL Server 2012 Reporting Services using c# and Reporting web Service of SSRS 2012

I was searching for automated deployment of .rdl [report definition language] files on SSRS reporting server 2012. Everywhere I found the reporting service example which was prior to 2012. Following code illustrates how we can deploy .rdl files to SSRS 2012. 

SSRS 2012 report service has URL same as 2010. Therefore the report service URL will be as follows –

http://ServerNameHavingSQL2012:PortNumber/reportserver/reportservice2010.asmx

If you have hosted reporting service on default port 80 then you don’t need to provide PortNumber in above URL. I have written a class to make automated deployment of .rdl files. Let’s call it as RDLDeployer. I have added a public method which takes string parameter. This string parameter can be the path of .rdl file to be deployed on SSRS 2012.

First add reference of SSRS web service in your application. Then use following code to deploy the .rdl file on report server 2012.

Steps are as follows –

How to call private method of one class in another class in C#

Sometimes you may want to call the private method declared in one class in another class in c#. Technically it is not possible to access private members of a class outside the class in c#. Either you will need to modify the access specifier of the class member from private to something else.  However if say you are not allowed to change the access specifier of private method but still want to provide a mechanism to enable calling of private method in another class? How can w achieve this in c#? It is very well possible in C# with the help of Delegates. 

Delegates are nothing but pointer to method. If you want to get value of private variable, we can declare a public property similarly if you want to call private method outside the declaring class we can use public property returning delegate (or pointer to the method).

Following construct depicts how delegates can be used to access private method outside a class. Let’s say I have a class A having private method as below –