30 December 2020

How to cleanup expired certificates from a Microsoft CA with PowerShell and Shrink the DB



This a shameless copy of the original post by André Gibel over at https://www.gibel.net/

The reason why I copied it is because there is very little info on this subject and even though the post is from 2014, it still applies today.

Regularly (depending on the number of issued certificates) you have to perform a clean-up of expired certificates from your CA (Certification Authority) DB and then shrink the DB to get rid of the “white space”.

You have to perform the following 3 steps in order:
1. Make a backup of your CA DB (protected with a password) to another Server / medium





- this backup also "removes" the maybe hundres of db log files (each of the has a size of 1 MB) – in my case 828



2.      Clean-up all expired certificates from all 4 categories  with my PowerShell Script


- in a first step it's the best to run the script in a "view only" modus to see which certificates would be deleted
- the script and all the details are explained 
below.

3.      Shrink your CA database to get rid of the “whitespace”

- for this you use the esentutl tool with the “/d” (= defragmentation) option

Before executing the esentutl command stop the AD Certificate service and disable it




- run the following command with the path to the .edb DB file



- at the end the DB - file is more than 100 MB smaller than before, depending on the size your database is.



- at this point you have to enable and start the CA Service again

Here I explain the PowerShell script in detail (the script is used in step 2)

The Microsoft Enterprise CA I’m responsible for is running on a Microsoft Windows Server 2008 Enterprise Server

- with PowerShell 2.0 installed
- no 3rd party PS modules are used
- the certutil.exe is used by the PowerShell (PS) script
- the PS script I created is "Cleanup_MSPKI_Cert_v1.1.ps1" and contains 3 functions

On this CA Server in the C:\ root drive I create a folder “_scripts “ (I don’t use PS remoting) and copy my PowerShell script “Cleanup_MSPKI_Cert_v1.1.ps1” into this folder


Per default the functions "Remove-ExpiredCertFromDB" writes the temporary files to a subfolder within C:\_scripts\PKICleanupLog.

You can change this default folder path with the parameter  “CleanedFolderLogPath”


The 3 functions I have implemented are:

A.) Get-PublishedCATemplate
B.) Get-IssuedCert
C.) Remove-ExpiredCertFromDB

A.) Get-PublishedCATemplate


When you run this function without a parameter, it displays all Templates from the "Certificate Templates" folder with it's OID. This OID is used by the other to functions to display or delete certificates issued with this certain template. In the following picture you see the corresponding templates from the PKI Snap In



function Get-PublishedCATemplate{             

    [CmdletBinding()]

    Param (

        [parameter()]

        [string]$filter   

    )      

    $FilterLen = ("msPKI-Cert-Template-OID =").length+3   

    $AllPublishedTemplates = Invoke-Expression "certutil.exe –catemplates –v | select-string msPKI-Cert-Template-OID"     

    $AllPublishedTemplates | foreach{       

        $tmp= ($_.line).Substring($FilterLen)       

        $splitarr = $tmp.split(" ",2)     

        $obj = New-Object PSObject                                     

        Add-Member -Input $obj -Name "name" -MemberType Noteproperty -Value $Splitarr[1].trim()

        Add-Member -Input $obj -Name "oid" -MemberType Noteproperty -Value $Splitarr[0].trim()              

        if ($PSBoundParameters["filter"]){  

            if ($Splitarr[1].trim() -match $filter){

                write-output $obj             

            }

        }

        else{

            write-output $obj             

        }

    }              

}

Below I run the script with the -filter parameter and so I only get templates with “SCCM” in their name



I assign the oid of ONE template (=> change filter that you get only one result)  to the variable WSTemplate

$WSTemplate = (Get-PublishedCATemplate -filter workstation).oid


B.) Get-IssuedCert

With this function  you can list the certificates  issued from all templates or a certain template (specified with it’s oid = $CertTemplate variable)  which are issued beginning at a certain date.

 function Get-IssuedCert{

  [CmdletBinding()]

  Param (

    [ValidatePattern('^([0-9\.\s])+$')]

    [string]$CertTemplate,

    [ValidatePattern('^\d\d[\./]{1}\d\d[\./]{1}\d\d\d\d$')]

    [string]$Date

  )

  if ($PSBoundParameters["CertTemplate"]){   

    Invoke-Expression "certutil.exe -view -restrict 'certificate template=$CertTemplate,disposition=20,notbefore>=$Date' -out 'Request.RequestID,Request.RequesterName,NotBefore,NotAfter,Request.Disposition'"       

  }

  else {

    # displays Certificates issued with any custom template   

    Invoke-Expression "certutil.exe -view -restrict 'disposition=20,notbefore>=$Date' -out 'Request.RequestID,Request.RequesterName,NotBefore,NotAfter,Request.Disposition'"             

  }

}

 

The following example lists all  29 certificates (from ALL templates) issued from 18 December 2014 and later …. (with this version it’s not possible to select a time range / only a “start-date”)

Get-IssuedCert  -Date 18.12.2014


The following example lists ONLY the 3 certificates which are issued with the Template $WSTemplate (OID of  “…- Workstation – Authentication Certificate”) beginning December 18. 2014

$WSTemplate = (Get-PublishedCATemplate -filter workstation).oid
Get-IssuedCert -CertTemplate $WSTemplate -Date 18.12.2014



C.) Remove-ExpiredCertFromDB

This is an advanced function and all available parameters are displayed with the get-help command

- the expired certificates to view (1st step) and then delete are in one of 4 folders


- I select this “folder” with the -state parameter


- the script creates a log file (also needed for further parsing) in a separate folder


These folders are created automatically if they don’t exist yet.
In a first step always run the cmdlet without the "-delete" parameter so nothing is really deleted.
I also recommend the ISE instead of the shell.
And I also always run this cmdlet with the “-verbose” parameter.

The following example displays all issued (and expired) certificates till 18.12.2014  --- they are not really deleted yet.

Remove-ExpiredCertFromDB -State issued -Date 18.12.2014  -Verbose 


Without the -delete switch parameter the log file has "-ViewOnly" in it’s name

Below is the output from the example above / 396 entries “would be” deleted from the “issued folder” (or category) if you run the cmdlet with “-delete”


The following example lists / deletes  certificates from a certain (workstation authentication) template expired up to 18 december 2014

$WSTemplate = (Get-PublishedCATemplate -filter workstation).oid
Remove-ExpiredCertFromDB -State issued -CertTemplate $WSTemplate -Date 18.12.2014 -Verbose


With the added-delete switch parameter you really delete the entries
T
his step can take some time if there are a lot of entries.

$WSTemplate = (Get-PublishedCATemplate -filter workstation).oid
Remove-ExpiredCertFromDB -State issued -CertTemplate $WSTemplate -Date 18.12.2014 -Verbose -delete


The output at the end (and the log file)


When  you run the same cmdlet again, you see that there aren’t any entries to delete from the DB



The latest (full) version of this script with the 3 functions you can download from the Microsoft Script Gallery: go to download

DirectDownload link

Since TechNet is retired and will be taken offline any time soon a backup can be downloaded here:

Cleanup_MSPKI_Cert_v1.2.ps1







23 December 2020

Convert security group to mail enabled security group with Powershell

Find your AD group name:

Get-ADGroup -Identity "groupname"

Enable the group for Email:

Enable-DistributionGroup -Identity "groupname" -PrimarySMTPAddress groupname@domain.com

21 December 2020

Download Files Through Authenticating Proxy with PowerShell

Download Files Through Authenticating Proxy with PowerShell

1st Method:
$source = "https://www.7-zip.org/a/7z1900-x64.exe";            
$dest = "C:\Temp\7z1900-x64.exe";            
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12            
$WebClient = New-Object System.Net.WebClient;            
$WebProxy = New-Object System.Net.WebProxy("http://proxy:8080",$true);            
$Credentials = (New-Object Net.NetworkCredential("USERNAME","PASSWORD","domain.com")).GetCredential("http://proxy","80", "KERBEROS");            
#$Credentials = [System.Net.CredentialCache]::DefaultNetworkCredentials            
$WebProxy.Credentials = $Credentials;            
$WebClient.Proxy = $WebProxy;            
$WebClient.DownloadFile($source,$dest);
Alternative Methods:
New-Item -ItemType Directory -Force -Path C:\Temp # Create Temp folder if it doesn't already exist            
$proxy="http://proxy:8080";            
$exclusionList="localhost;*.domain.local;10.*"
# Set winhttp proxy for PowerShell
netsh winhttp set proxy $proxy $exclusionList
# Prepare PowerShell to Use Default Credentials
[system.net.webrequest]::defaultwebproxy = New-Object system.net.webproxy($proxy)            
[system.net.webrequest]::defaultwebproxy.credentials = [System.Net.CredentialCache]::DefaultNetworkCredentials            
[system.net.webrequest]::defaultwebproxy.BypassProxyOnLocal = $true            
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
# Download using the most efficient method
$url = "https://www.7-zip.org/a/7z1900-x64.exe"             
$temp = "C:\Temp\7z1900-x64.exe"            
Import-Module BitsTransfer            
Start-BitsTransfer -Source $url -Destination $temp
Or:
$source = "https://www.7-zip.org/a/7z1900-x64.msi";            
$dest = "C:\Temp\7z1900-x64.msi";            
$browser = New-Object System.Net.WebClient            
$browser.Proxy.Credentials =[System.Net.CredentialCache]::DefaultNetworkCredentials;            
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12            
$browser.DownloadFile($source,$dest);
Or:
$source = "https://www.7-zip.org/a/7z1900-x64.msi";            
$dest = "C:\Temp\7z1900-x64.msi";            
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12            
(new-object System.Net.WebClient).DownloadFile($source,$dest)
Or:
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12            
[Net.WebRequest]::DefaultWebProxy.Credentials = [Net.CredentialCache]::DefaultCredentials; `
iex ((New-Object Net.WebClient).DownloadString('https://www.7-zip.org/a/7z1900-x64.msi'))
Or:
$page = (new-object net.webclient)            
$page.UseDefaultCredentials = $True            
$Page.DownloadString('https://www.7-zip.org/a/7z1900-x64.msi')

17 December 2020

Azure Log Analytics - The client and server cannot communicate, because they do not possess a common algorithm

The client and server cannot communicate, because they do not possess a common algorithm

Well the error itself reveals a little bit of info about what it involves, algorithms.
Where are algorithms used? Well secure connections, such as TLS.

If you have TLS 1.2 enabled chances are your application defaults to TLS 1.0 which you probably have disabled just a did for TLS1.1.

If you have servers that have .Net 4.5.2 and below, and you have a .Net application that is trying to connect to something using TLS or Schannel, it will default to TLS 1.0 and fail if you have disabled tls 1.0 and 1.1.
But you can fix this by adding a registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319

and

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v4.0.30319

called SchUseStrongCrypto and set that to 1.

After a reboot, the .Net applications will be forced to use TLS.1.2 and connect.

Full error from the monitoringAgent.log file:
Failed to connect, exception : System.Net.WebException: The underlying connection was closed: An unexpected error occurred on a receive. ---> System.ComponentModel.Win32Exception: The client and server cannot communicate, because they do not possess a common algorithm

01 November 2020

Add an emailaddress to the safe senders list in Outlook for all Office365 mailboxes

 Adding an email address to be trusted for whatever reason to your environment depends on what email anti spam/phish/malware/virus solution you have.

But here's one that's always in the way. Outlook.

Here's how to add an email address to the safe senders list in Outlook for all your mailboxes in Exchange Online:

#Add trusted sender and domain per user:            
Set-MailboxJunkEmailConfiguration firstname.lastname@domain.com  -TrustedSendersAndDomains @{Add="@something.com","info@something.com"}             
            
#Add Blocked sender and domain per user:            
Set-MailboxJunkEmailConfiguration firstname.lastname@domain.com  -BlockedSendersAndDomains @{Add="@something.com","info@something.com"}            
            
#Add emailaddress to the trusted list in all mailboxes:            
Get-Mailbox -resultsize unlimited | Set-MailboxJunkEmailConfiguration -TrustedSendersAndDomains @{Add="quarantine@messaging.microsoft.com"}            
            
#Add a domain and an email address to the trusted list in all mailboxes:            
Get-Mailbox -resultsize unlimited | Set-MailboxJunkEmailConfiguration -BlockedSendersAndDomains @{Add="domain1.com", "user@domain2.com"}            

Install Office365 requirements with PowerShell - SkypeOnline - ExchangeOnline - AzureAD - SharepointOnline - Teams PowerShell modules

Updated - 01-11-2020

  • Replaced the SharePoint Online installer with the PowerShell Gallery module
  • Added Exchange Online V2 module
  • Updated SharePoint Online PowerShell module URL
  • Added Teams PowerShell module
  • Added AZ PowerShell module
  • Added Intune PowerShell module
  • Added Staff Hub PowerShell module

I came across a script by Chris Goosen to connect to all of the Office 365 services via PowerShell.
When I tried to run it errors were flying everywhere.
All of the requirements were missing on my system.

So that's what I came up with, a one stop way to get all of those requirements in one single go.
<#
.SYNOPSIS 
Install Office365 PowerShell Prerequisites
 
.DESCRIPTION  
Downloads and installs the AzureAD, Sharepoint Online, Skype Online for Windows PowerShell etc.

.Made by 
Edwin van Brenk

.Date
01-11-2020

.Source
https://vanbrenk.blogspot.com/2018/03/install-office365-requirements-with.html
#>            
                       
<#
Function InstallSharepointOnlinePowerShellModule() {

$SharepointOnlinePowerShellModuleSourceURL = "https://download.microsoft.com/download/0/2/E/02E7E5BA-2190-44A8-B407-BC73CA0D6B87/SharePointOnlineManagementShell_8525-1200_x64_en-us.msi"

$DestinationFolder = "C:\Temp"

     If (!(Test-Path $DestinationFolder))
     {
         New-Item $DestinationFolder -ItemType Directory -Force
     }

Write-Host "Downloading Sharepoint Online PowerShell Module from $SharepointOnlinePowerShellModuleSourceURL"

     try
     {
         Invoke-WebRequest -Uri $SharepointOnlinePowerShellModuleSourceURL -OutFile "$DestinationFolder\SharePointOnlineManagementShell_7414-1200_x64_en-us.msi" -ErrorAction STOP

$msifile = "$DestinationFolder\SharePointOnlineManagementShell_7414-1200_x64_en-us.msi"
$arguments = @(
          "/i"
          "`"$msiFile`""
          "/passive"
)

Write-Host "Attempting to install $msifile"

         $process = Start-Process -FilePath msiexec.exe -Wait -PassThru -ArgumentList $arguments
         if ($process.ExitCode -eq 0)
         {
             Write-Host "$msiFile has been successfully installed"
         }
         else
         {
             Write-Host "installer exit code  $($process.ExitCode) for file  $($msifile)"
         }

     }
     catch
     {
         Write-Host $_.Exception.Message
     }
 }

InstallSharepointOnlinePowerShellModule
#>            
            
# Download and Install Visual Studio C++ 2017                        
$VisualStudio2017x64URL = "https://download.visualstudio.microsoft.com/download/pr/11687625/2cd2dba5748dc95950a5c42c2d2d78e4/VC_redist.x64.exe"            
Write-Host "Downloading VisualStudio 2017 C++ from $VisualStudio2017x64"                         
            
$DestinationFolder = "C:\Temp"            
            
Invoke-WebRequest -Uri $VisualStudio2017x64URL -OutFile "$DestinationFolder\VC_redist.x64.exe" -ErrorAction STOP            
            
Write-Host "Attempting to install VisualStudio 2017 C++, a reboot is required!"            
            
Start-Process "$DestinationFolder\VC_redist.x64.exe" -ArgumentList "/passive /norestart" -Wait            
            
Write-Host "Attempting to install VisualStudio 2017 C++"            
            
# Download and Install Skype Online PowerShell module            
$SkypeOnlinePowerShellModuleSourceURL = "https://download.microsoft.com/download/2/0/5/2050B39B-4DA5-48E0-B768-583533B42C3B/SkypeOnlinePowerShell.Exe"            
            
$DestinationFolder = "C:\Temp"            
            
     If (!(Test-Path $DestinationFolder))            
     {            
         New-Item $DestinationFolder -ItemType Directory -Force            
     }            
            
Write-Host "Downloading Skype Online PowerShell Module from $SkypeOnlinePowerShellModuleSourceURL"            
            
Invoke-WebRequest -Uri $SkypeOnlinePowerShellModuleSourceURL -OutFile "$DestinationFolder\SkypeOnlinePowerShell.Exe" -ErrorAction STOP            
            
Start-Process "$DestinationFolder\SkypeOnlinePowerShell.Exe" -ArgumentList "/quiet" -Wait            
            
# Register PSGallery PSprovider and set as Trusted source            
Register-PSRepository -Name PSGallery -SourceLocation https://www.powershellgallery.com/api/v2/ -PublishLocation https://www.powershellgallery.com/api/v2/package/             
-ScriptSourceLocation https://www.powershellgallery.com/api/v2/items/psscript/ -ScriptPublishLocation https://www.powershellgallery.com/api/v2/package/ -InstallationPolicy             
Trusted -PackageManagementProvider NuGet -ErrorAction SilentlyContinue                        
Register-PSRepository -Default -ErrorAction SilentlyContinue            
Set-PSRepository -Name psgallery -InstallationPolicy trusted            
            
# Install modules from PSGallery            
Install-Module -Name AADRM -Force            
Install-Module -Name AzureADPreview -Force            
Install-Module -Name AzureAD -Force            
Install-Module -Name MSOnline -Force            
Install-Module -Name AZ -Force            
Install-Module -Name MicrosoftTeams -Force            
Install-Module -Name Microsoft.Graph.Intune -Force            
Install-Module -Name Microsoft.Online.SharePoint.PowerShell -Force            
            
# Manually install Exchange Online with MFA authentication support from the Exchange Online ECP            
Write-Host "Login, go to Hybrid and download the Exchange Online Powershell module"            
Start-Process https://outlook.office365.com/ecp/

Add Color coded PowerShell code to your Blogger page

I wanted a nicer way to show PowerShell code on my page here.
So after a little bit of searching a came across the PowerShell ISE.
Now thats nothing new, but what i didn't know is that you can add "add ons" to the ISE and with that it becomes even more fun.

To be able to add color coded PowerShell code to your blog install the module "IsePackV2"

If you haven't set Chocolatey and PSGallery as Packagesource and trusted them then run:
Get-PackageProvider -Name PSGallery -Force
Set-PackageSource -Name PSGallery -Trusted
Get-PackageProvider -Name Chocolatey -Force
Set-PackageSource -Name Chocolatey -Trusted
This Add on needs some additional modules so we'll install all in one go.
You can run this in PowerShell Ise or regular PowerShell but both must be run with elevated permissions:
Install-Module IsePackV2 -AllowClobber -Force           
Install-Module ShowUI -AllowClobber -Force            
Install-Module RoughDraft -AllowClobber -Force            
Install-Module Pipeworks -AllowClobber -Force            
Install-Module EZOut -AllowClobber -Force            
Install-Module ScriptCop -AllowClobber -Force            
Import-Module IsePackV2
When running the PowerShell ISE as Administrator and running Import-Module IsePackV2 there will be some error. I don't know where it comes from, i'll have to look in to that later.
But for now the thing your looking for is the add on we just added.





















The way this is as follows.
Your type some stuff in the script pane in the ISE.

















Select and copy it, go to Add-ons -> IsePack -> Edit -> Copy-ColoredAsHtml.
As soon as you click "Copy-ColoredAsHtml" it on your clipboard.
Now go to your blog editor and paste the text in the Html editor page:













And then it will look like this on your blog:



18 September 2020

You don't have sufficient permissions. This operation can only be performed by a manager of the group

You don't have sufficient permissions. This operation can only be performed by a manager of the group.

Say what? I'm the Exchange admin.

I don't know why this happens, but there's a way to get around it.

C:\>Set-DistributionGroup -Identity "yourdistributiongroupname" -ManagedBy "username"
You don't have sufficient permissions. This operation can only be performed by a manager of the group.
    + CategoryInfo          : NotSpecified: (:) [Set-DistributionGroup], OperationRequiresGroupManagerException
    + FullyQualifiedErrorId : [Server=SR-xxxxx,RequestId=3aed658e-071f-4ddc-abd9-04cf5450b8b8,TimeStamp=18-9-2020 12:2
   3:03] [FailureCategory=Cmdlet-OperationRequiresGroupManagerException] 22D1312B,Microsoft.Exchange.Management.Recip
  ientTasks.SetDistributionGroup
    + PSComputerName        : sr-xxxxx.domain.lan

To solve it add -BypassSecurityGroupManagerCheck
C:\>Set-DistributionGroup -Identity "yourdistributiongroupname" -ManagedBy 'username' -BypassSecurityGroupManagerCheck
Now you can manage the group like the admin that you are.

07 September 2020

How old is your Active Directory?

 How old is your Active Directory?


([adsi]"LDAP://CN=krbtgt,CN=Users,$(([adsi]("LDAP://RootDSE")).defaultNamingContext)").whenCreated.Value

28 August 2020

How to install with WinGet the easy way - The winstall.app way

By using the new WinGet package manager you can use PowerShell to install all the software you need.

But how to use the command's in the correct way?

The site https://winstall.app let's you select all the apps you need and generate an install script for you to install them all at once.

Lets say you want to install, Spotify, Google Chrome, Microsoft Teams, 7Zip and Windows Terminal

Go to the site an search the apps you want and click:

The below command is showed, run it in an elevated PowerShell window and voila all your apps are installed silently and in the version you selected (if applicable)

winget install --id=Spotify.Spotify -e ; winget install --id=Google.Chrome -e ; winget install --id=Microsoft.Teams -e ; winget install --id=7zip.7zip -e ; winget install --id=Microsoft.WindowsTerminal -e