Use Azure Table Storage via PowerShell and the Rest API

I’ve had a few issues with my PowerShell Azure Functions loading modules, especially when I have bunch running concurrently. To solve this, I’ve switched to interacting with Azure Table Storage via the Rest API instead.

If you’re looking to do this with PowerShell, it can be difficult to form a successful call. There is a bit involved in signing the request and not a lot of table storage specific documentation.

You may find that you often come across the following error message:

Invoke-RestMethod: AuthenticationFailed Server failed to authenticate the request. Make sure the value of Authorization header is formed correctly including the signature.

Below are some common functions that you might find useful. These will allow you to interact with Azure Table Storage using PowerShell via the Rest API. You can use the functions to get all table entities, insert or replace a table entity, merge with an existing table entity and delete a table entity.

These examples use a sample table called “Testing”. To run this sample, just create an Azure Storage Table called Testing and add in the storage account details to the top of the script. I recommend downloading Azure Storage Explorer to interact with your storage accounts.

PowerShell functions to interact with Azure Storage Tables via Powershell and the Rest API

$storageAccount = "StorageAccountName"
$accesskey = "StorageAccountKey"


function GetTableEntityAll($TableName) {
    $version = "2017-04-17"
    $resource = "$tableName"
    $table_url = "https://$storageAccount.table.core.windows.net/$resource"
    $GMTTime = (Get-Date).ToUniversalTime().toString('R')
    $stringToSign = "$GMTTime`n/$storageAccount/$resource"
    $hmacsha = New-Object System.Security.Cryptography.HMACSHA256
    $hmacsha.key = [Convert]::FromBase64String($accesskey)
    $signature = $hmacsha.ComputeHash([Text.Encoding]::UTF8.GetBytes($stringToSign))
    $signature = [Convert]::ToBase64String($signature)
    $headers = @{
        'x-ms-date'    = $GMTTime
        Authorization  = "SharedKeyLite " + $storageAccount + ":" + $signature
        "x-ms-version" = $version
        Accept         = "application/json;odata=fullmetadata"
    }
    $item = Invoke-RestMethod -Method GET -Uri $table_url -Headers $headers -ContentType application/json
    return $item.value
}

function InsertReplaceTableEntity($TableName, $PartitionKey, $RowKey, $entity) {
    $version = "2017-04-17"
    $resource = "$tableName(PartitionKey='$PartitionKey',RowKey='$Rowkey')"
    $table_url = "https://$storageAccount.table.core.windows.net/$resource"
    $GMTTime = (Get-Date).ToUniversalTime().toString('R')
    $stringToSign = "$GMTTime`n/$storageAccount/$resource"
    $hmacsha = New-Object System.Security.Cryptography.HMACSHA256
    $hmacsha.key = [Convert]::FromBase64String($accesskey)
    $signature = $hmacsha.ComputeHash([Text.Encoding]::UTF8.GetBytes($stringToSign))
    $signature = [Convert]::ToBase64String($signature)
    $headers = @{
        'x-ms-date'    = $GMTTime
        Authorization  = "SharedKeyLite " + $storageAccount + ":" + $signature
        "x-ms-version" = $version
        Accept         = "application/json;odata=fullmetadata"
    }
    $body = $entity | ConvertTo-Json
    $item = Invoke-RestMethod -Method PUT -Uri $table_url -Headers $headers -Body $body -ContentType application/json
}

function MergeTableEntity($TableName, $PartitionKey, $RowKey, $entity) {
    $version = "2017-04-17"
    $resource = "$tableName(PartitionKey='$PartitionKey',RowKey='$Rowkey')"
    $table_url = "https://$storageAccount.table.core.windows.net/$resource"
    $GMTTime = (Get-Date).ToUniversalTime().toString('R')
    $stringToSign = "$GMTTime`n/$storageAccount/$resource"
    $hmacsha = New-Object System.Security.Cryptography.HMACSHA256
    $hmacsha.key = [Convert]::FromBase64String($accesskey)
    $signature = $hmacsha.ComputeHash([Text.Encoding]::UTF8.GetBytes($stringToSign))
    $signature = [Convert]::ToBase64String($signature)
    $body = $entity | ConvertTo-Json
    $headers = @{
        'x-ms-date'      = $GMTTime
        Authorization    = "SharedKeyLite " + $storageAccount + ":" + $signature
        "x-ms-version"   = $version
        Accept           = "application/json;odata=minimalmetadata"
        'If-Match'       = "*"
        'Content-Length' = $body.length
    }
    $item = Invoke-RestMethod -Method MERGE -Uri $table_url -Headers $headers -ContentType application/json -Body $body

}

function DeleteTableEntity($TableName, $PartitionKey, $RowKey) {
    $version = "2017-04-17"
    $resource = "$tableName(PartitionKey='$PartitionKey',RowKey='$Rowkey')"
    $table_url = "https://$storageAccount.table.core.windows.net/$resource"
    $GMTTime = (Get-Date).ToUniversalTime().toString('R')
    $stringToSign = "$GMTTime`n/$storageAccount/$resource"
    $hmacsha = New-Object System.Security.Cryptography.HMACSHA256
    $hmacsha.key = [Convert]::FromBase64String($accesskey)
    $signature = $hmacsha.ComputeHash([Text.Encoding]::UTF8.GetBytes($stringToSign))
    $signature = [Convert]::ToBase64String($signature)
    $headers = @{
        'x-ms-date'    = $GMTTime
        Authorization  = "SharedKeyLite " + $storageAccount + ":" + $signature
        "x-ms-version" = $version
        Accept         = "application/json;odata=minimalmetadata"
        'If-Match'     = "*"
    }
    $item = Invoke-RestMethod -Method DELETE -Uri $table_url -Headers $headers -ContentType application/http

}


$body = @{
    RowKey       = "ThisIsARowKey"
    PartitionKey = "HeresAPartitionKey"
    Address      = "123 Sample Street"
    Age          = "24"
}

Write-Host "Getting all table entities" 
$tableItems = GetTableEntityAll -TableName Testing
 
Write-Host "Creating a new table entity"
InsertReplaceTableEntity -TableName "Testing" -RowKey $body.RowKey -PartitionKey $body.PartitionKey -entity $body

Write-Host "Merging with an existing table entity"
MergeTableEntity -TableName "Testing" -RowKey $body.RowKey -PartitionKey $body.PartitionKey -entity $body

Write-Host "Deleting an existing table entity"
DeleteTableEntity -TableName "Testing" -RowKey $body.RowKey -PartitionKey $body.PartitionKey