Lesson Learned #431: Determining Connection Type to Azure SQL Database: Proxy or Redirect

[Jose_Manuel_Jurado]

When connecting to Azure SQL Database, it's important to understand the type of connection established. Azure offers two primary connection policies: Proxy and Redirect. Knowing which one you're using can be crucial for performance considerations and troubleshooting.



Understanding Connection Types

• 
  Proxy: In the proxy method, clients initially connect to the Azure SQL Gateway, which then redirects the connection to the appropriate database node. All subsequent data flow between client and database goes through the gateway, potentially introducing an additional layer of latency.
  
  
• 
  Redirect: In the redirect method, after the initial connection via the Azure SQL Gateway, the client is given information about the actual database node. Subsequent communications then occur directly with the database node, bypassing the gateway and often resulting in better performance.
  

Using PowerShell to Determine the Connection Type


We've developed a PowerShell script to help you determine which connection policy is in use when connecting to an Azure SQL Database. This is achieved by examining the network connections associated with the PowerShell session.



Here's how it works:

1. The script prompts the user to enter the connection details.
2. The script establishes a connection to the Azure SQL Database.
3. It then retrieves the TCP connections for the current PowerShell session.
4. By inspecting the ports used, it determines if the connection is Proxy (port 1433) or Redirect (ports between 1500 and 65000).

# Request connection details from the user
$serverName = Read-Host -Prompt "Enter server name"
$databaseName = Read-Host -Prompt "Enter database name"
$userId = Read-Host -Prompt "Enter user ID"
$password = Read-Host -Prompt "Enter password" -AsSecureString

# Check for empty inputs and exit if any is found
if ([string]::IsNullOrEmpty($serverName) -or [string]::IsNullOrEmpty($databaseName) -or [string]::IsNullOrEmpty($userId) -or ($password.Length -eq 0)) {
Write-Host "Error: Server, database, user ID, or password cannot be empty. Exiting."
return
}

# Convert the secure password to plain text (necessary for SqlConnection)
$BSTR = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($password)
$plainPassword = [System.Runtime.InteropServices.Marshal]:trToStringAuto($BSTR)

# Create the SQLClient connection
$connectionString = "Server=tcp:$serverName,1433;Database=$databaseName;User ID=$userId;Password=$plainPassword;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Pooling=False"
$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection -ArgumentList $connectionString

# Establish the connection
$connection.Open()

# Get the PowerShell PID
$currentPID = [System.Diagnostics.Process]::GetCurrentProcess().Id

# Look for TCP connections using the PID
$connections = Get-NetTCPConnection -OwningProcess $currentPID | Where-Object { $_.RemotePort -eq 1433 -or ($_.RemotePort -ge 1500 -and $_.RemotePort -le 65000) }

# Determine the connection type
$proxyPortExists = $connections | Where-Object { $_.RemotePort -eq 1433 }
$redirectPortsExist = $connections | Where-Object { $_.RemotePort -ge 1500 -and $_.RemotePort -le 65000 }

if ($redirectPortsExist) {
Write-Host "Connection type: Redirect"
} elseif ($proxyPortExists) {
Write-Host "Connection type: Proxy"
} else {
Write-Host "Unable to determine connection type."
}

# Close the connection
$connection.Close()

# Clean up (remove plain text password from memory)
[System.Runtime.InteropServices.Marshal]::ZeroFreeBSTR($BSTR)


Conclusion




By understanding and being able to determine your connection type to Azure SQL Database, you're better equipped to optimize performance and diagnose potential issues. We hope this PowerShell script aids you in your Azure SQL journey. Should you have any questions or need further assistance, always feel free to reach out!

Continue reading...