Using AWS Lambda for PowerShell To Get Data From Microsoft SQL Instance

Standard

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.

Continue reading

AWS CodeBuild for PowerShell

Standard

As I’ve been building more AWS Lambda for PowerShell functions I’m wanting to automate testing and deployment with Continuous Integration and Continuous Deployment, a CI/CD Pipeline. To do this one of the steps we’ll need to do is use the AWS Lambda PowerShell cmdlet New-AWSPowerShellLambdaPackage which will package up our script with all it’s dependencies into a zip file. I’ll want to run this on a build environment within AWS CodeBuild. Unfortunately there’s not a PowerShell specific runtime in CodeBuild yet. However, we can utilize the existing .NET 2.2 runtime provided and install PowerShell on it. With that we can run our PowerShell scripts on CodeBuild to build our functions artifacts.

Continue reading

AWS Lambda for PowerShell Encrypted Environment Variables

Standard

Recently I was developing a AWS Lambda for PowerShell function to read a table from a Microsoft SQL on RDS Instance within a VPC. Using the .NET Core SQL client I need to provide credentials to access the database. For the password it should be stored encrypted at rest, never in plain text, and when passed to the function should be done so as a secure string. We can configure the function to use KMS for encryption at rest. This presents us with two challenges. How do I encrypt a AWS Lambda environment variable and how will my AWS Lambda for PowerShell function securely get the decrypted value in memory as a secure string when the Lambda function is invoked.

Continue reading

New Fitch.Tech Domain Name

Standard

I was able to register a new domain to shorted my URL. https://Fitch.Tech is now live! https://FitchTech.com now redirects to the new domain. I love all the new root domain options available.

Testing PS Core Cross-Platform Support using VS Code with WSL PWSH and Windows PS Terminals

Standard

When working with PowerShell using Visual Studio Code it’ll default to the old PowerShell terminal. By using $PSVersionTable we can see it’s running PowerShell 5.1 but we’ll want 6.2. Also I wan’t to test using PowerShell Core on both Windows and Linux. To use .NET Core and PowerShell Core together and test cross platform development we’ll need to install PowerShell Core and the .NET Core SDK on Windows 10 and also within the Windows Subsystem for Linux (WSL).

Continue reading

Get Detailed Network Connection Information

Standard

This function will list all connections to a Windows computer in PowerShell. While most admins are familiar with Netstat and unless you have Windows 8.1 or Server 2012 R2 there’s no PowerShell equivalent. There’s a .Net method that could be utilized but it doesn’t supply the process identifier (PID) to relate that connection the process using it. Alternatively you create a custom type that uses IP Helper that does have the PID but that’s unnecessarily complicated for what I want. Instead I opted for parsing Netstat’s string output into a format I could use. Additionally I added in DNS lookup for remote addresses, process details from Get-Process, and several parameters to get just what you want.

Continue reading

Mass Merge Data From PowerShell To SQL

Merge-DataTableToSQL
Standard

If you have data that you’re collecting with PowerShell and wan’t to insert new entries or update existing ones in a Database then a SQL MERGE function is what you need. I frequently run scheduled queries to collect information that I want to submit to SQL for reporting purposes. When testing this function I was able to merge a DataTable with 100,000+ rows and 20+ columns very quickly. A unique identifier in the results is used as a primary key in the merger. When there’s a match it updates that row, if it doesn’t exist already it inserts it as a new row. To do this effectively a temporary table is created by first looking up the primary key in the targeted table (if there’s one); then builds the query with all the columns in the source. It also compares the source columns to the target table columns to make sure they correspond for the column mapping. The DataTable is then bulk copied to the temporary table, this becomes the source of the merge to the target table. After which the temporary table is dropped.

Continue reading