LogParser Loves Security Logs

Just digging something up that I used to use regularly to look for logon events related to a certain username (samAccountName). Thought I’d regurgitate them here for “the next time..”

Three different SQL queries for three different use cases:

Case 1. I know that the logon event that I’m looking for occurred on DC01.oholics.net, I’m therefore going to interrogate the live DC log. The primary username I’m looking for is “jon”, a secondary name shown as “dave”. This could be replaced by a junk string if I’m only really looking for “jon”, or just trim the query (up to you.. ).

Case 2. In my domain, there are three domain controllers, I’m not sure where the logon events happened, so as in Case 1 I search the live DC logs, but this time searching all DC’s logs.

Case 3. I have three months of backed up logs to search through (in C:\TEMP\Logs) for all logon events for samAccount name “jon” (and optionally “dave”, as above). I may splurge out the script that I used to use to backup and clear the event logs next, that could be useful again – I’ve got to clean it first.

Usage: logparser -i:EVT file:<SQLFileName>.sql -o:CSV -resolveSIDs:ON 

Where:

  • The above SQL query is saved as LogParserRedaction.sql in the same location as the LogParser binary.
  • The collection of logs to be redacted are in C:\TEMP\Logs\
  • The output file will be written to C:\TEMP\Output\output.csv

Redacting sensitive content from Windows event logs using LogParser

Consider the scenario: opening a ticket within Azure for an issue with an infrastructure component or security event. IP addresses, domain names and Machine names are classed as sensitive and should not be revealed to MS support staff.

You have a folder filled with event logs from the problem machine(s). You need to redact the above mentioned properties.

By using LogParser with the following sql statement, a CSV file is exported which strips out the sensitive properties, replacing parts of the properties with X’s.

  • OHOLICS‘ is replaced by XXXXXXX‘ where it is found in an event log, in the Strings, ComputerName, Message or Data fields
  • The first two octets of an IP address are stripped, where these are ‘192.168.’ in the Strings, Message or Data fields
  • blog.oholics.net‘ is replaced by blog.XXXXXXX.net‘ where it is found in an event log, in the Strings, ComputerName, Message or Data fields

Note that after the output file is created, the header row will need to be updated to remove the replace statements. Where normally just the item name would be added as a header, the full replace query is added as the header for those items.

Usage: logparser -i:EVT file:LogParserRedaction.sql -o:CSV -resolveSIDs:ON 

Where:

  • The above SQL query is saved as LogParserRedaction.sql in the same location as the LogParser binary.
  • The collection of logs to be redacted are in C:\TEMP\Logs\
  • The output file will be written to C:\TEMP\Output\OUTPUT.CSV

Azure Service Principle Authentication

I have recently been working within a client where all Azure/ Office 365 users must perform MFA on logon.

Ages ago I posted about using credential manager to automate Office 365 scripts: https://blog.oholics.net/using-credential-manager-to-authenticate-office-365-scripts/. This method will clearly not suffice where MFA is enforced, as there is no mechanism to allow MFA challenge and response.

Recently I have been looking into using Azure Service Principle objects to bypass MFA and to allow scripts, that need to connect to Azure or other services, to do so without input. Thus, I can then schedule scripted tasks to generate reports on Azure AD objects or AzureRM items.

Firstly I need to create some certificates, these will be used to authenticate, see here: https://blog.oholics.net/creating-simple-ssl-certificates-for-server-authentication-using-openssl/ for details on certificate creation.

Next, once we have the PFX certificate file, we can create the Azure App Registration, using PowerShell:

Then (optionally), if the script that you want to automate will be reading AzureRM objects, run the following script. Note that if the role assignment is to be constrained to a specific resource group, add the  -ResourceGroupName switch to New-AzureRMRoleAssignment

Additionally, the RoleDefinitionName can be altered to suit.

Now we have the Service Principle in place, we can connect! But in the case of the Azure AD connection, I need to first allow the application to Read AAD:

Go to the App Registrations blade in Azure AD, pick the application created earlier, then select settings. Select Required Permissions, add Azure AD and add the permissions shown in the following image:

Now lets connect using the certificate thumbprint:

By installing the certificate in the CurrentUser store, only that user can consume the certificate thumbprint for authentication using this method. Lovely.. 🙂

Why is this method secure?

  • You can only access the application to sign in if you have installed the certificate on the machine that you want to run the script from.
  • To install the certificate, you must know the password that was set on the private key during PFX creation.
  • No AAD user object is created
  • No plain text passwords need to be stored

To sign in using this method, you must know:

  1. The AAD Tenant GUID
  2. The Application GUID of the configured application
  3. The specific thumbprint of the certificate, used to make the connection
  4. The certificate and private key must be installed on the machine on which the connection attempt is being made.

Can’t rename, move or delete an OU

Today, I came across something that had me quite stumped…. well for a few minutes anyway 🙂

I was doing some tidying up of a domain, I found an OU that was incorrectly named, it was not to design. I thought, I’ll just rename it, but found that the option to do so was not available.

I took a look at the attributes of the OU, two immediately struck me as odd:

systemFlags was set to DISALLOW_DELETE|DOMAIN_DISALLOW_RENAME|DOMAIN_DISALLOW_MOVE

 

 

 

 

 

 

 

 

 

isCriticalSystemObject was set to TRUE:

 

 

 

 

 

 

 

 

 

Neither of these attributes could be modified, an error was thrown if attempted.

The simple answer: This OU had been set as the default location for new computer objects via redircmp 

By running redircmp CN=Computers,DC=oholics,DC=net (or your other true destination):

  • The systemFlags attribute was banished
  • The isCriticalSystemObject attribute was set to FALSE
  • The OU could be renamed, moved and deleted 😉

Creating simple SSL certificates for server authentication using OpenSSL

It is often useful to have a standalone and reliable process for provisioning SSL certificates, using an existing CA (internal or public) for use on enterprise servers.

This process makes use of OpenSSL, the Windows binaries for which can be found here: https://slproweb.com/products/Win32OpenSSL.html

Once installed, use an administrative command prompt and navigate to C:\OpenSSL-Win64\bin.

Use the following OpenSSL configuration file (backup the original first) in the bin directory:

Edit line 232 to define the first SAN for the certificate, this should match the common name of your certificate. Add further SAN’s in the subsequent lines.

For example, if my server advertised DNS name is blog.oholics.net, but I also want the root domain to be added as a SAN, then DNS.1 = blog.oholics.net and DNS.2 = oholics.net.

Run the following commands.

Generate the private key:

  • openssl genrsa -out blog.oholics.net.key 2048 (note: amend the numbits value as appropriate)

Generate the CSR, amending the country name and other values as appropriate, add the CN of the server when prompted:

  • openssl req -new -key blog.oholics.net.key -out blog.oholics.net.csr

Validate the CSR – check that the SAN’s are correct

  • openssl req -in blog.oholics.net.csr -noout -text

Once happy, submit the CSR to your CA. Wait for the response, save the file as blog.oholics.net.crt

Now, to combine the certificate file and the private key into a pfx file (providing a secure password when prompted):

openssl pkcs12 -export -out blog.oholics.net.pfx -inkey blog.oholics.net.key -in blog.oholics.net.crt -certfile blog.oholics.net.crt

Use the resulting file as you desire 🙂

PowerShell Module for AD/ ADLDS Schema modification

A couple of years ago a colleague within my company (Avanade) published a link to a GitHub project that he had just completed: https://github.com/SchneiderAndy/ADSchema

I had just finished working on a project using MIM to synchronise identities and groups from two domains into one Microsoft ADLDS instance, using the ProxyUser class to allow ADLDS to become a common authenticator for a divestment. While proving out the solution, the target ADLDS instance was trashed and rebuilt countless times. The rebuilds were time consuming and boring. With this use case in mind, I took a fork of Andy’s solution and spent a few months (off and on) to modify the module to allow its use against ADLDS, as the methods used to interact with ADLDS were often very different.

My version of the module can be found here: https://github.com/jkbryan/ADSchema, the detailed usage examples are detailed in the readme file.

If you want to give it a try, please, please test against something non-production! I will not be held responsible for any mistakes made while using the module. Test, test and test again before using it in a production environment!

Time flies when you are having fun!

Ha-ha! only one year (and a bit later) I finally finished the tidy up. All code samples are now in github gists. Crayon Syntax Highlighter is no more, it was a good plugin, but time for something better.

I found some odd formatting issues in some of the code samples. I reckon most of these are a legacy from when the blog was running on a Raspberry Pi and the cheap 4GB SD cards the the blog was running on were getting corrupted almost once a week. I used to spend hours every week recovering either the database or the entire disk image. Ahhh, such fond memories :), but a great way to learn.

It has been a super busy year, learning some new stuff focused on Azure infrastructure, Azure RBAC and AAD authentication to legacy applications. I’ll publish some content related to these activities soon.

Migration complete

I’m pleased to say that I have now finalised the migration of content from fim.oholics.net, script.oholics.net, rpi.oholics.net and 365.oholics.net into this new blog site. All re-directors/ etc. appear to be working as planned.

I have noticed a few formatting issues with the Crayon syntax highlighter plugin on some posts, so have migrated those that were ‘broken’ to GitHub Gists. I’ll eventually migrate all of the code samples/ snippets to Gists, as they should display more consistently and remove the need for rendering each sample by WordPress.