I’ve got many PowerShell scripts I’ve written over the years but one of the most useful was to simply get data from a Microsoft SQL server and another to insert or update data. This is an example of modernizing a script I wrote over 5 years ago to now run on serverless computing with AWS Lambda. In addition I can use API Gateway and Lambda to create a serverless interface to a Microsoft SQL instance. I’ve tested this with an Amazon RDS MS SQL standard instance within a VPC, a Lambda function with an ENI within that VPC, and API integration with Lambda. Additionally the security group on the RDS instance will need to allow port 1433 with the source being the Lambda function security group. With this combination of components I can do an HTTPS get request to the API which reads from a MSSQL database. This is also very useful for decoupling, migration, and adopting a microservices architecture.

Get data from Microsoft SQL with AWS Lambda for PowerShell

The script below contains 3 PowerShell functions which are used in one AWS Lambda for PowerShell function. Get-EnvironmentVariables does just that, and I use it in debugging, write to console and therefor CloudWatch logs all environment variables for that invokation. ConvertFrom-KmsEncryptedString I covered in a previous post on this blog. It will be used to convert the KMS encrypted environment variable to a secure string password for the MSSQL instance. Finally the Get-SqlToDataTable function uses the .NET Core SQL Client to run our query and return the data as a datatable which is then returned as JSON for the response message from Lambda.

#Requires -Modules @{ModuleName='AWSPowerShell.NetCore';ModuleVersion='3.3.498.0'}

#To use AWS PowerShell .NetCore for local testing use the following
#Install-Module -Name AWSPowerShell.NetCore
#Import-Module -Name AWSPowerShell.NetCore

function ConvertFrom-KmsEncryptedString
{
<# .EXAMPLE $SecureString = ConvertFrom-KmsEncryptedString -EncryptedBase64String $env:password .PARAMETER EncryptedBase64String Encrypted base 64 string to be decrypted .PARAMETER ToPlainText Default output is a secure string object, us this to output as a plain text string. #>
param(
    [Parameter(Mandatory=$true)][string]$EncryptedBase64String,
    [switch]$ToPlainText = $false
)
begin {
    #KMS Client Object
    $KmsClient = New-Object Amazon.KeyManagementService.AmazonKeyManagementServiceClient
    #Convert encrypted base 64 text string to and encrypted bytes array. 
    $EncryptedBytes = [System.Convert]::FromBase64String($EncryptedBase64String)
    $utf8 = [System.Text.UTF8Encoding]::UTF8
    $SecureString = [System.Security.SecureString]::new()
}
process {
    foreach ($byte in $KmsClient.Decrypt($EncryptedBytes,$null)) {
        $SecureString.AppendChar($utf8.GetString($byte))
    }
    #Disposing the KMS client to release its resources.
    $kmsClient.Dispose()
    #Makes the secure string value immutable.
    $SecureString.MakeReadOnly()   
}
end {
    if ($ToPlainText.IsPresent -eq $true) {
        #Returns the plain text string of the encrypted environment variable when -PlainText switch is used.
        Return [System.Runtime.InteropServices.Marshal]::PtrToStringAuto([System.Runtime.InteropServices.Marshal]::SecurestringToBstr($SecureString))
    }
    else {
        #Returns a secure string of the decrypted environment variable.
        Return $SecureString
    }
}
}

function Get-EnvironmentVariables {
<# .DESCRIPTION Get-EnvironmentVariables Creates a PowerShell variable from every environment variable .PARAMETER ToJSON Return environment variables as JSON is good for debug logging. .PARAMETER EnvironmentVariable Get a single environment variable instead of all. #>
param(
    [Parameter(Mandatory=$false)][string]$EnvironmentVariable="*",
    [switch]$ToJSON = $false
)
    $EnvironmentVariables = [System.Environment]::GetEnvironmentVariables()
    $Array = @()
    $keys = $EnvironmentVariables.Keys | Where-Object {$_ -like $EnvironmentVariable}
    foreach ($key in $keys) {
        $value = $null
        $value = try{$EnvironmentVariables.Item($key)}catch{$null}
        $Array += @{$key=$value}
        New-Variable -Name $key -Value $value -Scope global -Force
    }
    if ($ToJSON.IsPresent -eq $true) {
        Return ,$(ConvertTo-Json -InputObject $Array -Compress -Depth 5)
    }
}

function Get-SqlToDataTable
{
<# .EXAMPLE $DataTable = Get-SqlToDataTable -Instance mssql.asdf.us-west-2.rds.amazonaws.com -Database dBName -Target dbo.myDBTable .PARAMETER Password The Password must be a Secure String .PARAMETER Instance Endpoint address of the SQL instance e.g. mssql.asdf.us-west-2.rds.amazonaws.com .PARAMETER Database Name of the database on the instance where the table is. .PARAMETER Table The Table to get data from. .PARAMETER Filter Default="*" Used in selecting the columns from the target. .PARAMETER Timeout Default="60" Used in connection string. .PARAMETER Where Variable in command text after Select * From Target. Example: "WHERE [Parent Container] LIKE '$($domain)%' AND [In AD] = '1' AND FQDN LIKE '%VCEN%'" #>
param(
    [Parameter(Mandatory=$true)][string]$Instance,
    [Parameter(Mandatory=$true)][string]$Database,
    [Parameter(Mandatory=$true)][string]$Table,
    [Parameter(Mandatory=$true)][string]$UserName,
    [Parameter(Mandatory=$true)][securestring]$Password,
    [string]$Filter= "*",
    [Int]$Timeout="60",
    [string]$Where,
    [switch]$Distinct
)
begin {
#Create connection object to SQL instance
    $DataTable = $null
    if ($SQLConnection.State -ne [Data.ConnectionState]::Open) {
        $SQLConnection = New-Object System.Data.SqlClient.SqlConnection
        $SQLConnection.ConnectionString = "Server=$Instance;Database=$Database;Integrated Security=false;Connection Timeout=$Timeout"
        $SQLConnection.Credential = [System.Data.SqlClient.SqlCredential]::new($UserName,$Password)
        $SQLConnection.Open()
    }
}
process {
    if ($SQLConnection.State -ne [Data.ConnectionState]::Open) {
        "Connection to SQL DB not open"
    } else {
        #Command to be executed
        $SQLCommand = New-Object System.Data.SqlClient.SqlCommand
        $SQLCommand.Connection = $SQLConnection
        if($Distinct) { 
            $SQLCommand.CommandText = "SELECT DISTINCT $Filter FROM $Table $Where"
        } else {
            $SQLCommand.CommandText = "SELECT $Filter FROM $Table $Where"
        }
        #Empty DataTable to be filled by SQL Adapter
        $DataTable = New-Object System.Data.DataTable
        #SQL Adapter used to execute SQL command
        $SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
        $SQLAdapter.SelectCommand = $SQLCommand
        $SQLAdapter.Fill($DataTable) | Out-Null
    }
}
end{
        #Closing connection to SQL server if open
        if ($SQLConnection.State -eq [Data.ConnectionState]::Open) {
            $SQLConnection.Close()
        }
        Return ,$DataTable
    }
}

#Set AWS Lambda for PowerShell function with an environment variable for debug and a value of true to write to CloudWatch Logs Input and Context information
if ($env:debug -eq $true) {
    Write-Host (ConvertTo-Json -InputObject $LambdaInput -Compress -Depth 5)
    Write-Host (ConvertTo-Json -InputObject $LambdaContext -Compress -Depth 5)
    Write-Host (ConvertTo-Json -InputObject $PSVersionTable -Compress -Depth 5)
    Write-Host (Get-EnvironmentVariables -ToJSON)
}

#Connect to the SQL Database and get the table as a datatable object
$Datatable = Get-SqlToDataTable -Instance $env:instance -Database $env:database -Table $env:table -UserName $env:username -Password $(ConvertFrom-KmsEncryptedString -EncryptedBase64String $env:password)

#Last object in pipeline returned by function handler. Converting datatable to json. 
Return ($Datatable | Select-Object $Datatable.Columns.ColumnName ) | ConvertTo-Json

Deploying the script to AWS Lambda for PowerShell

To deploy the following script as a AWS Lambda for PowerShell function save it as get-sqltable.ps1 then run the following after providing the appropriate Subnet ID and Security Group ID.

Publish-AWSPowerShellLambda -ScriptPath .\get-sqltable.ps1 -Name get-sqltable -Subnet [subnet-id] -SecurityGroup [sg-id]

Configure Environment Variables

This script uses environment variables for the SQL instance endpoint address, database name, table name, and user name. There’s also a password environment variable which I’ve set as encrypted with KMS and converted to a secure string using ConvertFrom-KmsEncryptedString which is passed to Get-SqlToDataTable. Make sure you set environment variables with the following keys and don’t forget to encrypt the password. See my previous post for help on AWS Lambda for PowerShell encrypted environment variables.

Environment Variable Keys:
instance = [endpoint URL]
database = [database name]
table = [dbo.tablename] (include dbo. for regular database tables)
username = [SQL username]
password = [SQL password]
debug = [true/false]