Lesson Learned #204: Elastic Query and firewall issues

[Jose_Manuel_Jurado]

Today, I worked on a service request that our customer needs to add the IP of Azure SQL node that is executing any elastic query. 

We have the following situation:

• In the server $serverSource and database $DbSource we have a table called dummy that is an external table that, using elastic query is connecting to $serverTarget and other database.
• As the Azure SQL firewall has not defined the IP address of the SQL Azure node that is running the query they are getting the following error: "Exception calling "ExecuteNonQuery" with "0" argument(s): "An error occurred while establishing connection to remote data source: [Microsoft][ODBC Driver17 for SQL Server][SQL Server]Cannot open server 'servername' requested by the login. Client with IP address 'xxx.xxx.xxx.xxx' is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot open server 'servername' requested by the login. Client with IP address 'xxx.xxx.xxx.xxx' is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect."

Why?:

• This issue is explained in this article

Solution proposed as an example: 

• Connect to the server $ServerSource and database $DbSource
• Run the query "select * from dummy". This table is the external table that makes a reference.
• If we obtain an error:
  • Obtain the IP of the SQL Azure Node. 
  • Connect to the server $ServerTarget and database master
  • Run sp_set_firewall_rule to add this IP
• The next connection will be fine and it won't return any error.
• As a note, of course, we need to maintain the number of IP added.

$serverTarget = "ServerNameThatHasTheReferenceTable.database.windows.net" 
$userTarget = "UserNameThatHasTheReferenceTable" #UserName parameter  to connect
$passwordTarget = "PasswordThatHasTheReferenceTable" #Password Parameter  to connect
$DbTarget = "master" #DBName Parameter  to connect

$serverSource = "ServerNameThatHasDefinedExternalTable.database.windows.net"
$userSource = "UserNameThatHasDefinedExternalTable" #UserName parameter  to connect
$passwordSource = "PasswordThatHasDefinedExternalTable" #Password Parameter  to connect
$DbSource = "DBNameThatHasDefinedExternalTable" #DBName Parameter  to connect

#----------------------------------------------------------------
#Function to connect to the target database using a retry-logic
#----------------------------------------------------------------

Function GiveMeConnectionTarget()
{ 
  for ($i=1; $i -lt 10; $i++)
  {
   try
    {
      logMsg( "Connecting to the database...Attempt #" + $i) (1)
      logMsg( "Connecting to server: " + $serverTarget + " - DB: " + $DbTarget) (1)

      $SQLConnection = New-Object System.Data.SqlClient.SqlConnection 
      $SQLConnection.ConnectionString = "Server="+$serverTarget+";Database="+$dbTarget+";User ID="+$userTarget+";Password="+$passwordTarget+";Connection Timeout=60;Application Name=Tester;Pooling=True" 
      $SQLConnection.Open()
      logMsg("Connected to the database...") (1)
      return $SQLConnection
      break;
    }
  catch
   {
    logMsg("Not able to connect - Retrying the connection..." + $Error[0].Exception) (2)
    Start-Sleep -s 5
   }
  }
}

#----------------------------------------------------------------
#Function to connect to the source database using a retry-logic
#----------------------------------------------------------------

Function GiveMeConnectionSource()
{ 
  for ($i=1; $i -lt 10; $i++)
  {
   try
    {
      logMsg( "Connecting to the database...Attempt #" + $i) (1)
      logMsg( "Connecting to server: " + $serverSource + " - DB: " + $DbSource ) (1)

      $SQLConnection = New-Object System.Data.SqlClient.SqlConnection 
      $SQLConnection.ConnectionString = "Server="+$serverSource+";Database="+$dbSource+";User ID="+$userSource+";Password="+$passwordSource+";Connection Timeout=60;Application Name=Tester;Pooling=True" 
      $SQLConnection.Open()
      logMsg("Connected to the database...") (1)
      return $SQLConnection
      break;
    }
  catch
   {
    logMsg("Not able to connect - Retrying the connection..." + $Error[0].Exception) (2)
    Start-Sleep -s 5
   }
  }
}

#----------------------------------------------------------------
#Function to execute any elastic query using a command retry-logic
#----------------------------------------------------------------

Function ExecuteQuerySource($query)
{ 
  for ($i=1; $i -lt 3; $i++)
  {
   try
    {
     $SQLConnectionSource = GiveMeConnectionSource
     $command = New-Object -TypeName System.Data.SqlClient.SqlCommand
     $command.CommandTimeout = 6000
     $command.Connection=$SQLConnectionSource
     $command.CommandText = $query
     $command.ExecuteNonQuery() | Out-Null 
    break;
    }
  catch
   {
    $ErrorMsg = $Error[0].Exception
    return $ErrorMsg
   }
  }
}

#----------------------------------------------------------------
#Function to add the firewall rule
#----------------------------------------------------------------

Function ExecuteQueryTarget( $IP)
{ 
  for ($i=1; $i -lt 3; $i++)
  {
   try
    {
     $SQLConnectionSource = GiveMeConnectionTarget
     $commandText = "EXECUTE sp_set_firewall_rule @name = N'TestContosoFirewallRule"+ $IP + "',"
     $commandText = $commandText + "@start_ip_address = '" + $IP+ "',"
     $commandText = $commandText + "@end_ip_address = '" + $IP+ "'"
     $command = New-Object -TypeName System.Data.SqlClient.SqlCommand
     $command.CommandTimeout = 6000
     $command.Connection=$SQLConnectionSource
     $command.CommandText = $commandText
     $command.ExecuteNonQuery() | Out-Null 
    break;
    }
  catch
   {
    logMsg("Not able to run the query - Retrying the operation..." + $Error[0].Exception) (2)
    Start-Sleep -s 2
   }
  }
}

#--------------------------------
#Log the operations
#--------------------------------
function logMsg
{
    Param
    (
         [Parameter(Mandatory=$true, Position=0)]
         [string] $msg,
         [Parameter(Mandatory=$false, Position=1)]
         [int] $Color
    )
  try
   {
    $Fecha = Get-Date -format "yyyy-MM-dd HH:mm:ss"
    $msg = $Fecha + " " + $msg
    $Colores="White"
    $BackGround = 
    If($Color -eq 1 )
     {
      $Colores ="Cyan"
     }
    If($Color -eq 3 )
     {
      $Colores ="Yellow"
     }

     if($Color -eq 2)
      {
        Write-Host -ForegroundColor White -BackgroundColor Red $msg 
      } 
     else 
      {
        Write-Host -ForegroundColor $Colores $msg 
      } 


   }
  catch
  {
    Write-Host $msg 
  }
}

#--------------------------------
#Empty?
#--------------------------------
function TestEmpty($s)
{
if ([string]::IsNullOrWhitespace($s))
  {
    return $true;
  }
else
  {
    return $false;
  }
}


#--------------------------------
#Give the IP that is trying to connect
#--------------------------------

function GiveMeIP
{
Param([Parameter(Mandatory=$true)]
      [System.String]$Text)
  try
   {
    $Pos = $Text.IndexOf("Client with IP address '")
    $return= $Text.substring( $Pos+24) 
    $PosUntil = $Return.IndexOf("'")
    $return = $Return.substring(0,$PosUntil ) 
    return $Return
   }
  catch
  {
    $return= $Text
    return $Return
  }
}

cls

logMsg("Starting the process") (2)
$ErrorMsg = ExecuteQuerySource("Select * from dummy")
if(TestEmpty($ErrorMsg) -eq $true) 
{ 
}
else
{
   logMsg("Adding the IP.." + $IP) (2)
   $IP = GiveMeIP($ErrorMsg)
   ExecuteQueryTarget($IP)
   logMsg("Added the IP.." + $IP) (2)
}
logMsg("Finished the process") (2)

 Enjoy!