Powershell: How to store secrets the right way

There are secrets that can be deadly.

Here we’re not going to talk about this kind 😊 But that doesn’t mean it’s not important.

It happens quite often that a script you need to run needs access to a resource, and for this you need to provide a secret. It might be a password, a token, whatever.

The easy way is obviously to have them in the script as variables. Is that a good solution?

If you did not answer NO THAT’S HORRIBLE… please change your answer until you do.

Ok so you don’t want to leave it lying around in a script. You can ask at runtime, like this:

$token = Read-Host -Prompt "Please enter the connection token:" -AsSecureString

That’s definitely not as bad. But the follow up problem is, the user needs to type (or, most probably, copy-paste) the secret every time they run the script. Where do the users store their secrets? Are you nudging them to store it in a notepad file for convenience?

In order to keep our systems safe, we need a way that is both secure and convenient.

That’s why using the Windows Credential Manager is a much, much better way. The users only have to recover the secret once, and then they have it stored in a safe way.

Here’s an example of how you can save the secret in Windows Credential manager. It uses the CredentialManager module.

# === DO NOT SAVE THIS SCRIPT ===

# How to save a secret

# PREREQUISITE: 
# Install-Module CredentialManager -Scope CurrentUser

$secretName = 'myAzureServiceBusToken' # or whatever

New-StoredCredential -Target $secretName -Username 'myusername' -Pass 'mysecret' -Persist LocalMachine

And here’s how you can recover and use it:

# How to use the secret

# PREREQUISITE: 
# Install-Module CredentialManager -Scope CurrentUser

$secretName = 'myAzureServiceBusToken' # or whatever

$cred=Get-StoredCredential -Target $secretName
$userName = $cred.UserName
$secret = $cred.GetNetworkCredential().Password

# do whatever you need with the secret

Just for completeness, here’s an example of how to call a REST API with this secret. I imagine that’s one of the most common use cases.

#
# Source: DotJim blog (https://dandraka.com)
# Jim Andrakakis, April 2024
#

# PREREQUISITES: 
# 1. Install-Module CredentialManager -Scope CurrentUser
# 2. New-StoredCredential -Target 'myRESTAPICredential' -Username 'myusername' -Pass 'mysecret' -Persist LocalMachine

# === Constants ===
$uri = 'https://myhost/myapi'
$credName = 'myRESTAPICredential'
$fileName = 'C:\somepath\data.json'
# === Constants ===

$cred=Get-StoredCredential -Target $credName
$pair="$($cred.UserName):$($cred.GetNetworkCredential().Password)"
$encodedCreds = [System.Convert]::ToBase64String([System.Text.Encoding]::ASCII.GetBytes($pair))
$basicAuthValue = "Basic $encodedCreds"

$headers = @{
    Authorization = $basicAuthValue;
    ContentType = 'application/json';
    Accept = 'application/json'
}

try {
    $resp = Invoke-WebRequest -UseBasicParsing -Uri $uri -Headers $headers -Method Post -InFile $fileName
}
catch {
    $errorMsg = "Error sending file '$fileName', exception in line $($_.InvocationInfo.ScriptLineNumber): $_.Exception.Message $_"
    Write-Warning $errorMsg     
}

My problem with history debates online

If you’re in any social medium, I’m sure you’ve come upon one. Would USSR have lost WW2 if not for US’s lend-lease program? Did Mao really kill 50 million people? Were Native Americans peaceful land-loving bison hunters? Were the Turks genocidal? Were the Greeks? Were the Spanish?

Here I’m not going to try and give an answer to these, and many other, questions that I encounter online. Rather, I need to express my deep distaste for the majority of them.

You see, when one does start such a debate online, usually in the context of a social medium, it’s not exactly the case that an impartial scholar wants to discuss historical facts (exceptions do exist; albeit, sadly, few and far between).

No, what happens in the vast majority of cases is that one is trying to express their current preferences, be it ideological, political, social, economic, whatever. And they’re using history as a vehicle.

You can see it everywhere. A debate starts whether “USSR beat Nazi Germany”, which, although wrongly stated in such an absolute way, has undoubtedly some basis in fact. But hidden behind it, not far away, is the projection to modern-day Russia and an attempt to excuse genocidal crimes.

Or take another debate, beloved in US twitter, that somehow the main reason South fought the Civil War was not defending their right to own slaves. Thinly veiled behind it is the american political divide between Republicans and Democrats, usually referred to as “red-blue” divide.

And there lies my deep dislike for such discussions, pleasant exceptions notwithstanding. Far from being truth-seeking, fact-based discourse, they’re disingenuous attempts to impose one’s beliefs unto others.

Or, of course, straight up state propaganda.

Powershell: Get Active Directory group members (without the need to install the ActiveDirectory module)

Powershell offers a number of Active Directory (AD for short) commandlets to make an AD admin’s life a little easier. For example, if you need to get a list of members from an AD group, you can use something like:

Get-ADGroupMember -Identity 'Enterprise Admins' -Recursive

The problem is that this doesn’t work everywhere. The ActiveDirectory module is not a “normal” one you can install with Install-Module; instead, you need to install a Windows feature, either from Control Panel or by using the Add-WindowsCapability commandlet.

But you don’t have to use this module. You can use something that’s available everywhere, the adsiSearcher type accelerator.

So here are a couple of scripts I came up with (credits where they’re due). The first searches through all groups, finds all the ones that match a string and lists all their members.

#
# Source: DotJim blog (https://dandraka.com)
# Jim Andrakakis, January 2024
#
  
# ===== Parameters =====
  
param(
    [string]$searchString = 'accounting'
)
  
# ======================

Clear-Host
$ErrorActionPreference='Stop'

# === Get all groups ===
$objSearcher=[adsisearcher]'(&(objectCategory=group))'
$objSearcher.PageSize = 20000 # may need to adjust, though should be enough for most cases

# specify properties to include
$colProplist = "name"
foreach ($i in $colPropList) { $objSearcher.PropertiesToLoad.Add($i) | out-null } 
	
$colResults = $objSearcher.FindAll()

foreach ($objResult in $colResults)
{
    #group name
    $group = $objResult
    $groupname = ($objResult.Properties).name    

    if (-not ($groupname[0].ToLower().Contains($searchString.ToLower()))) {
        continue
    }

    Write-Host "Members of $groupname [$($group.Path)]"    

    $Group = [ADSI]$group.Path
    $Group.Member | ForEach-Object {
        $Searcher = [adsisearcher]"(distinguishedname=$_)"
        $member = $searcher.FindOne()
        $userName = $member.Properties.samaccountname
        $name = $member.Properties.displayname

        Write-Host "`t[$userName]`t$name"
    }
}

The second displays all details of all users whose name matches a substring.

#
# Source: DotJim blog (https://dandraka.com)
# Jim Andrakakis, January 2024
#
   
# ===== Parameters =====
   
param(
    [string]$searchString = 'Papadomanolakis'
)
   
# ======================
 
Clear-Host
$ErrorActionPreference='Stop'
 
# === Get all groups ===
$objSearcher=[adsisearcher]"(&(objectClass=user)(displayname=*$($searchString)*))"
$objSearcher.PageSize = 20000 # may need to adjust, though should be enough for most cases
#$objSearcher.FindOne().Properties.Keys
$objSearcher.FindAll() | % { $_.Properties }

And the third one is a brilliant one-liner by Jos Lieben that lists all groups of a user.

$userName = $env:USERNAME # change if different user needed
([ADSISEARCHER]"(member:1.2.840.113556.1.4.1941:=$(([ADSISEARCHER]"samaccountname=$userName").FindOne().Properties.distinguishedname))").FindAll().Properties.distinguishedname -replace '^CN=([^,]+).+$','$1'

Hope that helps. Enjoy! 😊

SQL Server: How to allow access to a Windows service without a password

Let us start by stating the obvious: password management for programs and services is a huge pain for developers.

It’s one of the things that is always overlooked during development, where you’re just trying to make the thing work. It’s even not given much attention during testing, where people are usually focused on whether it works correctly on normal uses and edge cases, they look for the UI and usability etc etc.

But come deployment time and the admins start complaining. Storing passwords in plain text files is, how to put it mildly, BLOODY HORRIBLE from a security perspective. And storing them in better ways takes a surprising amount of time –just when the devs thought they’re almost finished.

So having less passwords to store and secure is very helpful for everyone. And one thing many applications need is the credentials to a database.

Fortunately, if your application is running as a Windows service and your database is SQL server, you don’t need a password. You can use integrated security. All you need is to allow (grant) access for the service user to read data from SQL server.

Now here’s the thing: if you’re using a domain user to run the server, that’s obvious. You just create the user in SQL and grant access as needed (you can even use the script below and change the user). But what happens when, as is very common, the application is running under the Local System account?

Turns out, fortunately, there’s a solution for that as well. Every computer’s Local System account exists in Active Directory as “hostname$”. E.g. if the hostname of the application server is MYSERVER, the user name will be MYDOMAIN\MYSERVER$.

So you can run the following SQL to grant access:

/*
Source: DotJim blog (http://dandraka.com)
Jim Andrakakis, April 2023
*/

-- suppose you work on domain MYDOMAIN
-- and the server that hosts the Windows
-- service is MYSERVER
-- this is the name given by the hostname command

USE mydatabase;
GO

CREATE LOGIN [MYDOMAIN\MYSERVER$] FROM WINDOWS;
GO
CREATE USER [MYDOMAIN\MYSERVER$] FOR LOGIN [MYDOMAIN\MYSERVER$]
GO
/* db_datareader grants read-only access */
ALTER ROLE [db_datareader] ADD MEMBER [MYDOMAIN\MYSERVER$]
GO
/* if you want to insert, update or delete, add db_datawriter */
ALTER ROLE [db_datawriter] ADD MEMBER [MYDOMAIN\MYSERVER$]
GO

That done, you can use the following connection string to connect to the database:

Server=MYDBSERVER;Database=myDataBase;Trusted_Connection=yes;

or if you’re running a named instance:

Server=MYDBSERVER\MYINSTANCE;Database=myDataBase;Trusted_Connection=yes;

RabbitMQ: list queues and exchanges with Powershell

As I haven’t yet had the time to set up a proper devops deployment pipeline from my development RabbitMQ to UAT and then to production (don’t yell at me, I know, I’ll get to it… eventually), I find myself comparing instances in order not to forget adding a queue or an exchange.

So I wrote this script, that produces a diff-friendly text file that I can use to compare instances and see what’s missing:

#
# Source: DotJim blog (https://dandraka.com)
# Jim Andrakakis, April 2023
#
 
# ===== Parameters =====
 
param(
    [string]$rqServer = 'http://myServer:15672', # better use HTTPS though
    [string]$rqVhostName = 'myVhost',
    [string]$rqUsername = 'myUsername', # this user needs at least 'Management' permissions to post to the REST API
    [string]$rqPassword = 'myPassword',
    [string]$outDir = 'C:\temp'
)
 
# ======================
 
Clear-Host
$ErrorActionPreference = 'Stop'
$WarningPreference = 'Continue'

$plainCredentials = "$($rqUsername):$($rqPassword)"
$encodedCredentials = [Convert]::ToBase64String([System.Text.Encoding]::UTF8.GetBytes($plainCredentials))
$authHeader = "Basic " + $encodedCredentials

[string]$rqUrlQueues = "$rqServer/api/queues/$rqVhostName/"
[string]$rqUrlExchanges = "$rqServer/api/exchanges/$rqVhostName/"

[string]$filename = [System.IO.Path]::Combine($outDir, [guid]::NewGuid().ToString().Split('-')[0] + ".txt")
Out-File -FilePath $filename -Encoding utf8 -Append -InputObject "Server : $rqServer"
Out-File -FilePath $filename -Encoding utf8 -Append -InputObject "VHost : $rqVhostName"
 
$respQueues = Invoke-WebRequest -Method Get -Uri $rqUrlQueues -Headers @{'Authorization'= $authHeader} 
$respExchanges = Invoke-WebRequest -Method Get -Uri $rqUrlExchanges -Headers @{'Authorization'= $authHeader} 

$queuesJson = ConvertFrom-Json $respQueues.Content
$queuesJson | Sort-Object -Property name | % { Out-File -FilePath $filename -Encoding utf8 -Append -InputObject  "Queue : $($_.name)" }

$exchangesJson = ConvertFrom-Json $respExchanges.Content
$exchangesJson | Sort-Object -Property name | % { Out-File -FilePath $filename -Encoding utf8 -Append -InputObject  "Exchange : $($_.name)" }

Write-Host "Finished, output written to $filename"

Jams Scheduler: Get info about the currently running job in Powershell

When writing jobs in JAMS Scheduler it’s very common to need info about the job that’s currently running. Logging is an obvious need but it’s not the only one.

You’d think the way to do this should be prominently displayed in their otherwise very good documentation, but for whatever reason it’s not. I couldn’t find this anywhere.

Fortunately their support is beyond excellent -it’s hands down the best support I’ve ever worked with- and they gave me the answer as soon as I asked:

Import-Module JAMS
$currentJobEntry = Get-JAMSEntry <<JAMS.JAMSEntry>>

That’s it! It’s really as simple as that. That’s a sample that you can use to see what properties you get:

Import-Module JAMS
$currentJobEntry = Get-JAMSEntry <<JAMS.JAMSEntry>>

# to print all info
$currentJobEntry | select *

# or to get individual info
$jobId = $currentJobEntry.JAMSEntry
$jobName = $currentJobEntry.Name
$jobError = $currentJobEntry.Error

Write-Host "Job $jobId [$jobName]"
Write-Host "Error (if any): $jobError"

# === all properties with example values ===
#Entry               : 21760
#ExtensionData       : System.Runtime.Serialization.ExtensionDataObject
#JAMSEntry           : 21760
#RON                 : 3565757
#AvgElapsedTime      : 
#TodaysDate          : 2/17/2023 12:00:00 AM
#ScheduledTime       : 2/17/2023 10:35:40 AM
#ScheduledTimeUTC    : 2/17/2023 9:35:40 AM
#OriginalHoldTime    : 2/17/2023 10:35:39 AM
#OriginalHoldTimeUTC : 2/17/2023 9:35:39 AM
#HoldTime            : 2/17/2023 10:35:39 AM
#HoldTimeUTC         : 2/17/2023 9:35:39 AM
#StartTime           : 2/17/2023 10:35:40 AM
#StartTimeUTC        : 2/17/2023 9:35:40 AM
#CompletionTime      : 1/1/0001 12:00:00 AM
#CompletionTimeUTC   : 1/1/0001 12:00:00 AM
#MethodId            : 16
#MethodName          : PowerShell
#ParentFolderID      : 96
#ParentFolderName    : \el\MyFolder\Test
#JobID               : 1747
#ExecutingAgentID    : 1
#ExecutingAgentName  : Agent JAMS App-Server
#InitiatorType       : ManualSubmit
#InitiatorID         : 0
#InitiatorUid        : 00000000-0000-0000-0000-000000000000
#ProcessID           : 5408
#SchedulingPriority  : 0
#ExecutionPriority   : 0
#FinalStatusCode     : 0
#FinalSeverity       : Success
#RetainOption        : Default
#RetainTime          : 
#RestartCount        : 0
#CurrentState        : Executing
#Tags                : 
#Debug               : False
#Held                : False
#Icon                : Default
#IconPermanent       : False
#IconMessage         : 
#LogFilename         : D:\JAMS\Logs\Logs\test-jobinfo_003668BD.log
#TempFilename        : D:\JAMS\Temp\test-jobinfo_003668BD.ps1
#SubmittedBy         : MYDOMAIN\myuser
#Name                : test-jobinfo
#JobName             : test-jobinfo
#DisplayName         : test-jobinfo
#FinalStatus         : 
#Note                : 
#JobStatus           : 
#ReconnectAgentName  : MYSERVER
#Source              : 
#JAMSId              : 939fb4c8-ce31-4e1a-8704-10258e85c003
#WFNextTimer         : 1/1/0001 12:00:00 AM
#WFState             : 0
#WFInstance          : 00000000-0000-0000-0000-000000000000
#AuditTrail          : {}
#WFTracking          : {}
#Parameters          : {[JAMSTraceLevel, MVPSI.JAMS.EntryParam], [PSExecutionPolicyPreference, MVPSI.JAMS.EntryParam], 
#                      [ErrorActionPreference, MVPSI.JAMS.EntryParam]}
#Elements            : {MaintenanceWindow, SendEMail, SendEMail}
#SourceElements      : {}
#Properties          : {ExecuteAs: JAMS, HomeDirectory: C:\JamsWorkingFolder, SingleInstanceAction: AllowMultiple, 
#                      NotifyEMail: ...}
#ExecuteAsName       : JAMS
#ExecuteAsID         : 1
#LoadedFrom          : Server: MYSERVER.mycompany.local/Default
#BatchQueue          : Queue: 
#BatchQueueName      : 
#BatchQueueID        : 0
#Calendar            : Calendar: 
#CalendarName        : 
#CalendarID          : 0
#Agent               : Agent: Agent JAMS App-Server
#AgentID             : 1
#AgentName           : Agent JAMS App-Server
#MinimumSeverity     : Warning
#Job                 : Job: test-jobinfo
#LogFile             : System.ServiceModel.Dispatcher.StreamFormatter+MessageBodyStream
#Modified            : False
#NewObject           : False
#Validated           : False
#InEdit              : False
#HasErrors           : False
#Error               : 

SQL Server: How to backup all databases with a timestamp

I hate, hate, hate clicking and clicking again, especially for tasks that can be automated. And one of these tasks is doing DB backups, which I have to do every time before starting a deployment.

So here’s what I’ve come up with. This generates a backup for every database except the system ones (see line 20) as databasename_yyyymmdd.bak, e.g. ERP-UAT-DB_20230321.bak.

/*
Source: DotJim blog (http://dandraka.com)
Jim Andrakakis, March 2023
*/

DECLARE @name NVARCHAR(256) -- database name  
DECLARE @path NVARCHAR(512) -- path for backup files  
DECLARE @fileName NVARCHAR(512) -- filename for backup  
DECLARE @fileDate NVARCHAR(40) -- used for file name

/* specify database backup directory */
SET @path = 'D:\myBackups\'  
 
/* get date as yyyyMMdd */
SELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) 
 
DECLARE db_cursor CURSOR READ_ONLY FOR  
SELECT name 
FROM master.sys.databases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   
 
WHILE @@FETCH_STATUS = 0   
BEGIN   
   SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
   BACKUP DATABASE @name TO DISK = @fileName  
   FETCH NEXT FROM db_cursor INTO @name   
END   
 
CLOSE db_cursor   
DEALLOCATE db_cursor

Change the path, obviously, and you can also change the WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’). E.g. you can do something like WHERE name LIKE ‘%_PRODUCTION’ to suit your scenario.

RabbitMQ: How to publish (upload) a file to a queue via Powershell using REST

As part of my job, this is something I use a lot. And the thing is, it’s quite easy, it’s just an Invoke-WebRequest. Here’s how I do it:

#
# Source: DotJim blog (https://dandraka.com)
# Jim Andrakakis, January 2023
#

# ===== Parameters =====

param(
    [string]$fileName = 'C:\temp\uploadinfo.json',
    [string]$rqServer = 'http://myServer:15672', # better use HTTPS though
    [string]$rqVhostName = 'myVhost',
    [string]$rqQueueName = 'myQueue',
    [string]$rqExchangeName = 'amq.default', # or your exchange name
    [string]$rqUsername = 'myUser', # this user needs at least 'Management' permissions to post to the REST API
    [string]$rqPassword = 'myPass',
	# RabbitMQ has a recommended message size limit of 128 MB
    # See https://www.cloudamqp.com/blog/what-is-the-message-size-limit-in-rabbitmq.html
    # But of course depending on your app you might want to set it lower
	[int]$rqMessageLimitMB = 128		
)

# ======================

Clear-Host
$ErrorActionPreference = 'Stop'
$WarningPreference = 'Continue'

[string]$rqUrl = "$rqServer/api/exchanges/$rqVhostName/$rqExchangeName/publish"

# Sanity check
if (-not (Test-Path $fileName)) {
    Write-Error "File $fileName was not found"
}

# Check RabbitMQ size limit
[int]$rqMessageLimit = $rqMessageLimitMB * 1024 * 1024 
[long]$fileSize = (Get-Item -Path $fileName).Length
if ($fileSize -gt $rqMessageLimit) {
    Write-Error "File $fileName is bigger that the maximum size allowed by RabbitMQ ($rqMessageLimitMB MB)"
}

$plainCredentials = "$($rqUsername):$($rqPassword)"
$encodedCredentials = [Convert]::ToBase64String([System.Text.Encoding]::UTF8.GetBytes($plainCredentials))
$authHeader = "Basic " + $encodedCredentials

[string]$content = Get-Content -Path $fileName -Encoding UTF8
$msgBase64 = [Convert]::ToBase64String([System.Text.Encoding]::UTF8.GetBytes($content))
$json = "{`"properties`":{`"content_type`":`"application/json`",`"delivery_mode`":2},`"routing_key`":`"$rqQueueName`",`"payload`":`"$msgBase64`",`"payload_encoding`":`"base64`"}"
$resp = Invoke-WebRequest -Method Post -Uri $rqUrl -Headers @{'Authorization'= $authHeader} -Body $json
if([math]::Floor($resp.StatusCode/100) -ne 2) {
    Write-Error "File $fileName could not be posted, error $($resp.BaseResponse)"
}

Write-Host "File $fileName was posted to $rqUrl"

Software, Greece, Switzerland. And coffee. LOTS of coffee !