Category: Windows 2019

Scheduled export of the security log

If you have trouble with the log being overwritten before you can read it and do not want to increase the size of the log further, you can use a scheduled PowerShell script to create regular exports. The script below creates csv files that can easily be imported to a database for further analysis.

The account running the scheduled task needs to be a local admin on the computer.

#######################################################################################################################
#   _____     __     ______     ______     __  __     ______     ______     _____     ______     ______     ______    #
#  /\  __-.  /\ \   /\___  \   /\___  \   /\ \_\ \   /\  == \   /\  __ \   /\  __-.  /\  ___\   /\  ___\   /\  == \   #
#  \ \ \/\ \ \ \ \  \/_/  /__  \/_/  /__  \ \____ \  \ \  __<   \ \  __ \  \ \ \/\ \ \ \ \__ \  \ \  __\   \ \  __<   #
#   \ \____-  \ \_\   /\_____\   /\_____\  \/\_____\  \ \_____\  \ \_\ \_\  \ \____-  \ \_____\  \ \_____\  \ \_\ \_\ #
#    \/____/   \/_/   \/_____/   \/_____/   \/_____/   \/_____/   \/_/\/_/   \/____/   \/_____/   \/_____/   \/_/ /_/ #
#                                                                                                                     #
#                                                   http://lokna.no                                                   #
#---------------------------------------------------------------------------------------------------------------------#
#                                          -----=== Elevation required ===----                                        #
#---------------------------------------------------------------------------------------------------------------------#
# Purpose:Export and store the security event log as csv.                                                             #
#                                                                                                                     #
#=====================================================================================================================#
# Notes: Schedule execution of tihis script every capturehrs hours - script execution time.                           #
# Test the script to determine the execution time, add 2 minutes for good measure.                                    #
#                                                                                                                     #
# Scheduled task: powershell.exe -ExecutionPolicy ByPass -File ExportSecurityEvents.ps1                               #
#######################################################################################################################

#Config
$path = "C:\log\security\" # Add Path, end with a backslash
$captureHrs = 20 #Capture n hours of data

#Execute
$now=Get-Date
$CaptureTime = (Get-Date -Format "yyyyMMddHHmmss")
$CaptureFrom = $now.AddHours(-$captureHrs)
$Filename = $path + $CaptureTime + 'Security_log.csv' 
$log = Get-EventLog -LogName Security -After $CaptureFrom
$log|Export-Csv $Filename -NoTypeInformation -Delimiter ";"
Last edit: Friday, October 25, 2019

iSCSI in the LAB

I sometimes run internal Windows Failover Clustering training, and I am sometimes asked “How can I test this at home when I do not have a SAN?”. As you may know, even though clusters without shared storage are in deed possible in the current year, some cluster types still rely heavily on shared storage. When it comes to SQL Server clusters for instance, a Failover Cluster instance (which relies on shared storage) is a lot easier to operate compared to an AOAG cluster which does not rely on shared storage. There are a lot of possible solutions to this problem, you could for instance use your home NAS as an iSCSI “SAN”, as many popular NAS boxes have iSCSI support. In this post however, I will focus on how to build a Windows Server 2019 vm with an iSCSI target for LAB usage. This is NOT intended as a guide for creating a production iSCSI server. It is most definitely a bad iSCSI server with poor performance and not suited for anything requiring production level performance.

“What will I need to do this?” I hear you ask. You will need a Windows Server 2019 VM with some spare storage mounted as a secondary drive, a domain controller VM and some cluster VMs. You could also use a physical machine if you want, but usually this kind of setup involves one physical machine running a set of VMs. In this setup I have four VMs:

  • DC03, the domain controller
  • SQL19-1 and SQL19-2, the cluster nodes for a SQL 2019 CTP 2.2 failover cluster instance
  • iSCSI19, the iSCSI server.

The domain controller and SQL Servers are not covered in this post. See my Failover Cluster series for information on how to build a cluster.

Action plan

  • Make sure that your domain controller and iSCSI client VMs are running.
  • Make sure that Failover Cluster features are installed on the client VMs.
  • Enable the iSCSI service on your cluster nodes that are going to use the iSCSI server. All you have to do is to start the iSCSI initiator program, it will ask you to enable the iSCSI service:
  • clip_image001

  • Create a Windows 2019 Server VM or physical server.
  • Add it to your lab domain.
  • Set a static IP, both v4 and v6. This is important for stability, iSCSI does not play well with DHCP. In fact, all servers involved in this should have static IPs to ensure that the iSCSI storage is reconnected properly at boot.
  • Install the iSCSI target feature using powershell.
    • Install-WindowsFeature FS-iSCSITarget-Server
  • Add a virtual hard drive to serve as storage for the iSCSI volumes.
  • Initialize the drive, add a volume and format it. Assign the drive letter I:

  • clip_image002

  • Open Server Manager and navigate to File and Storage Services, iSCSI
  • Start the “New iSCSI virtual disk wizard”
  • Select the I: drive as a location for the new virtual disk

  • clip_image003

  • Select a name for you disk. Here, I have called it SQL19_Backup01
  • clip_image004

  • Set a disk size in accordance with your needs. As this is for a LAB environment, select a Dynamically expanding disk type.
  • clip_image005

  • Create a new target. I used SQL19Cluster as the name for the target.
  • Add the cluster nodes as initiators. You should have enabled iSCSI on the cluster nodes before this step (see above). The cluster nodes also has to be online for this to be successful.
  • clip_image006

  • As this is a lab we skip the authentication setup
  • clip_image007

  • Done!
  • clip_image008

  • On the cluster nodes, start the iSCSI initator.
  • Input the name of the iSCSI target and click the quick connect button.
  • clip_image009

  • Initialize, online and format the disk on one of the cluster nodes
  • clip_image010

  • Run storage validation in failover cluster manager to verify your setup. Make sure that your disk/disks are part of the storage validation. You may have to add the disk to the cluster and set it offline for validation to work.
  • Check that the disk fails over properly to the other node. It should appear with the same drive letter or mapped folder on both nodes, but only on one node at the time unless you convert it to a Cluster Shared Volume. (Cluster Shared Volumes are for Hyper-V Clusters).
Last edit: Saturday, February 2, 2019

Static IPv6 Cluster address

Problem

If you install a Windows Failover cluster using IPv6, you will get a dynamic IPv6 address as the standard failover clustering management tools only support dynamic IPv6 addresses. If you live in Microsoft Wonderland (a mythical land without any firewalls) this is fine, but for the rest of us, this is kind of impractical. We need a static address.

Solution

Run this script to add a static address. Then you have to find and remove the dynamic address. You can do this in Failover Cluster Manager or through powershell, but you have to identify it first so I cannot script it for you.

The script defaults to the Cluster Group resource group, but you can do the same for say a SQL Server instance or any other cluster group with IP addresses and network names.

#Add a static IPv6 address to the cluster group and Network name specified
$IPv6="STATIC ADDRESS HERE"
$IPv6Name="IP Address $IPv6" #Do not change this line
$Group= "Cluster Group"
$NetworkName = "Cluster Name"
                               
Add-ClusterResource -name $IPv6Name -ResourceType "IPv6 Address" -Group $Group
Get-ClusterResource -Name $IPv6Name | Get-ClusterParameter
Get-ClusterResource -Name $IPv6Name | Set-ClusterParameter -Multiple @{"PrefixLength" = "64";"Address"= $IPv6}
Get-ClusterResource -Name $IPv6Name | Start-ClusterResource
Stop-ClusterResource $NetworkName
Add-ClusterResourceDependency $IPv6Name -Resource $NetworkName
Start-ClusterResource $NetworkName

Last edit: Sunday, January 27, 2019

Disable automatic loading of Server Manager

Problem

When you log in to a server, the Server Manager windows loads automatically. On a small VM this can take some time and waste som e resources, especially if you forget to close it and log off.

Solution

Create a GPO to disable the automatic loading of Server Manager.

  • Start Group Policy Managment Editor
  • Create and link a new GPO on the OU/OUs where you want to apply it.
  • Find the setting Computer Configuration\Policies\Administrative Templates\System\Server Manager\Do not display Server Manager at logon
  • Enable it
  • close the GPO and wait for a GPO refresh, or trigger a gpupdate /force on any applicable computers.

image

Last edit: Wednesday, January 23, 2019