Category Archives: Microsoft Windows

Quick Tip- Azure SQL Server Connectivity

Symptoms

  1. An application server in Azure can’t connect to an IaaS SQL Server on Windows (also in Azure).
  2. The Connection Troubleshoot utility in the Azure Portal says network connectivity between the App server and SQL server on port 1433 is allowed:
    image
  3. PowerShell Test-NetConnection on the App server shows that communication with the SQL Server is blocked on port 1433
    image

Cause

Windows Firewall on the SQL Server is blocking communications from the App Server

Solution

Add a rule to the Windows Firewall on the SQL Server to allow SQL Traffic. See Microsoft Docs for details on how to do this.

PowerShell- Get Usernames from Windows Security Log

This snippet takes the export of the Windows Security log and returns a list of user ids from within it.

Exporting the Logs

  1. Open Event Viewer in Windows, select the Security Log and choose “Save All Events As….” – save the file as a Comma Delimited CSV.
    EventViewer
  2. Open the exported file in Notepad and add “,Description” to the end of the first line (PowerShell won’t import the description field otherwise)
    Notepad

PowerShell Manipulation

$events=Import-CSV securitylog.csv
$result= foreach ($event in $events) {
(((($event.Description) -Split "`r`n" |
Where-Object {$_ -like '*Account Name:*'}) -Split ":")[1]).trim() }
$result | Sort-Object –Unique

The result is a list of the Account Names found in the file. See GitHub for further info and updates.

Checking Hybrid Benefits in Azure with PowerShell

When using Windows-based Virtual Machines on Microsoft Azure, there’s an option to use “Azure Hybrid Benefit” to re-use existing Windows licenses you own on-premises for workloads now running in the public cloud.

image

If you don’t select this option then your Azure bill will include the cost of a new Windows license for that virtual machine, so it’s important to ensure it is used where you are entitled to do so. If you have a site license, or campus agreement, you may find that you are allowed Hybrid Benefit on all your workloads in Azure.

This PowerShell snippet will list all the Windows Virtual machines (in the current subscription- use Set-AzContext to change that) which are not making use of the Hybrid Benefits- giving you a quick list of VMs to check the settings on.

Get-AzVM | Where-Object {$_.OSProfile.WindowsConfiguration -and !($_.LicenseType)}

CSS, Javascript, Images cause 500 errors after IIS Website upgrade

Symptoms

A website has been copied from a server running IIS 6 (Windows Server 2008) to a server running IIS 10 (Windows Server 2019). Pages do not render correctly, and further investigation shows that pictures, css files, and javascript are all failing with 500 (Internal Server Error) responses. This is shown here in the F12 view in Chrome.

 2020-02-13_11-18-38

Trying to access one of these static files directly also fails and just shows the message “The page cannot be displayed because an internal server error has occurred.

2020-02-13_11-17-35

Cause

In my case, the site was referencing the FontAwesome package which uses the WOFF file type. In the older IIS this MIME type was not registered so required a manual entry in the configuration. In the newer IIS this is handled natively and the manual configuration is now causing a problem rather than remediating one.

Solution

Check the web.config file for a reference to “woff” for example

<mimeMap fileExtension=”.woff” mimetype=”application/font-woff” />

and comment out or remove this line.

2020-02-13_11-20-07

Refresh the page in your browser and it should now load correctly.

Get the Windows Install Date of a remote computer with PowerShell

A quick script that came up in response to a situation where I wanted to know the date a workstation on the network was last built without visiting the machine or interrupting the currently logged on user. PowerShell to the rescue!

The WMI property “InstallDate” comes into play here.

(Get-WmiObject Win32_OperatingSystem).InstallDate

Returns a date: “20180717202039.000000+060” for my workstation. The Get-WmiObject cmdlet can again be leveraged to make this into a more usable PowerShell DateTime object:

(Get-WmiObject Win32_OperatingSystem).ConvertToDateTime( (Get-WmiObject Win32_OperatingSystem).InstallDate )

Which returns 17 July 2018 20:20:39.

Get-WmiObject can also be used on a remote computer, this example would return the date that the computer called WS12000 was built.

(Get-WmiObject win32_OperatingSystem –ComputerName “WS12000”).InstallDate

I’ve taken this work and expanded it into a PowerShell function (available on GitHub). This function “Get-BuildDate” takes one or more computer names and returns a table of build dates and, because it’s handy, the number of days that have passed since that date.

Some example usage would be:

Return the installation date of workstation 40200:

Get-BuildDate  -ComputerNames "WS40200"

Computer BuildDate           DaysSinceLastBuild
-------- ---------           ------------------
WS40200  17/07/2018 20:20:39                 55

Return the installation date of workstations WS40200 and WS46000:

Get-BuildDate ("WS40200","WS46000")

or alternatively using pipeline input:

("WS40200","WS46000") | Get-BuildDate

Finally, return a table of the last build dates for the sequentially numbered computers called WS12300, WS12301, WS12302…. right through to WS12399:

$Computers=((12300..12399) | ForEach-Object{ "WS$_"}) | Get-BuildDate

The script (and any future refinements) is available here: https://github.com/isjwuk/powershell-general/blob/master/Get-BuildDate.ps1