emote-access-for-azure-vms-using-powershell

emote-access-for-azure-vms-using-powershell.ps1

Gather SMO exception details
function SmoException
{
    $err = $_.Exception
    while ( $err.InnerException )
    {
        $err = $err.InnerException;
    };
    Write-Host $err.Message;
}

Import-Module SQLPS -DisableNameChecking
Import-Module NetSecurity
Import-Module Azure

$SQLBrowserServiceName = "SQLBrowser"

#Don't forget to setup your azure configuration!

Try
{
    #The sql browser service must be installed and running
    if ((Get-Service $SQLBrowserServiceName -ErrorAction Stop | Where-Object {$_.status -eq "running"}).count -eq 0 ) 
    {
         Write-Host "Starting $SQLBrowserServiceName" 
         Start-Service $SQLBrowserServiceName -ErrorAction Stop
    }

    $ManagedComputer = New-Object 'Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer'

    # Gather data
    foreach ($ServerInstanceMC in $ManagedComputer.ServerInstances)
    {
       foreach ($ServerProtocol in $ServerInstanceMC.ServerProtocols | Where {$_.Displayname -eq 'TCP/IP'} )
       {
           foreach ($IPAddresse in $ServerProtocol.IPAddresses | Where {$_.Name -eq 'IPAll'})
           {
               $ServerInstanceConfigOptions = @{
                'ServerInstance'= $ServerInstance.Name    
                'Port'= $IPAddresse.IPAddressProperties | Where {$_.Name -eq 'TcpDynamicPorts'} | Select Value
               }

              $ServerInstance = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList  ($env:COMPUTERNAME + '\' + $ServerInstanceMC.Name)

              #Enable remote Option RemoteAccess
              $ServerInstance.Configuration.RemoteAccess.ConfigValue = 1;
              $ServerInstance.Configuration.Alter();

              #Create firewall rule
              New-NetFirewallRule -DisplayName (“SQL Server ” + $ServerInstance.Name) -Direction Inbound –Protocol TCP –LocalPort $ServerInstanceConfigOptions.Port.Value -Action allow

              #Parameter Set: NoLB!
              Get-AzureVM -ServiceName $env:COMPUTERNAME -Name $env:COMPUTERNAME |  Add-AzureEndpoint -Protocol tcp -LocalPort $ServerInstanceConfigOptions.Port.Value -PublicPort $ServerInstanceConfigOptions.Port.Value -Name (“SQL_Server_” + $ServerInstance.Name.Replace('\','_')) | Update-AzureVM

           }
       }
    }
}
Catch 
{ 
    SmoException $_.Exception
}