Tag: Powershell

Posted on

PowerShell Error: The underlying connection was closed: An unexpected error occurred on a send

I got mad the other day, trying to do a simple wget (i.e. invoke-webrequest) to an Azure Function I made and I was getting:

The underlying connection was closed: An unexpected error occurred on a send

I tried switching to .NET Webclient but still same error.

What was more frustrating is that it worked on my dev machine, worked on the server I was running to code on in a browser, just not in powershell.

The Fix

Apparently PowerShell version 5 defaults to TLS 1.0. Azure Functions require TLS 1.2. The fix is super simple, just add this in your code on its own line:

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

Posted on

Query Azure SQL Database Table via Powershell

Real quick one… I have used similar code for ages to query local on-premise SQL databases. However Azure requires the use of encrypted connections.  Here is some fully working code:

#Set Defaults (Optional) which allows you to skip defining instance, user, and password each time
$AzureDefaultInstanceName = “myUniqueAzureSQLDBName”
$AzureDefaultUserID = “myUserIDToAzureSQL”
$AzureDefaultPassword = “myPasswordToAzureSQL”

#The actual function
Function get-azureSQL (
[string]$InstanceName = $AzureDefaultInstanceName
,[string]$UserID = $AzureDefaultUserID
,[string]$Password = $AzureDefaultPassword
,[string]$Query){

$connectionString = “Server=tcp:$($InstanceName).database.windows.net,1433;”
$connectionString = $connectionString + “Database=$($InstanceName);”
$connectionString = $connectionString + “User ID=$($UserID)@$($InstanceName);”
$connectionString = $connectionString + “Password=$($Password);”
$connectionString = $connectionString + “Encrypt=True;”
$connectionString = $connectionString + “TrustServerCertificate=False;”
$connectionString = $connectionString + “Connection Timeout=30;”

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $connectionString

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $Query
$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet) | Out-Null
$SqlConnection.Close()

return $DataSet.Tables[0]
}

#Querying Azure SQL using Defaults defined above
get-azureSQL -Query “select * from logs”

#Querying Azure SQL without Defaults
get-azureSQL -InstanceName “myUniqueAzureSQLDBName” -UserID “myUserIDToAzureSQL” -Password “myPasswordToAzureSQL” -Query “select * from logs”

Sure you can install the Azure Powershell module and then the SQL commands too but most of the time you need to get in quick and grab something, this code is super fast and works every time for me and best of all…. no installs.

If this helped you or you want to suggest an improvement, please just leave it in the commands.

Enjoy,

-Eric