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!!!