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([s07ing] $SQLServer)
{
            07y
            {
                        $Connection = New-Object System.Data.SQLClient.SQLConnection
                        $Connection.ConnectionS07ing = "server=mycloudservice.cloudapp.net,57500;Database=Master;User Id=myuser;Password=mypassword;"
                        $Connection.Open()
                        return $07ue;
            }
            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 07ying 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!!!

Comments

Post a Comment

Popular posts from this blog

The request has both SAS authentication scheme and 'Bearer' authorization scheme. Only one scheme should be used

Image
While doing testing after doing a POC on Securing Logic App with Azure Active Directory authentication , where I have put logic app behind APIM and before passing the request to logic app, apim does validation of the token.  I was encountered with an error "The request has both SAS authentication scheme and 'Bearer' authorization  scheme.  Only one scheme should be used." Why it happened After validating the token which is part of the header i.e. Authorization, APIM forwards the request as it is to backend. As  Logic app is configured as  back end , it's url already consist of SAS signature plus the request also has Authorization section and this is the problem. By default every request endpoint on a logic app has a Shared Access Signature (SAS) in the endpoint's URL, which follows this format: https://<request-endpoint-URI>sp=<permissions>sv...
Read more

Getting Started with Logic Apps - AS2

Image
What is AS2? Every enterprise requires some kind of product, service or counselling from another enterprises thus there happens a B2B(business to business) communications, some of the communication is unique whereas most of the communications are common for most of the enterprises e.g., Purchase Order. But every enterprise has there own way(format) of sending the messages, leading to difficult management when number of partners increased.  Considering this the Enterprise leaders decided to have some standards/rules defined across the communication leading to B2B protocol standards which provide guidelines for trading partners to follow when conducting business between enterprises. EDI X12, EDIFACT, TRADACOM etc. AS2 stands for Applicability Statement 2 and is a B2B messaging protocol used to transmit Electronic Data Interchange (EDI)/Business documents from one organization to another. So EDI standards define how to format data and AS2 specifies how to securely transport...
Read more

How to Debug and Trace request in Azure APIM - Portal, Postman, RequestBin

Image
Introduction  APIM is a great option to expose API's with out of box features for applying restrictions, preprocessing, postprocessing etc. You can leverage existing API's,  by importing it and it gets added as a new API with all the operations associated with it. And based on the requirements you  apply policies at the stage/level (Inbound,Backend/Outbound) and you are ready to use the API. Before it is shared, we do some testing to make sure everything is working as per the expectation and this is where Debugging and Tracing request becomes important. APIM does provide a way to Trace a call with the help of  Ocp-Apim-Trace http header. So whichever request/call is to be traced, it needs to include this in header  with the value set to true and it has dependency on another header, so that also needs to be passed i.e.  Ocp-Apim-Subscription-Key Note: The api on which Tracing is to applied , it requires the subscription key to be en...
Read more