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;

/* db_datareader grants read-only access */
/* if you want to insert, update or delete, add db_datawriter */

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


or if you’re running a named instance:


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 =====
    [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'
# ======================
$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               :