Checking SQL Account Password Expiry

Introduction

“Who uses SQL authentication these days” I hear you ask. Well unfortunately there are some legacy applications out there that only work with a SQL account, not an Active Directory one. Not ideal but that’s life if you want to use that vendor’s application but what if your corporate security policy insists that passwords are changed regularly, which, let’s face it, isn’t exactly unreasonable in this day of heightened security awareness? Oh, and perhaps we also should change passwords when people who know them leave an organisation – now there’s a radical idea!

A Scripted Solution

I’m no SQL expert but it didn’t take me long to figure out a TSQL query to return SQL account details, assuming you run it with an account that has sufficient SQL privileges to query accounts although I found that a non-privileged SQL account can query itself which is ideal for my first use case as we have no privileged access to the SQL instance.

Putting this into PowerShell is relatively easy although I tend to use the SQL cmdlets available with .NET rather than relying on the SQLPS module having been installed. A little more code is required to use them but with the advantage that the script should run wherever the .NET Framework is installed.

Passing Credentials

When I write any script that takes credentials, I’m acutely aware that if someone is sniffing process command lines, either live with task manager, Process Explorer, etc. or trawling through historic data, depending on what process auditing options are set, then passing a clear text password on the command line isn’t a great idea.

Therefore there are a number of ways to make it more difficult, but not impossible, for the password to be obtained by a third-party.

1. The -password option can be omitted if the %SQLval1% environment variable has been set to contain the clear text password as the script will use that instead. However, this still requires the plain text password to be stored somewhere although file system permissions can be used to restrict access. If mail server credentials need to be specified then the environment variable %_MVal12% can be used to store that.
2. PowerShell includes the cmdlet ConvertTo-SecureString which can take a plain text string and encrypt it so the script has the ability to encrypt a plain text password such that it can later be passed via the -hashedPassword option to the script (or -mailHashedPassword if this is the password for the account required to connect to the mail server). This encrypted password can only be used, which includes decryption, on the same machine as it was encrypted on and by the same user. For example, to encrypt the password we can run this which places it into the clipboard:

& '.\Check SQL account expiry.ps1' -encryptPassword -password thepassword|Set-Clipboard

and then we can paste that into the argument for the -hashedPassword option when we call the script, such as via a scheduled task. This still isn’t 100% secure because an administrator could reset the password on the account used to encrypt the password so they could then logon and decrypt it but it’s a whole lot better than passing a clear text password on a command line!

There is also a -mailOnFail argument so that if the connection to the SQL server fails then an email can be sent by the script in case the potential outage or account problem hasn’t been detected already.

Additionally, there is -includeDisabled, which will include all accounts, unless -accountName is specified (as a regular expression) to limit what accounts are reported, that cannot currently login since password expiry for an account is a little moot if that account is already disabled, or set to require a password change after first use.

Example Usage

The script includes full PowerShell compatible help including examples but I’ll include one here so you get the gist of it:

& '.\Check SQL account expiry.ps1' -sqlServer SQL01\instance01 -recipients guyl@hell.com -mailServer smtp.hell.com -mailOnFail -expireWithinDays 10

This will connect to the specified SQL server instance as the user running the script and email a list of all SQL accounts which expire within the next 10 days to the given recipient. If the script fails to connect to the SQL server then an email will also be sent containing details of the error.

The Output

If no mail parameters, such as the mail server and recipients, are specified then the output will go to an onscreen grid view, unless -nogridview is specified whereupon the account objects will be output directly so they can be piped into another script or manipulated manually.

With mail parameters set, an email should be sent which looks similar to the following, where I’ve overridden the default of 7 days for the expiry check to 39 days to ensure that it captures some data for demonstration purposes:

If the script fails to connect to SQL and the -mailOnFail argument is specified then an email similar to the following will be sent:

If you just want the script to check that connections to SQL can be made, specify the -accountName argument with the name of an account that doesn’t exist, such as “non-existent” so that it won’t email any expiring accounts, just connection failures. Even without privileged credentials being used to connect to SQL, either by running the script under a Windows account that has the securityadmin server/instance privilege or with a privileged SQL account via -username, a SQL account can query itself so that can be used.

The Script

The digitally signed script is available on GitHub here and you use it at your own risk although it is purely passive so cannot inflict any damage.

Advertisement

Guide to my GitHub Scripts

This article, which will be updated as new scripts are added, serves as an index to the scripts I have uploaded to GitHub with a quick summary of what the script can do and links to explanatory blog articles. The scripts are split logically into a number of GitHub repositories, namely:

• Citrix
• Microsoft
• General-Scripts
• Ivanti
• VMware

Citrix

1. DailyChecks.ps1 – allows you to get a summary of your Citrix XenApp/XenDesktop 7.x deployment emailed to you via a scheduled task to help spot issues. Blog Post
2. End Disconnected sessions.ps1 – finds sessions disconnected over a given duration and logs them off, optionally terminating specified processes in case they are preventing logoff.
3. Get PVS boot time stats.ps1 – pull PVS target device boot times from PVS server event logs to show fastest, slowest, mean, median and mode values with the option to send an email if thresholds are breached. Blog Post
4. Get PVS device info.ps1 – retrieve PVS target device information from PVS servers and display their configuration along with corresponding data from Citrix Studio, Active Directory, VMware and the devices themselves such as last boot time & IP address. Selected devices can then have operations performed on them such as deleting from PVS/AD/Studio or rebooting. Blog Post
5. Ghost Hunter.ps1 – find disconnected Citrix XenApp sessions which Studio/Director say still exist but do not and mark them such that they cannot prevent affected users from launching further published applications. Blog Post
6. Show PVS audit trail.ps1 – collect PVS auditing events in a given date/time range and show on-screen or export to a csv file. Can also enable auditing if it is not already enabled.
7. Show Studio Access.ps1 – show all users granted access to Citrix Studio and their access levels and optionally export to a csv file. It will recursively enumerate AD groups to show each individual user with Studio access.
8. StoreFront Log Levels.ps1 – display and/or change the logging levels on Citrix StoreFront servers. It can operate on multiple servers from a single script invocation. Blog Post
9. Parse storefront log files.ps1 – show Citrix StoreFront log files in a sortable and filterable consolidated view, optionally filtering on entry type and date ranges. Selected lines will be placed in the clipboard to enable further research. Blog Post
10. Get Citrix admin logs.ps1 – retrieve the logs viewable in Studio in a given time window and write to a csv file or display in an on screen sortable/filterable grid view. The logs can be filtered on the user who performed the action, where the action was performed from, either Studio or Director, whether it was an admin or config change action and the type of action such as logoff or shadow.
11. Get Citrix OData.ps1 – query the OData interface exposed by Citrix Delivery Controllers to retrieve information on sessions, errors, machines, etc. This is where Citrix Director gets its information from and also means that you don’t have to query SQL (which is unsupported). See here for information on what is available.
12. Modify and launch file.ps1 – make modifications to a text file such as an ICA file, e.g. to change window sizes, and launch the newly created file. Can also install itself as an explorer SendTo context menu shortcut.
13. Recreate PVS XML manifest.ps1 – create the XML manifest that PVS needs in order to import disks which have multiple versions. Can import from orphaned SQL data or examination of specified *.(a)vhd(x) files. Use when a disk has disappeared from the PVS console.
14. Direct2Events.ps1 – Uses OData (like Citrix Director) to retrieve Citrix Virtual Apps and Desktops Session information from a Delivery Controller and displays in a WPF GUI allowing troubleshooting and remediation without needing to go to different tools such as PVS Console, VMware vSphere and Active Directory or connecting to the end-points

Microsoft

1. Change CPU priorities.ps1 – dynamically change the base priorities of processes which over consume CPU so other processes get preferential access to the CPU. If a process stops over consuming then its original base priority will be restored. Can include/exclude specific users, processes and sessions.
2. Trimmer.ps1 – trim the working sets of processes to make more memory available for other processes/users on a system. Can trim on demand or when processes are unlikely to need the memory such as when a session is idle, disconnected or locked. Can also set hard working set limits to cap leaky processes. Blog Post Blog Post Blog Post
3. Get installed software.ps1 – show the installed software on one or more computers where the computers are specified on the command line or via a csv file. Queries the registry rather than the win32_product WMI/CIM class which is faster and gives more complete results. Output can be to a csv file, an on screen grid view or standard output for piping into something else. If -uninstall is specified, items selected when OK is clicked in the grid view will be uninstalled. Similarly, a -remove option takes a comma separated list of package names or regular expressions and will run the uninstaller for them, silently if -silent is specified and the uninstall program is msiexec.exe.
4. Group Membership Modifier.ps1 – add or remove a specified list of user accounts from local groups, such as Administrators or Remote Desktop Users, on one or more machines.
5. Clone VHD.ps1 – create a new Hyper-V virtual machine from a .vhd/.vhdx file containing an existing VM, selecting the VM configuration in a GUI. Will integrate itself into Windows Explorer so you right-click on a virtual disk file and run it, elevating itself if required. Can make linked clones which can reduce disk space. Blog Post
6. Fix Sysprep Appx errors.ps1 – parses sysprep logs looking for failures due to AppX packages causing sysprep to fail, removes them and runs sysprep again until successful.
7. Show NTFS zone info.ps1 – Google Chrome and Internet Explorer store the URL of where downloaded files have come from in an NTFS Alternate Data Stream (ADS). This script shows these and optionally removes this information. Blog Post
8. Profile Cleaner.ps1 – retrieve local profile information from one or more machines, queried from Active Directory OU, group or name, present them in an on-screen filterable/sortable grid view and delete any selected after prompting for confirmation. Options to include or exclude specific users and write the results to a csv file. Blog Post
9. Show users.ps1 – Show current and historic logins including profile information, in a given time range or since boot, across a number of machines queried from Active Directory OU, group or name, write to csv file or display in an on-screen sortable/filterable grid view and logoff any selected sessions after confirmation. Works on RDS and infrastructure servers as well as XenApp. Blog Post
10. Profile.ps1 – a PowerShell profile intended to be used on Server Core machines, with PowerShell set as the shell, which reports key configuration and status information during logon.
11. Add firewall rules for dynamic SQL ports.ps1 – find all SQL instances and create firewall rules for them to work with dynamic ports
12. Find Outlook drafts.ps1 – find emails in your Outlook drafts folder of a given age, prompt with the information with the option to open the draft. Designed to help you stop forgetting to complete and send emails. Has options to install & uninstall itself to launch at logon. Blog Post
13. Outlook Leecher.ps1 – find SMTP email addresses in all your Outlook folders including calendars and write them to a csv file including context such as the subject and date of the email.
14. Check Outlook recipient domains – an Outlook macro/function which will check the recipient addresses when sending an email and will warn if the email is going to more than a single external domain. Designed to help prevent accidental information leakage where someone may pick the wrong person when composing.
15. Fix reminders – an Outlook macro/function which will find any non-all day Outlook meetings which have no reminder set, display the details in a popup and add a reminder for a number of minutes before the event as selected by the user. Blog Post.
16. Check Skype Signed in.ps1 – uses the Lync 2013 SDK to check Skype for Business is signed in and will alert if it is not via a popup and playing an optional audio file. Can also pop up an alert if the client has been in “Do Not Disturb” in excess of a given period of time.
17. Redirect Folders.ps1 – show existing folder redirections for the user running the script or set one or more folder redirections with a comma separated list of items of the form specialfolder=path. For example Music=H:\Music
18. Check and start processes.ps1 – check periodically if each of a given list of processes is running and if not optionally start it, after an optional prompt is displayed. Any necessary parameters for each process can be specified after an optional semicolon character in the process name argument. Can install or uninstall itself to the per user or per machine registry run key so it runs at logon. Use it to launch and monitor key processes such as Outlook or Skype for Business (lync.exe).
19. Autorun.ps1 – list, remove or add logon autoruns entries in the file system or registry for the user running the script or all users if the user has permissions. Can also operate on the RunOnce key and wow6432node on x64 systems. Uses regular expressions for matching the shortcut/registry value name and/or the command so knowing the exact names or commands is not required. Uses PowerShell’s built in confirmation mechanism before overwriting/deleting anything.
20. Find and check IIS server certs.ps1– find IIS servers via OUs or AD groups or specify via regular expression, specific servers or from the contents of a text file. Check the expiry date of any certificates in use and present a list of those expiring within a specified number of days in a grid view, write to csv file or send via email.
21. wcrasher.cs.ps1 – compiles embedded C# code to produce an exe file (32 or 64 bit or even Itanium) which will crash when the “OK” button of the displayed dialogue box is clicked. Use it to check that the OS is configured the desired way for handling application crashes or to produce dumps for practicing analysis.
22. WTSApi.ps1 – provides the function Get-WTSSessionInformation which is a wrapper for the WTSQuerySessionInformationW function from wtsapi32.dll with the WTSSessionInfoEx class parameter. This returns an array of session information items for the one or more computers passed to it which can be used in place of running quser.exe (“query user”) and having to parse its somewhat inconsistent output.
23. Trim run history.ps1 – Remove items from the history of Explorer’s Start->Run menu, and task Manager’s  File->Run new task, either by specifying what to keep or what to remove via regular expression (which can be as simple as something like ‘mstsc’). Uses PowerShell’s builtin confirmation mechansim so by default will prompt before each deletion.
24. Get Process Durations.ps1 – Retrieve process creation and termination events from the security event log, if auditing of these is enabled, and show the start and end times of the processes and command lines if that auditing is enabled too. Can optionally show how long after logon and/or boot processes started and can filter on specific processes and/or users. Output to csv format file, sortable/filterable grid view or the PowerShell pipeline.
25. Analyse IIS log files.ps1 – Analyse IIS log files to show requests/min/sec, and min, max, average and median response times per time interval, usually seconds to aid in finding busy/overloaded periods for capacity planning, troubleshooting, etc.
26. Check AD account expiry.ps1 – Find AD accounts with passwords or accounts expiring within the specified number of days or are locked out or disabled and optionally send an email containing the information. To help spot problems where account expiry could cause issues such as when used as service accounts.
27. Check SQL account expiry.ps1 – Find SQL accounts with passwords expiring within the specified number of days and optionally send an email containing the information. Useful where these accounts are used as service accounts. Can also be used to send an email alert if the specified SQL server cannot be connected to.
28. Download and Install Office 365 via ODT.ps1 – Download the latest version of the Office Deployment Kit and use that, once the executable has been extracted and its certificate checked, to download and install Office 365.
29. Find loaded modules.ps1 – Examine loaded modules all or specific processes by name or pid and show those where the module name/path or company name match a specified string/regex. Designed to help spot processes hooked by 3rd party software like Citrix, Ivanti, Lakeside, etc. Shows module versions so can also be used to play spot the difference between processes.
30. Get Remote User Logon Times.ps1 – Use WMI to query computers to find out, since boot, when any remote desktop connections logged on. Gives finer granularity than “query user” (quser) and works on multiple computers in a single invocation.
31. Kill elevated processes.ps1 – Check already running processes and then watch for process created events and if the process is in a specified list and have been launched elevated then terminate them and audit to the event log.
32. Monitor process start stop.ps1 – Uses WMI/CIM to register for notifications when processes are started or stopped so effectively a process watcher.
33. Network Profile Actioner.ps1 – Check network connection profiles and if any are connected on a public network, or nothing is connected so the computer is offline, set a registry value differently compared with private/domain network. Defaults to setting the registry such that the username is not displayed on the lock screen if the computer is on a public network or offline to aid with privacy protection.
34. Power Watcher.ps1 – Designed to help set the most suitable power scheme when using an external power bank for a laptop as the laptop sees it as still being powered by an external power source so does not implement any power saving (on a Dell laptop).
35. Show FSlogix volumes.ps1 – Show FSLogix currently mounted volume details & cross reference to FSLogix session information in the registry.
36. Check and fix domain membership.ps1 – Check domain membership of the machine the script is running on and try to repair using Test-ComputerSecureChannel. Can be placed in a computer startup script with encrypted password.
37. Convert graphics files.ps1 – bulk convert graphics files from one format to another
38. Get file bitness.ps1 – show the file bitness of specified files or files in a folder including the .NET CPU specifications
39. event aggregator.ps1 – retrieve all events from the 300+ event logs on one or more computers and show in sortable/filterable gridview and/or write to csv. Various filter in/out option available.
40. Set Foreground Window.ps1 – find the main window for given one or more processes, by id or name, and optional argument matching, and set as the foreground window or perform another operation on them such as minimising or maximising. Written to solve a problem when a running process refused to show its window or even a taskbar icon for it.
41. Get MSI Properties.ps1 – get any MSI property, such as ProductVersion, from one or more MSI files by reading the contents of the MSI file. Useful for finding out the version of an MSI file. ALso gets summary information such as bitness https://twitter.com/guyrleech/status/1207013011417948160
42. Get files modified since boot.ps1 – Find files modified or created since the last boot time without following symbolic links and junction points. Useful to find out what has consumed the Citrix Provisioning Services write cache but can also take arbitrary start and end times to find files modified/created in a given time window for troubleshooting.
43. Pause Resume Processes.ps1 – Pause or resume processes using debugger API functions. Useful to stop applications being used outside of approved hours, stop resource guzzling applications impacting other processes so it can be examined later. Note that the process resuming a process must be the same one that paused it otherwise it will fail. Also if the pausing process exits, the paused processes will exit. The script caters for this. https://twitter.com/guyrleech/status/1254432210872078337
44. Bincoder GUI.ps1 – base 64 encode and decode data to and from any file to allow the data to be copied over the Windows clipboard, e.g. to or from a remote session where file sharing sites, email, etc are not available.
45. Get Extended File Properties.ps1 – Retrieve specified or all extended properties from a file, not just those in the version resource of the file
46. Add computers to perfmon xml.ps1 – Take an XML template exported from perfmon with a single machine and duplicate all counters for a specified set of machines. This creates a new XML file which can be imported back in to perfmon to capture performance data across all the machines.
47. Delete profile for group member.ps1 – Delete local user profiles for members of an Active Directory group which are not currently in use
48. Fix shortcuts.ps1 – Find shortcuts with target or icon path or arguments matching a given regular expression and change to a new string.
49. Get Account Lockout details.ps1 – Find all domain controllers and show account lockouts in a given time range and/or for a specific user including the machine where the lockout occurred.
50. Get info via CIM.ps1 – Gather info from one or more computers via CIM and write to CSV files to aid health checking. A list of nearly 50 CIM classes is built in to assist relevant information gathering.
51. Send to Clipboard.ps1 – Put contents of text or graphics files onto the clipboard – designed for use as a shortcut in explorer’s right click send to menu

General Scripts

1. Regrecent.ps1 – find registry keys modified in a given time/date window and write the results to a csv file or in an on-screen sortable/filterable grid view. Can include and/or exclude keys by name/regular expression. Blog Post
2. Leaky.ps1 – simulate a leaky process by causing the PowerShell host process for the script to consume working set memory at a rate and quantity specified on the command line.
3. Twitter Statistics.ps1 – fetch Twitter statistics, such as the number of followers and tweets, for one or more Twitter handles without using the Twitter API
4. Sendto Checksummer.ps1 – when a shortcut to this script, by setting the shortcut target to ‘powershell.exe -file “path_to_the_script.ps1”, is added to the user’s Explorer SendTo folder, a right-click option for calculating file checksums/hashes is available. The user will be prompted for which hashing algorithm to use and then the checksums of all selected files will be calculated and shown in a grid view where selected items will be copied to the clipboard when “OK” is clicked.
5. Zombie Handle Generator.ps1 – opens handles to a given list of processes and then closes them after a given time period or after keyboard input. Used to simulate handle leaks to test other software. Can open process or thread handles.
6. Sendto folder size.ps1 – shows the sizes of each folder/file selected in explorer, or passed directly on the command line. For each item then selected in the grid view, it will show the largest 50 files. If any files are selected when OK is pressed in that grid view, a prompt to delete will be shown and if Yes is clicked, the files will be deleted via the recycle bin. To install for explorer right-click use and add a shortcut to this script via Powershell.exe -file in the shell:sendto folder.
7. Compare files in folders.ps1 – compare file attributes and checksums between files in two specified folders, and sub folders. Files selected in the grid view when OK is clicked will then have their differences shown in separate grid views.
8. Query SQLite database.ps1 – query data from a SQLite database file or show all of the table names. Queries can be qualified with a “where” clause, the columns to return specified, or it defaults to all, and the results output to a csv file or are displayed in an on-screen filterable/sortable grid view.
9. Find file type.ps1 – Looks at the content of files specified to determine what the type of a file actually is. File types identifiable include various zip formats, image and video formats and executables. It will also seek out files stored in Alternate Data Streams on NTFS volumes.
10. Set photo dates.ps1  – Get the date/time created from image file metadata and set as the file’s creation date/time which can make it easier to see/sort picture files by the creation date of the image itself, not when the file was copied to the current folder it resides in.
11. Shortcuts to csv.ps1 – Produce csv reports of the shortcuts in a given folder and sub-folders and optionally email the resulting csv file. Can check shortcuts locally (default) or on a remote server, e.g. for checking centralised Citrix XenApp/XenDesktop shortcuts. By default it will check that the target and working directory exist for a shortcut so the resulting csv file can be filtered on these columns to easily find bad shortcuts.
12. Update dynamic dns.ps1 – Update dynamic DNS provider if the external IP address has changed (stored in the registry) to update the address or email the details to a given list of recipients.
13. Find JSON attribute by name.ps1 – Find JSON attributes via name or regex and return the value(s). Saves having to navigate a potentially unknown object structure.
14. Get chunk at offset.ps1 – display the text from a given file at a given offset within the file. Used with SysInternals Process Monitor (procmon) to see what is being written to a log file for any given procmon trace line.
15. Digital Clock.ps1 – display a digital clock, stop watch (with 0.1 second granularity) or countdown timer with the ability to “mark” specific points, e.g. when timing a logon

Ivanti

1. AMC configuration exporter.ps1 – Export the configuration of one or more AppSense/Ivanti DesktopNow Management Servers to csv or xml file.
2. Get process module info.ps1 – Interrogate running processes to extract file and certificate information for their loaded modules which can be useful in composing Ivanti Application Control configurations.
3. Ivanti UWM EM event processor.ps1 – Get Ivanti UWM EM event log entries and split into sortable table for durations to aid logon analysis. Display on screen in a sortable/filterable grid view or export to a CSV file.

VMware

1. ESXi cloner.ps1 – Create one or more new VMware ESXi virtual machines from existing VMs nominated as templates. For use when not using vCenter which has a built in templating mechansim. Can created linked clones to save on disk space and drastically speed up new VM creation. Can be used with or without a GUI.
2. Get VMware or Hyper-V powered on vm details.ps1 – Retrieves details of all powered on virtual machines, or just those matching a name pattern, from either VMware vSphere/ESXi or Hyper-V and either displays them in an on screen sortable & filterable grid view, standard output for further processing or writes to a text file that can be used in a custom field in SysInternals BGinfo tool to show IP addresses of these VMs on your desktop wallpaper which is useful when they are on an isolated network or not registered in DNS.
3. Power state change running VMs.ps1 – Pause or shutdown running VMs and the ESXi host – designed to be run by UPS shutdown software. Requires the VMware PowerCLI module.
4. VMware GUI.ps1 – Allow users to view VMs and their details that they have access to in a WPF grid view and perform the following actions if they have permissions in VMware as well as being able to launch mstsc and VMware consoles:

• • • • Snapshots – take, delete, revert, consolidate
      • Power – on, off, suspend, shutdown/restart guest
      • Reconfigure – number of CPUs, amount of memory and change notes
      • Delete
      • Screenshot
      • Run scripts/cmdlets/exes
      • Mount/Unmount CDs
      • Connect/Disconnect NICs
      • Show events
      • Backup

1. Set VMware guest info.ps1– Set VM guest information, by connecting to vCenter or ESXi directly, so it can be retrieved in VMs. For example, set the VMware host running the VM in the guest so it knows who its parent is.

Where did that download come from?

I was investigating something completely unrelated recently when I came across the fact that the Zone.Identifier information for downloaded files, on Windows 10, which is stored in NTFS Alternate Data Streams (ADS) on each downloaded file, contains the URL from which the file came. Yes, the whole URL so could potentially be very useful and/or very embarrassing. It’s this Zone.Identifier file that Windows Explorer checks when it puts restrictions on files that it deems could be unsafe because they have come from the internet zone.

Let me illustrate this with an example  where I have downloaded a theme from Microsoft using Chrome version 68 on Windows 10 and saved it into C:\Temp. One can then easily examine the ADS on this downloaded file using PowerShell version 3.0 or higher:

The ZoneId is 3, which is the “Internet” zone as can be checked by looking at the “DisplayName” value in “HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Internet Settings\Zones\3”, and notice that it gives the actual path to where the file came from, which is actually different to the URL that I clicked. I reckon that could be handy if you forget where a particular file came from but also potentially embarrassing/incriminating depending on what you download where clearing your browser history and cache will only delete some of the evidence.

I’ve been aware of the Zone.Identifier ADS for a long time but I only ever remember seeing the zone number in there, not URLs, so I went back to a 2008R2 system, downloaded the same file with IE11 and sure enough there was only the ZoneId line. I then tried IE11 on Windows 10 and it too only had the ZoneId in the ADS file which gave rise to this table for my Windows 10 laptop since the behaviour is browser specific:

Browser	Version	Captures URL in ADS
Internet Explorer	11	No
Edge	42.17134	Yes
Chrome	68	Yes
Firefox	61	No
Tor	7.5.6	No

Although both Chrome and Edge don’t put the URL in the Zone.Identifier ADS when browsing in Incognito and InPrivate modes respectively.

This got me sufficiently interested to write a PowerShell script which finds files with a Zone.Identifier ADS in a given folder, and sub-folders if the -recurse option is specified. The script just outputs the data found so you can pipe it through cmdlets like Export-CSV or Out-GridView – below is an example of piping it through Out-GridView:

The script also has -remove and -scrub options which will either completely remove the Zone.Identifier ADS file or just remove the URLs from it, so keeping the zone information, respectively.

The script is available here and you use it entirely at your own risk.

Petya: disabling remote execution of psexec

This morning in response to reports of an outbreak of yet more malware I wrote a quick blog post on one way to stop the SysInternals psexec from being allowed to execute by using the Image File Execution Options registry key mechanism – see here for that post.

My technique was, and still is sound, but @RennJohnny correctly pointed out that if the psxec.exe executable was renamed then my approach would not work. I therefore set about finding another way to stop psexec from running for those who don’t (yet) have security software in place to stop the exploit. Note that you need to be running it on a system where you are an administrator and have the same rights on the remote system to be attacked. As the disposable virtual machine that I used for my testing is not on a domain, I passed explicit credentials to psexec for my testing – I don’t believe Petya operates this way but my solution will still work.

So when psexec is used to run something on a remote system, it works by creating a new service executable called psexesvc.exe which is embedded within the original psexec.exe file. This is copied to the Windows folder on the remote machine via the admin$ default share (hence why you need to be an admin to get psexec to work remotely). It then creates the PSEXESVC service with this, now local, executable, starts it and then runs the specified command.

What I found was that even when I copied psexec.exe to another file name, the file produced and copied to the remote system was still called psexesvc.exe. This is what happens when you run the copied psexec.exe and tell it to invoke a command on a remote machine:

 

On that remote system we can then see this has been created in the services registry key:

How do we stop it? I reckon that the easiest way is to use good old Image File Execution Options (IFEO) mechanism again but this time we create the key “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Image File Execution Options\PSEXESVC.exe” and in there create the REG_SZ value called “Debugger” and set it to “svchost.exe”. Now when we try and run psexec to execute on the system where we just patched the registry, this happens instead:

What happened? Well when the Service Control Manager (SCM) on the remote machine was asked to start the PSEXESVC service, it started the psexesvc.exe process but the IFEO entry we created caused it to run svchost.exe instead but as that can’t be used as a standalone service, it failed to start so SCM reported this to psexec which is the error we see above. You will also get this in the System event log of the remote system:

One way to roll it out to all your computers is to put the above registry value into a Group Policy Preference that applies to those computers.

You can also create a dummy psexesvc.exe file in your Windows folder, remove all permissions and change the owner to, say, TrustedInstaller, and that will also prevent it from running.

I hope this helps some of you and stay safe (and don’t run routinely with admin privileges!).

Petya: easily disabling access to psexec

So it seems there is yet another piece of ransomware in the wild which is more sophisticated than Wannacry as it uses multiple attack vectors. I have read that one of these is once a machine is infected that it uses the great SysInternals utility psexec.exe, and possibly Microsoft’s command line WMI utility wmic.exe, to spread further. Whilst there are products, like Ivanti Application Control, formerly AppSense Application Manager, that can be used to blacklist these, if you haven’t got those products today then you need a way of stopping these attack vectors. One way would obviously be to delete wmic.exe, or remove NTFS permissions to it, but you can’t do that for psexec since presumably the malware is either downloading it or has it embedded within its payload.

Here is where we can use the little known Image File Execution Options (IFEO) registry key to put a temporary, or permanent, block on these, or any other executables, so they cannot run. We can either get them to fail silently or run a script informing the users that their machine is infected.

IFEO has been around for 20+ years – in fact the very first version of the software that I wrote that became AppSense Application Manager used this feature. It is great for debugging but is also an attack vector itself for malware as it can be used to disable security programs with the same technique. One mitigating factor is that because the key is in HKLM, you need administrative rights to write to it and we don’t let any user run with administrative rights when they are running web browsers, email, Office products, etc. now do we?

So what do I do? In its simplest form, create the key “psexec.exe” in “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Image File Execution Options” and then create a REG_SZ value called “Debugger” and set it to “svchost.exe” as below:

Job done! If you run psexec.exe before adding the above settings you’ll see something like this:

But once the registry key and value are in place we get this instead:

Attack vector thwarted! But aren’t we then running a service because svchost.exe is being run instead? No, service executables can’t just be run from the command line, they need to be invoked via the Service Control Manager (SCM), so this invocation of svchost.exe just fails silently.

Do the same with a wmic.exe key and that’s both supposed attack vectors blocked for now.

You can also set the value to run a script although you need to ensure that the script itself cannot be compromised using file system security. For instance, if I set the Debugger value to this:

"C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe" -file c:\temp\infected.ps1

Note the use of the full path so we don’t accidentally run a malicious powershell.exe (unless your whole system is compromise!) and if using PowerShell rather than cmd.exe ensure that your PowerShell execution policy allows the script to run.

My c:\temp\infected.ps1 script contains just  this:

$null = [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic')

$null = [Microsoft.VisualBasic.Interaction]::MsgBox( "Your system is infected - call IT now!" , 'OkOnly,SystemModal,Critical' , $MyInvocation.MyCommand.Name )

then when psexec is run a user will get this popup:

How do I roll this out quickly in an enterprise? Unless you’ve already got something that can push out registry settings to multiple computers then I would suggest that Group Policy Preferences is one of the easiest ways of achieving this.

When your security software is up to date then you might want to delete the key/value unless you need to run psexec.exe for other reasons (it is a great tool and I hope it isn’t blocked by anti-virus software in the future).

It may also be worth changing the permissions on the sethc.exe (and wmic.exe) keys you create such that they are read-only to everyone, admins included, just in case further malware tries to target these keys. In fact, why not protect the whole IFEO key, particularly if you are letting people logon with administrative rights?

I  hope this helps and stay safe people!

Vulnerability when using shared Windows base user profiles

In many Terminal Services/Remote Desktop and Citrix XenApp envrionments, a common practice is to use a single shared base profile for the users, typically a mandatory profile. Since there is effectively a single copy of this profile, the registry hive contained therein, either in an ntuser.man or ntuser.dat file, must have registry permissions such that the user whose profile gets built on top of this base has full access to it. However, since it is shared across many different users, it has to have a fairly lax set of permissions – typically allowing a group such as “Everyone” to have Full Control which is then propagated to each user’s individual profile as they logon.

What does this actually mean then when two or more users are logged on to the same server at the same time? Well one user can access, and change, another user’s HKEY_CURRENT_USER (HKCU) key via HKEY_USERS\<sid>. As a demonstration, see below where user gl-test02 has accessed the registry for user gl-test01 without even having to know any SIDs:

You might counter that registry editing tools are disabled through group policy but unless you are running something like AppSense Application Manager to give an extra layer of security via it’s Trusted Ownership feature, then a “policy proof” version is easy to produce with a binary editor and a little know how (a useful tool for troubleshooting in “locked down” environments). Even with an extra security layer in place, it is still relatively easy to access the registry via macros in any Microsoft Office product.

Being the responsible chap that I am, I wouldn’t write about a vulnerability without also offering a method of fixing it. All we need to do is to change the registry permissions at logon such that the group ACE (Access Control Entry) with Full Control is removed and replaced with one for the logging on user. I would normally use the Microsoft subinacl.exe utility but I couldn’t seem to get it to work on HKCU so I switched to setacl.exe from Helge Klein available here. I find the syntax a little perverse but it does the job so one shouldn’t grumble in what is a very flexible and granular free tool. Add the following line into a logon script, AppSense Environment Manager logon action, or similar, assuming you have the Everyone Full Control ACE in the base profile and your HKCU is secured:

 

setacl -on hkcu -ot reg -actn trustee 
-trst "n1:everyone;n2:%username%;ta:repltrst;w:d"

This only changes the top level key but as long as inheritance for subkeys is on then this should be enough and is very quick. This then means we get this after logon if we try the same method as above to access gl-test01’s registry hive from gl-test02’s session:

Note that when running setacl you will probably see the following warnings, since it is hopefully being run as a non-administrative user who doesn’t have these privileges but also doesn’t need them for this particular use of setacl.

Privilege 'Restore files and directories' could not be enabled. 
SetACL's powers are restricted. Better run SetACL with admin rights.
Privilege 'Take ownership of files or other objects' could not be enabled. 
SetACL's powers are restricted. Better run SetACL with admin rights.

The mitigating circumstances here are that the information contained within the registry is probably not that sensitive, although there may be third party products that store passwords which could be used nefariously or even just lists of document names worked on which might interest someone.