Active Directory – Change User’s Password – Resolution

 

Preface

In a previous post, I spoke of a SMK ( Shaking My Head ) moment I was having.

I couldn’t change a password assigned to a newly created Service Account.

The post is here.

 

 

Problem Identification

Thankfully, I have friends in high places or at least friends who are not so dim.

As Ron was leaving for the day, I said to him you gonna hate me for bothering you.

But, what is with my inability to change my password.

He said it is a Group Policy thing.

I said I checked the Group Policy (GP) and I did not see that.

 

Group Policy Report

Code

Using gpresult we can generate Group Policy Reports.

Generate HTML Output

Script


set "_user=LAB\sbc"
If not exist "d:\temp" md "d:\temp"
gpresult /USER %_user% /F /H d:\temp\grResultUser.html

 

Output

accountandpasswordpolicies

Generate Textual Output

Script


set "_user=LAB\svcSQL"

gpresult /V /USER %_user% | more

Output

rsop-minimumpasswordage

Explanation

Underneath \Policies\Windows Settings \ Account Policies / Password Policy

There  goes a Winning GPO stating “Minimum password age” is 5 days.

 

Conclusion

I still did not get it, and so Ron had to explain it.

A password has to be at least 5 days old, prior to anyone having the ability to change it.

The password was only created yesterday and so I have to a wait a few more days.

 

MSFT’s Recommendation

Cristian Dobre

Link

cristiandobre

 

Confirm Our Last Password Date

Let us confirm our last password date

Code – Credit

As always, I can not write this code.

Stealing this time from Homework

The specific post is titled “How to get the last password change for a user in Active Directory” and it is credited to Alessandro Tani.

It is available here.

Code


Import-Module ActiveDirectory

$ADUser="svcDBHRDB"

$formatDate="yyyy-MM-dd HH:mm"
$now=Get-Date -format $formatDate

"Current Date & TIme is {0}" -f $now

Get-ADuser $ADUser -properties PasswordLastSet | Format-List

 

Output

getaduseroutput-20161201-0838am

Errors

Error – Import-Module : The specified module ‘ActiveDirectory’ was not loaded because no valid module file was found in any module directory.

Please read this QA:

Import-Module : The specified module ‘activedirectory’ was not loaded because no valid module file was found in any module directory
Link

 

References

  1. Security Policy Settings Reference > Account Policies > Password Policy > Minimum password age
    Link
  2. Alessandro Tani
    • How to get the last password change for a user in Active Directory
      Link
  3. Nirmal Sharma
    • When was the Last Password Changed for a User Account in Active Directory
      Link

 

SQL Server Best Practice Analyzer (BPA) – v2012 – Against SQL Server v2014 and Beyond

Background

There are a couple of things one needs to do to get “SQL Server Best Practice Analyzer” (BPA) v2012 to work against MS SQL Server v2014 and above.

In an earlier post we discussed how we determined that we need to have “SQL Server Shared Management Objects” (SMO) installed.

 

Guidelines

  1. Copy SQL2012BPA
    • Copy SQL2012BPA from C:\Windows\System32\BestPractices\v1.0\Models
    • To C:\ProgramData\Microsoft\Microsoft Baseline Configuration Analyzer 2\Models\
    • Special Thanks to Rob “Barkz” Barker
  2. Download the following “MS SQL Server Microsoft® SQL Server® 2012 Feature Pack” components
    • Microsoft® SQL Server® 2012 Shared Management Objects
    • Microsoft® SQL Server® 2012 Analysis Management Objects
  3. Modify the Powershell in this way
    • Microsoft® SQL Server® 2012 Shared Management Objects [ SQL Server SMO ]
      • $SMOAvailable = [System.Reflection.Assembly]::Load(“Microsoft.SqlServer.Smo, Culture=Neutral,Version=11.0.0.0, PublicKeyToken=89845dcd8080cc91″)
        • Original
          • if($SqlServerVersion -ne 11) # Not Equal
        • Revised
          • if($SqlServerVersion -lt 11) # Less Than
      • Microsoft® SQL Server® 2012 Analysis Management Objects [ SQL Analysis AMO ]
        • $AMOAvailable = [System.Reflection.Assembly]::Load(“Microsoft.AnalysisServices, Culture=Neutral,Version=11.0.0.0, PublicKeyToken=89845dcd8080cc91”)
          • Original
            • if($SqlServerVersion -ne 11)
          • Revised
            • if($SqlServerVersion -lt 11)

 

 

Download

Versioning

Which Versions of SQL Server Feature pack do we need?

Took a good look at the code and this is how it reads…

SMO Assembly Reference

Code


$SMOAvailable = [System.Reflection.Assembly]::Load("Microsoft.SqlServer.Smo, Culture=Neutral,Version=11.0.0.0, PublicKeyToken=89845dcd8080cc91")

AMO Assembly Reference – AnalyisServices.ps1

Code


# AMO Assembly Reference
$AMOAvailable = [System.Reflection.Assembly]::Load("Microsoft.AnalysisServices, Culture=Neutral,Version=11.0.0.0, PublicKeyToken=89845dcd8080cc91")


Explanation

The code is specifically looking for Version=11.0.0.0.

SQL Server version 11 is MS SQL Server v2012.

Links

Here are the components of “Microsoft® SQL Server® 2012 Feature Pack” that we need.

Product Version x64 x32
 Microsoft® SQL Server® 2012 Shared Management Objects ( SharedManagementObjects.msi )
Link Link
Microsoft® SQL Server® 2012 Analysis Management Objects ( SQL_AS_AMO.msi )
Link Link

 

 

Please download and install.

 

Scripts

Modified the PowerShell scripts in the following way

Original


if($SqlServerVersion <strong>-ne</strong> 11)
{
	Get-LogText $cError "Engine Rules Execution Interrupted - The instance being scanned exist for SQL Server version which is not supported." >> $EngineLogFile
	AddElementToDocument $XmlDoc $tns $SqlServerVersion "SufficiencyCheck" "SQLServerVersion"
	$XmlDoc
	exit
}

Revised


if($SqlServerVersion <strong>-le</strong> 11)
{
	Get-LogText $cError "Engine Rules Execution Interrupted - The instance being scanned exist for SQL Server version which is not supported." >> $EngineLogFile
	AddElementToDocument $XmlDoc $tns $SqlServerVersion "SufficiencyCheck" "SQLServerVersion"
	$XmlDoc
	exit
}



 

Github

Here is the Github repository for the Scripts.

 

Commendation

Rob “Barkz” Barker

Special commendation to Mr. Rob Barker’s ( Barkz ) work:

GETTING SQL SERVER 2012 BPA WORKING ON WINDOWS SERVER 2012 R2
Link

 

SQL Server / Local Security Policies & PowerShell – Using PowerShell Community Extensions

Introduction

Attempting to use “PowerShell Community Extensions” to augment user’s “Local Security Policies“.

 

What Is?

What is “PowerShell Community Extensions”?

Here
PowerShell Community Extensions (PSCX) is aimed at providing a widely useful set of additional cmdlets, providers, aliases, filters, functions and scripts for Windows PowerShell that members of the community have expressed interest in.

Download

Let us download PowerShell Community Extensions from here.

The current version is 3.2 and it was released way back in 2014-Oct-23.

 

Install

License Agreement

licenseagreement

 

User Account Control

useraccountcontrol

 

Completed

completed

 

Usage

Introspection

Introduction

Let us look at the PSCX Module and see what commands are available for dealing with Privileges.

Code

Set-StrictMode -Version 2

# Import Module
Import-Module PSCX

$filterName = "Privilege"
$filterNameWildcard = "*" + $filterName + "*"


function getPSCXCommandsMatchingPrivilege()
{

	Write-Host "Module pscx // Commands like Privilege"
	Get-Command|where { ( ( $_.modulename -eq "pscx") -and  ($_.name -like $filterNameWildcard) )} |% {$_.name}

	Write-Host 
	Write-Host 

}
	
function getParmsForGetPrivilege()
{

	# get parameters for Get-Privilege
	$privGet = gcm Get-Privilege
	$parmListGet = $privGet.ParameterSets[0] | select -ExpandProperty parameters

	# display Parm List for Get-Privilege
	Write-Host "Parm List for Get-Privilege"
	$parmListGet

	Write-Host 
	Write-Host 
}

function getParmsForSetPrivilege()
{

	# get parameters for Set-Privilege
	$privSet = gcm Set-Privilege
	$parmListSet = $privSet.ParameterSets[0] | select -ExpandProperty parameters

	# display Parm List for Set-Privilege
	Write-Host "Parm List for Set-Privilege"
	$parmListSet

	Write-Host 
	Write-Host 

}


getPSCXCommandsMatchingPrivilege

getParmsForGetPrivilege

getParmsForSetPrivilege

Local Security Privileges Get / Set

Introduction

Let us see how to read/set privileges

Code




param
(
     [Parameter(Mandatory=$true)]
     [String]$account
	 
     ,[Parameter(Mandatory=$true)]
     [String]$method      
)
     
Set-StrictMode -Version 2

[String]$log="";
[String]$privilegeSetOutput="";

[String]$CHAR_LINEBREAK = "===================================================================================="  

$log = "$env:PSModulePath is {0}" -f $env:PSModulePath
Write-Host $Log

<# Method has to be get or set #>
if ($method -eq $null)
{
	Write-Error "Method is required.  Please specify as get or set"
	exit -1
}


if ( ($method -ne "get") -and ($method -ne "set") )
{
	Write-Error "Method is required.  Please specify as get or set"
	exit -1
}

$sidstr = $null
try {
	$ntprincipal = new-object System.Security.Principal.NTAccount "$account"
	$sid = $ntprincipal.Translate([System.Security.Principal.SecurityIdentifier])
	$sidstr = $sid.Value.ToString()
} catch {
	$sidstr = $null
}

Write-Host "Account: $($account)" -ForegroundColor DarkCyan

if( [string]::IsNullOrEmpty($sidstr) ) {
	
	#Write-Host "Account not found!" -ForegroundColor Red
	
	$errLog = "Account ({0}) Not Found "-f $account
	Write-Host $errLog -ForegroundColor Red
	
	exit -1	
	
}

Write-Host "Account SID: $($sidstr)" -ForegroundColor DarkCyan

# Get Windows dentify from account
$userTargeted = new-object system.security.principal.windowsidentity($account)

if ($userTargeted -eq $null)
{

	$errLog = "Account ({0}) Not Found "-f $account
	
	Write-Host $errLog -ForegroundColor Red
	
	exit -2
	
}

# Import Module
Import-Module PSCX

if ($method -eq "get")
{


	Write-Host ""
	$log = "Write out privileges for {0} {1} - Before" -f $account, $userTargeted.Name

	Write-Host $log

	Get-Privilege -Identity $userTargeted
	
	Write-Host ""
	Write-Host ""

}
elseif ($method -eq "set")
{

	Write-Host ""
	$log = "Write out privileges for {0} {1} - Before" -f $account, $userTargeted.Name
	Write-Host $log
	Get-Privilege -Identity $userTargeted
	
	
	Write-Host $CHAR_LINEBREAK
	

	# Instanciate Pscx.Interop.TokenPrivilegeCollection
	$privilegeSet = new-object  Pscx.Interop.TokenPrivilegeCollection

	# Add Pscx.Interop.TokenPrivilegeCollection - SeManageVolumePrivilege
	$priv = 'SeManageVolumePrivilege'
	$objPriv = New-object Pscx.Interop.TokenPrivilege($priv, $true) 
	$privilegeSet.Add($objPriv)
	
		
	$log = "Add privileges ...." 
	Write-Host $log

	
	$log = "`tAdd privilege for {0}" -f $priv
	Write-Host $log

	# Add Pscx.Interop.TokenPrivilegeCollection - SeLockMemoryPrivilege
	$priv = 'SeLockMemoryPrivilege'
	$objPriv = New-object Pscx.Interop.TokenPrivilege($priv, $true) 
	$privilegeSet.Add($objPriv)

	$log = "`tAdd privilege for {0}" -f $priv
	Write-Host $log

	#http://windowsitpro.com/powershell/save-output-powershell-pipeline-variable
	Set-Privilege -Identity $userTargeted -Privileges $privilegeSet  -outvariable privilegeSetOutput

	Write-Host $CHAR_LINEBREAK
	
	Write-Host ""
	$log = "Write out privileges -outvariable [{0}] " -f $privilegeSetOutput
	Write-Host $log


	$log = "Write out privileges for {0} {1} - After" -f $account, $userTargeted.Name
	Write-Host $log

	Write-Host ""
	Write-Host ""

	Get-Privilege -Identity $userTargeted

}
Write-Host ""

Invocation

Read Privileges

Code

set "_account=daniel.adeniji"
set "_method=get"

powershell ./LocalSecurityPolicies_PSCX.ps1 -account %_account%   -method %_method%

Output

usage-get-20161105-0918am

 

 

Set Privileges

Code

set "_account=daniel.adeniji"
set "_method=set"

powershell ./LocalSecurityPolicies_PSCX.ps1 -account %_account%   -method %_method%

Output

setprivileges-20161105-0921am

Explanation
  1. Tried adding the following Local Security Policies
    • SeManageVolumePrivilege
    • SeLockMemoryPrivilege
  2. But, even though error not reported, privileges are not added

Source Control

GitHub

Here is the code’s link

 

Closing

Please help!….

 

SQL Server / Local Security Policies & PowerShell – Using pInvoke.Net

Background

Went down that long road yesterday.  My objective was simple, but the task was arduous.

Basically, wanted to use PowerShell to grant Local Security Policies to our SQL Server Service Account.

Baseline Code

Here we attempt to use the following codebases:

  1. PInvoke.Net (Community Service By Redgate)
  2. Roel van Lisdonk
    • How to grant “Log on as a service” rights to an user account, using PowerShell
      Link
  3. Jordan Mills
    • Change local user rights assignment from powershell
      Link

 

Purpose

 

 

Vendor/Person Date Context Link
 pInvoke.net  The LsaEnumerateAccountsWithUserRight function returns the accounts in the database of a Local Security Authority (LSA) Policy object that hold a specified privilege.  Link
Roel van Lisdonk 2010-Mar-24  

If you want to grant “Log on as a service” rights to a user account, using PowerShell you can use the secedit.exe tool, using a *.inf security template file.

An other solution, is to P/Invoke the LSA functions in the advapi32.dl with C#.
Willy created a LSA wrapper for C#: http://www.developmentnow.com/g/36_2005_3_0_0_223925/LSA-functions.htm
I used this wrapper class to grant “Log on as a service” rights to an user account, using PowerShell
After building the assembly containing the LSA wrapper class, you can grant access with PowerShell

Link.
 Jordan’s Space 2014-07-31 There are lots of “solutions” out there that just shell out to ntrights.exe or secedit or something else not powershell, and say “but powershell calls it so it counts!” No it doesn’t.

There is no native NET or COM interface to manage local user rights assignment. You have to use P/Invoke to call the API. It’s a pain. But I swiped some code from Roel van Lisdonk (http://www.roelvanlisdonk.nl/?p=1151) and got it working. Looks like he got the code from Willy Denoyette (see http://www.pinvoke.net/default.aspx/advapi32.lsalookupsids) which I kept intact except for a minor error correction (it was missing a semicolon and wouldn’t compile). See http://www.hightechtalks.com/csharp/lsa-functions-276626.html for what seems to be Willy’s original source.

Link.

 

 

 

Code

Here is our current version

Files List

 

Vendor/Person Purpose Details
LsaSecurity.cs  C# Code Grunt of the work
LsaSecurity.ps1 PowerShell Code Permissions – SeManageVolumePrivilege & SeLockMemoryPrivilege
invokePSScript.cmd Sample Invocation file Review as one needs to specify Service Account [domain-name\samaccountname]

 

 

 

 

LsaSecurity.cs




using System;
using System.Collections.Generic;
using System.Text;
using System.Diagnostics;


namespace LsaSecurity
 {
     /*
	 
	   1) LsaWrapper class credit: Willy Denoyette [MVP]
       http://www.hightechtalks.com/csharp/lsa-functions-276626.html

		a) Added support for:
           LsaLookupSids
	 
	 
	   2)  Roel van Lisdonk
    	   How to grant “Log on as a service” rights to an user account, using PowerShell
		   
How to grant “Log on as a service” rights to an user account, using PowerShell
3) Jordan Mills https://jordanmills.wordpress.com/2014/07/31/change-local-user-rights-assignment-from-powershell/ a) Added LsaWrapperCaller classes */ using System.Runtime.InteropServices; using System.Security; using System.Management; using System.Runtime.CompilerServices; using System.ComponentModel; using LSA_HANDLE = IntPtr; public class Program { public static void Main() { using (LsaWrapper lsaSec = new LsaWrapper()) { string rights = null; rights = "SeManageVolumePrivilege"; Console.WriteLine("Calling GetUsersWithPrivilege for rights " + rights); string[] accounts = lsaSec.GetUsersWithPrivilege(rights); if (accounts == null) { return; } int iNumberofAccounts = accounts.Length; int i; for (i=0; i<iNumberofAccounts; i++) { Console.WriteLine(accounts[i]); } } } } [StructLayout(LayoutKind.Sequential)] struct LSA_OBJECT_ATTRIBUTES { internal int Length; internal IntPtr RootDirectory; internal IntPtr ObjectName; internal int Attributes; internal IntPtr SecurityDescriptor; internal IntPtr SecurityQualityOfService; } [StructLayout(LayoutKind.Sequential, CharSet = CharSet.Unicode)] struct LSA_UNICODE_STRING { internal ushort Length; internal ushort MaximumLength; [MarshalAs(UnmanagedType.LPWStr)] internal string Buffer; } sealed class Win32Sec { [DllImport("advapi32", CharSet = CharSet.Unicode, SetLastError = true), SuppressUnmanagedCodeSecurityAttribute] internal static extern uint LsaOpenPolicy( LSA_UNICODE_STRING[] SystemName, ref LSA_OBJECT_ATTRIBUTES ObjectAttributes, int AccessMask, out IntPtr PolicyHandle ); [DllImport("advapi32", CharSet = CharSet.Unicode, SetLastError = true), SuppressUnmanagedCodeSecurityAttribute] internal static extern uint LsaAddAccountRights( LSA_HANDLE PolicyHandle, IntPtr pSID, LSA_UNICODE_STRING[] UserRights, int CountOfRights ); [DllImport("advapi32", CharSet = CharSet.Unicode, SetLastError = true), SuppressUnmanagedCodeSecurityAttribute] internal static extern uint LsaRemoveAccountRights( LSA_HANDLE PolicyHandle, IntPtr pSID, bool allRights, LSA_UNICODE_STRING[] UserRights, int CountOfRights ); [DllImport("advapi32", CharSet = CharSet.Unicode, SetLastError = true), SuppressUnmanagedCodeSecurityAttribute] internal static extern uint LsaEnumerateAccountsWithUserRight( LSA_HANDLE PolicyHandle, LSA_UNICODE_STRING[] UserRights, out IntPtr EnumerationBuffer, out int CountReturned ); [DllImport("advapi32", CharSet = CharSet.Unicode, SetLastError = true), SuppressUnmanagedCodeSecurityAttribute] internal static extern uint LsaLookupSids( LSA_HANDLE PolicyHandle, int count, IntPtr buffer, out LSA_HANDLE domainList, out LSA_HANDLE nameList ); [DllImport("advapi32", CharSet = CharSet.Unicode, SetLastError = true), SuppressUnmanagedCodeSecurityAttribute] internal static extern int LsaLookupNames2( LSA_HANDLE PolicyHandle, uint Flags, uint Count, LSA_UNICODE_STRING[] Names, ref IntPtr ReferencedDomains, ref IntPtr Sids ); [DllImport("advapi32")] internal static extern int LsaNtStatusToWinError(int NTSTATUS); [DllImport("advapi32")] internal static extern int LsaClose(IntPtr PolicyHandle); [DllImport("advapi32")] internal static extern int LsaFreeMemory(IntPtr Buffer); } public sealed class LsaWrapper : IDisposable { private bool _writeToConsole = false; [StructLayout(LayoutKind.Sequential)] struct LSA_TRUST_INFORMATION { internal LSA_UNICODE_STRING Name; internal IntPtr Sid; } [StructLayout(LayoutKind.Sequential)] struct LSA_TRANSLATED_SID2 { internal SidNameUse Use; internal IntPtr Sid; internal int DomainIndex; uint Flags; } //[StructLayout(LayoutKind.Sequential)] //struct LSA_REFERENCED_DOMAIN_LIST //{ // internal uint Entries; // internal LSA_TRUST_INFORMATION Domains; //} // Commented by KaushalendraATgmail.com [StructLayout(LayoutKind.Sequential)] internal struct LSA_REFERENCED_DOMAIN_LIST { internal uint Entries; internal IntPtr Domains; } [StructLayout(LayoutKind.Sequential)] struct LSA_ENUMERATION_INFORMATION { internal LSA_HANDLE PSid; } [StructLayout(LayoutKind.Sequential)] struct LSA_SID { internal uint Sid; } [StructLayout(LayoutKind.Sequential)] struct LSA_TRANSLATED_NAME { internal SidNameUse Use; internal LSA_UNICODE_STRING Name; internal int DomainIndex; } enum SidNameUse : int { User = 1, Group = 2, Domain = 3, Alias = 4, KnownGroup = 5, DeletedAccount = 6, Invalid = 7, Unknown = 8, Computer = 9 } enum Access : int { POLICY_READ = 0x20006, POLICY_ALL_ACCESS = 0x00F0FFF, POLICY_EXECUTE = 0X20801, POLICY_WRITE = 0X207F8 } const uint STATUS_ACCESS_DENIED = 0xc0000022; const uint STATUS_INSUFFICIENT_RESOURCES = 0xc000009a; const uint STATUS_NO_MEMORY = 0xc0000017; IntPtr lsaHandle; public LsaWrapper() : this(null) { } // // local system if systemName is null public LsaWrapper(string systemName) { LSA_OBJECT_ATTRIBUTES lsaAttr; lsaAttr.RootDirectory = IntPtr.Zero; lsaAttr.ObjectName = IntPtr.Zero; lsaAttr.Attributes = 0; lsaAttr.SecurityDescriptor = IntPtr.Zero; lsaAttr.SecurityQualityOfService = IntPtr.Zero; lsaAttr.Length = Marshal.SizeOf(typeof(LSA_OBJECT_ATTRIBUTES)); lsaHandle = IntPtr.Zero; LSA_UNICODE_STRING[] system = null; if (systemName != null) { system = new LSA_UNICODE_STRING[1]; system[0] = InitLsaString(systemName); } uint ret = Win32Sec.LsaOpenPolicy(system, ref lsaAttr, (int)Access.POLICY_ALL_ACCESS, out lsaHandle); if (ret == 0) { return; } if (ret == STATUS_ACCESS_DENIED) { throw new UnauthorizedAccessException(); } if ((ret == STATUS_INSUFFICIENT_RESOURCES) || (ret == STATUS_NO_MEMORY)) { throw new OutOfMemoryException(); } throw new Win32Exception(Win32Sec.LsaNtStatusToWinError((int)ret)); } /* 2016-11-03 DAdeniji - Added Dispose */ public void Dispose() { if (lsaHandle != IntPtr.Zero) { Win32Sec.LsaClose(lsaHandle); lsaHandle = IntPtr.Zero; } GC.SuppressFinalize(this); } ~LsaWrapper() { Dispose(); } // helper functions IntPtr GetSIDInformation(string account) { LSA_UNICODE_STRING[] names = new LSA_UNICODE_STRING[1]; LSA_TRANSLATED_SID2 lts; IntPtr tsids = IntPtr.Zero; IntPtr tdom = IntPtr.Zero; names[0] = InitLsaString(account); lts.Sid = IntPtr.Zero; int ret = Win32Sec.LsaLookupNames2(lsaHandle, 0, 1, names, ref tdom, ref tsids); if (ret != 0) throw new Win32Exception(Win32Sec.LsaNtStatusToWinError(ret)); lts = (LSA_TRANSLATED_SID2)Marshal.PtrToStructure(tsids, typeof(LSA_TRANSLATED_SID2)); Win32Sec.LsaFreeMemory(tsids); Win32Sec.LsaFreeMemory(tdom); return lts.Sid; } static LSA_UNICODE_STRING InitLsaString(string s) { // Unicode strings max. 32KB if (s.Length > 0x7ffe) { throw new ArgumentException("String too long"); } LSA_UNICODE_STRING lus = new LSA_UNICODE_STRING(); lus.Buffer = s; lus.Length = (ushort)(s.Length * sizeof(char)); lus.MaximumLength = (ushort)(lus.Length + sizeof(char)); return lus; } public string[] GetUsersWithPrivilege(string privilege) { //LSA_UNICODE_STRING[] privileges = new LSA_UNICODE_STRING[1]; LSA_UNICODE_STRING[] privileges = null; //privileges[0] = InitLsaString(privilege); IntPtr buffer; int count; //long count; uint ret = 0; string[] domainUserName = null; List<string> objListofSteps = new List<string>(); LSA_HANDLE domains; LSA_HANDLE names; //string[] retNames = new string[count]; string[] retNames = null; //List<int> currentDomain = new List<int>(); List<int> currentDomain; int domainCount = 0; //LSA_TRANSLATED_NAME[] lsaNames = new LSA_TRANSLATED_NAME[count]; LSA_TRANSLATED_NAME[] lsaNames; try { //LSA_UNICODE_STRING[] privileges = new LSA_UNICODE_STRING[1]; privileges = new LSA_UNICODE_STRING[1]; objListofSteps.Add("Invoking InitLsaString(privilege)"); privileges[0] = InitLsaString(privilege); objListofSteps.Add("Win32Sec.LsaEnumerateAccountsWithUserRight Initiating ...."); ret = Win32Sec.LsaEnumerateAccountsWithUserRight ( lsaHandle , privileges , out buffer , out count ); objListofSteps.Add("Win32Sec.LsaEnumerateAccountsWithUserRight - Completed"); objListofSteps.Add("Win32Sec.LsaEnumerateAccountsWithUserRight - return code is " + ret); objListofSteps.Add("Win32Sec.LsaEnumerateAccountsWithUserRight - Number of Accounts returned is " + count); if (ret != 0) { if (ret == STATUS_ACCESS_DENIED) { throw new UnauthorizedAccessException(); } if (ret == STATUS_INSUFFICIENT_RESOURCES || ret == STATUS_NO_MEMORY) { throw new OutOfMemoryException(); } throw new Win32Exception(Win32Sec.LsaNtStatusToWinError((int)ret)); } objListofSteps.Add(" Initializing LSA_ENUMERATION_INFORMATION for " + count + " nodes " + " ..."); objListofSteps.Add(" .... LSA_ENUMERATION_INFORMATION Buffer Size is " + buffer); LSA_ENUMERATION_INFORMATION[] lsaInfo = new LSA_ENUMERATION_INFORMATION[count]; //dadeniji 2016-11-03 11:56 AM //int elemOffs = 0; long elemOffs = 0; for (int i = 0; i < count; i++) { objListofSteps.Add("(LSA_ENUMERATION_INFORMATION)Marshal.PtrToStructure - (int)buffer for " + i + " " + buffer); //elemOffs = (int)buffer; elemOffs = (long)buffer; objListofSteps.Add("(LSA_ENUMERATION_INFORMATION)Marshal.PtrToStructure for " + i + " buffer is " + buffer); lsaInfo[i] = (LSA_ENUMERATION_INFORMATION)Marshal.PtrToStructure ( (IntPtr)elemOffs //(LongPtr)elemOffs , typeof(LSA_ENUMERATION_INFORMATION) ); elemOffs += Marshal.SizeOf(typeof(LSA_ENUMERATION_INFORMATION)); } objListofSteps.Add(" Completed LSA_ENUMERATION_INFORMATION for " + count + " nodes " + " ..."); objListofSteps.Add("Win32Sec.LsaLookupSids - Invoking "); ret = Win32Sec.LsaLookupSids ( lsaHandle , lsaInfo.Length , buffer , out domains , out names ); if (ret != 0) { if (ret == STATUS_ACCESS_DENIED) { throw new UnauthorizedAccessException(); } if (ret == STATUS_INSUFFICIENT_RESOURCES || ret == STATUS_NO_MEMORY) { throw new OutOfMemoryException(); } throw new Win32Exception(Win32Sec.LsaNtStatusToWinError((int)ret)); } objListofSteps.Add("Win32Sec.LsaLookupSids - Completed "); retNames = new string[count]; currentDomain = new List<int>(); domainCount = 0; lsaNames = new LSA_TRANSLATED_NAME[count]; //elemOffs = (int)names; elemOffs = (long)names; //for (int i = 0, elemOffs = (int)names; i < count; i++) for (int i = 0; i < count; i++) { lsaNames[i] = (LSA_TRANSLATED_NAME)Marshal.PtrToStructure((LSA_HANDLE)elemOffs, typeof(LSA_TRANSLATED_NAME)); elemOffs += Marshal.SizeOf(typeof(LSA_TRANSLATED_NAME)); LSA_UNICODE_STRING name = lsaNames[i].Name; retNames[i] = name.Buffer.Substring(0, name.Length / 2); objListofSteps.Add(retNames[i]); if (!currentDomain.Contains(lsaNames[i].DomainIndex)) { domainCount = domainCount + 1; currentDomain.Add(lsaNames[i].DomainIndex); } //Error: not necessary to count domain names } string[] domainPtrNames = new string[count]; LSA_REFERENCED_DOMAIN_LIST[] lsaDomainNames = new LSA_REFERENCED_DOMAIN_LIST[count]; //Error: LSA_REFERENCED_DOMAIN_LIST is a structure, not an array //for (int i = 0, elemOffs = (int)domains; i < count; i++) //elemOffs = (int)domains; elemOffs = (long)domains; for (int i = 0; i < count; i++) //Error: not necessary { lsaDomainNames[i] = (LSA_REFERENCED_DOMAIN_LIST)Marshal.PtrToStructure ( (LSA_HANDLE)elemOffs , typeof(LSA_REFERENCED_DOMAIN_LIST) ); elemOffs += Marshal.SizeOf(typeof(LSA_REFERENCED_DOMAIN_LIST)); } LSA_TRUST_INFORMATION[] lsaDomainName = new LSA_TRUST_INFORMATION[count]; string[] domainNames = new string[domainCount]; //for (int i = 0, elemOffs = (int)lsaDomainNames[i].Domains; i < domainCount; i++) //elemOffs = (int)lsaDomainNames[0].Domains; elemOffs = (long)lsaDomainNames[0].Domains; for (int i = 0; i < domainCount; i++) { lsaDomainName[i] = (LSA_TRUST_INFORMATION)Marshal.PtrToStructure ( (LSA_HANDLE)elemOffs , typeof(LSA_TRUST_INFORMATION) ); elemOffs += Marshal.SizeOf(typeof(LSA_TRUST_INFORMATION)); LSA_UNICODE_STRING tempDomain = lsaDomainName[i].Name; //if(tempDomain.Buffer != null) //{ domainNames[i] = tempDomain.Buffer.Substring(0, tempDomain.Length / 2); //} } //string[] domainUserName = new string[count]; domainUserName = new string[count]; for (int i = 0; i < lsaNames.Length; i++) { domainUserName[i] = domainNames[lsaNames[i].DomainIndex] + "\\" + retNames[i]; } objListofSteps.Add("Win32Sec.LsaFreeMemory - Initiating"); Win32Sec.LsaFreeMemory(buffer); Win32Sec.LsaFreeMemory(domains); Win32Sec.LsaFreeMemory(names); objListofSteps.Add("Win32Sec.LsaFreeMemory - Completed"); } catch(Exception ex) { //Get a StackTrace object for the exception StackTrace st = new StackTrace(ex, true); //Get the first stack frame StackFrame frame = st.GetFrame(0); //Get the file name string fileName = frame.GetFileName(); //Get the method name string methodName = frame.GetMethod().Name; //Get the line number from the stack frame int line = frame.GetFileLineNumber(); //Get the column number int col = frame.GetFileColumnNumber(); String strErr = null; strErr = ex.Message + " - filename " + fileName + " - methodName " + methodName + " - Line Number " + line + " - Column " + col ; domainUserName = new string[1]; domainUserName[0] = strErr; objListofSteps.Add(strErr); domainUserName = objListofSteps.ToArray(); } return (domainUserName); } public void AddPrivileges(string account, string privilege) { IntPtr pSid = GetSIDInformation(account); LSA_UNICODE_STRING[] privileges = new LSA_UNICODE_STRING[1]; privileges[0] = InitLsaString(privilege); uint ret = Win32Sec.LsaAddAccountRights(lsaHandle, pSid, privileges, 1); if (ret == 0) { if (this._writeToConsole) { Console.WriteLine("Added: {0} to {1} successfully.", account, privilege); } return; } if (ret == STATUS_ACCESS_DENIED) { throw new UnauthorizedAccessException(); } if ((ret == STATUS_INSUFFICIENT_RESOURCES) || (ret == STATUS_NO_MEMORY)) { throw new OutOfMemoryException(); } throw new Win32Exception(Win32Sec.LsaNtStatusToWinError((int)ret)); } public void RemovePrivileges(string account, string privilege) { IntPtr pSid = GetSIDInformation(account); LSA_UNICODE_STRING[] privileges = new LSA_UNICODE_STRING[1]; privileges[0] = InitLsaString(privilege); uint ret = Win32Sec.LsaRemoveAccountRights(lsaHandle, pSid, false, privileges, 1); if (ret == 0) { if (this._writeToConsole) { Console.WriteLine("Removed: {0} from {1} successfully.", account, privilege); } return; } if (ret == STATUS_ACCESS_DENIED) { throw new UnauthorizedAccessException(); } if ((ret == STATUS_INSUFFICIENT_RESOURCES) || (ret == STATUS_NO_MEMORY)) { throw new OutOfMemoryException(); } throw new Win32Exception(Win32Sec.LsaNtStatusToWinError((int)ret)); } public bool WriteToConsole { set { this._writeToConsole = value; } } } public class LsaWrapperCaller { //static String computerName = null; public static string computer { get; set; } public static void AddPrivileges(string account, string privilege) { using (LsaWrapper lsaWrapper = new LsaWrapper(computer)) { lsaWrapper.AddPrivileges(account, privilege); } } public static void RemovePrivileges(string account, string privilege) { using (LsaWrapper lsaWrapper = new LsaWrapper(computer)) { lsaWrapper.RemovePrivileges(account, privilege); } } public static string[] GetUsersWithPrivilege(string privilege) { string[] users = null; using (LsaWrapper lsaWrapper = new LsaWrapper(computer)) { users = lsaWrapper.GetUsersWithPrivilege(privilege); } return (users); } } }

 

LsaSecurity.cs


param
(
     [Parameter(Mandatory=$true)]
     [String]$account
      
)
     
Set-StrictMode -Version 2
<# addCSharpObjectTemplate Read C# file and make available to Powershell #>
function addCSharpObjectTemplate($file)
{
     
    $fileFullPath = resolve-path $file;
     
    $Source = [System.IO.File]::ReadAllText($file);
      
    $strLog = "Importing Files :- fileFullPath: {0}"  -f $fileFullPath
     
    Write-Host $strLog
     
    Add-Type -TypeDefinition $Source -Language CSharpVersion3;
 
}
 
 
<# addCSharpObject Invokes addCSharpObjectTemplate for the listed files #>
function addCSharpObject
{
	
	$file = "LsaSecurity.cs";
    addCSharpObjectTemplate($file);

 
}

#Add C# Objects
addCSharpObject

<# CHAR #>
$CHAR_TAB = "`t";
$CHAR_YES = "Y";
$CHAR_NO = "N";


$listofRights = @(
					  'SeManageVolumePrivilege'
					, 'SeLockMemoryPrivilege'
				)

$rights = "SeManageVolumePrivilege"


[LsaSecurity.LsaWrapperCaller]::computer = $null;

<# [LsaSecurity.LsaWrapperCaller]::GetUsersWithPrivilege($rights) [LsaSecurity.LsaWrapperCaller]::AddPrivileges($account, $rights) [LsaSecurity.LsaWrapperCaller]::RemovePrivileges($account, $rights) #>

$log = "Adding Local Security Policy Privilges for {0} ..." -f $account
Write-Host $log

# For each right, grant to account
foreach ($rights in $listofRights) {

    $log = "`tWorking on {0} ..." -f $rights
	
	Write-Host $log
	
	[LsaSecurity.LsaWrapperCaller]::AddPrivileges($account, $rights)
}


Sample Invocation

Local Computer – Service Account

Documentation

Here we grant permission to the host computer Service Account.

In Windows parlance, the NT Service Account.

It is designated via Domain Name\ComputerName$.

Code


set "_ADDomain=%USERDOMAIN%"
set "_host=%COMPUTERNAME%"
set "_ADAccount=%_ADDomain%\%_host%$"
echo _ADAccount is %_ADAccount%

powershell ./LsaSecurity.ps1 -account %_ADAccount%

Invoker

Documentation

And, here we grant permission to the person running the script.

It is designated via Domain Name\Username.

We determine the Domain and Username by querying the environment variables USERDOMAIN & USERNAME.

Code


set "_ADDomain=%USERDOMAIN%"
set "_ADUsername=%USERNAME%"
set "_ADAccount=%_ADDomain%\%_ADUsername%"
echo _ADAccount is %_ADAccount%

rem powershell ./LsaSecurity.ps1 -account %_ADAccount%

Source Code Control

GitHub

Code is available in GitHub.

Specifically Here

 

Summary

Later will come back and update post as to why we made small changes.

 

References

  1. Local Security Policies
  2. SQL Server Service Accounts
    • Database Engine Features and Tasks \ Database Engine Instances (SQL Server) \ Manage the Database Engine Services \ Configure Windows Service Accounts and Permissions
      Link
    • SQL Server service account Windows privileges and rights
      Link

 

Windows – What is in your System Drive – C:\WINDOWS\SoftwareDistribution\Download Folder?

Background

Trying to see what is taking up space on an OS Drive.

Tools to Use

OS Drive View

SpaceSniffer

BTW, SpaceSniffer is available here.

Here is what SpaceSniffer came up with.

spacesniffer-brushedup

 

Which Applications is using our big folders?

One of the folders that is big is C:\WINDOWS\SoftwareDistribution\Download

As the C:\WINDOWS\SoftwareDistribution\Download folder is in use by many applications, let us see which Application is using the most storage.

 

Explorer View

In Explorer, select the Title Bar, and add the following columns:

  1. Company
  2. Description

 

Choose Details

choosedetails

 

Files

Here are the files ordered by Size in Descending Order:

mswindowsexplorer

Explanation:

  1. 1258935150ae2e1692ddee617d6fd408259eff42
    • 120 MB
    • Microsoft Antimalware Signature Package Redirector
  2. From Aug-11th to Aug-18
    • 19 MB
    • AntiMalware Definition Update

 

AntiMalware Definition Update

multiplefiles19mbeach

 

PowerShell

Script


<# .PARAMETER folder The name of the folder .PARAMETER maxFiles The maximum # of files #>
param
(
	 [Parameter(Mandatory=$true)]
	 [string]$folder="C:\WINDOWS\SoftwareDistribution\Download"
	
	,[Parameter(Mandatory=$false)]
	 [int]$maxFiles=15
	 
)
	
Set-StrictMode -Version 2

##
# start of script
##
#[System.Type]$Global:ConvertSize = $null;
#[String]$Global:ConvertSize = $null;

Function Convert-Size {
    <# .SYSNOPSIS Converts a size in bytes to its upper most value. .DESCRIPTION Converts a size in bytes to its upper most value. .PARAMETER Size The size in bytes to convert .NOTES Author: Boe Prox Date Created: 22AUG2012 .EXAMPLE Convert-Size -Size 568956 555 KB Description ----------- Converts the byte value 568956 to upper most value of 555 KB .EXAMPLE Get-ChildItem | ? {! $_.PSIsContainer} | Select -First 5 | Select Name, @{L='Size';E={$_ | Convert-Size}} Name Size ---- ---- Data1.cap 14.4 MB Data2.cap 12.5 MB Image.iso 5.72 GB Index.txt 23.9 KB SomeSite.lnk 1.52 KB SomeFile.ini 152 bytes Description ----------- Used with Get-ChildItem and custom formatting with Select-Object to list the uppermost size. #>
    [cmdletbinding()]
    Param (
        [parameter(ValueFromPipeline=$True,ValueFromPipelineByPropertyName=$True)]
        [Alias("Length")]
        [int64]$Size
    )
    Begin 
	{
		if ($Global:ConvertSizeDefined -eq $false) 
		{
		
            Write-Verbose ("Creating signature from Win32API")
			
            $Signature =  @"
                 [DllImport("Shlwapi.dll", CharSet = CharSet.Auto)]
                 public static extern long StrFormatByteSize( long fileSize, System.Text.StringBuilder buffer, int bufferSize );
"@
			 
            $Global:ConvertSize = Add-Type -Name SizeConverter -MemberDefinition $Signature -PassThru
			
        }
		
        Write-Verbose ("Building buffer for string")
		
        $stringBuilder = New-Object Text.StringBuilder 1024
		
    }
	
    Process 
	{
        Write-Verbose ("Converting {0} to upper most size" -f $Size)
        $ConvertSize::StrFormatByteSize( $Size, $stringBuilder, $stringBuilder.Capacity ) | Out-Null
        $stringBuilder.ToString()
    }
	
}

if(-not($folder)) 
{ 
	Throw “You must supply a value for -folder” 
}

[bool]$Global:ConvertSizeDefined = $false;
$MaxFilesDefaut = 15

if(-not($maxFiles)) 
{
	$maxFiles = $MaxFilesDefaut
}


#Get Files in Folders Ordered By Size
$files = Get-ChildItem $folder | Sort-Object length  -Descending

#Get # of Folders & Files
$iFileCount = $files.Count

$iFileID = 0;
$iFilesProcessed=0

#Iterate Folders and Files
for ($iFileID=0; $iFileID -lt $iFileCount; $iFileID++) `
{

	$objFile = $files[$iFileID]
	$filename = $objFile.Name.Trim()	
	$filenameFull = $objFile.FullName
	$strFileSize= "";

	# Get File Item
	$objFileItem = Get-Item $filenameFull
	
	# Get File Type
	$objFileType = $objFileItem.GetType()
	
	# Make sure is File and not a Folder
	if ($objFileItem -is [system.io.fileinfo])
	{

		# Increment File Processed Counter
		$iFilesProcessed = $iFilesProcessed + 1;
		
		#Get File Size
		$iFileSize = $objFileItem.length
		
		#Covert File Size to Human Readable Fashion
		$strFileSize = Convert-Size($iFileSize)
		
		
		#Get File Info
		$info= [system.Diagnostics.FileVersionInfo]::GetVersionInfo($filenameFull)
		
		# If File Info is good
		if ($info)
		{
			"File ID			 : {0}" -f $iFilesProcessed 
			"File CompanyName 	 : {0}" -f  $info.CompanyName
			"File Name	         : {0,-40}" -f $filename.Trim()
			"File Description 	 : {0}" -f  $info.filedescription
			"File Size     		 : {0}" -f  $strFileSize.Trim()
			Write-Host ""
		}

		# Break if Max Files 
		if ($iFilesProcessed -ge $maxFiles) 
		{ 
			break; 
		}
	
	} # if ($objFileItem is [system.io.fileinfo])
	

	
}	
	 
	 

 

Sample Invocation


powershell ./Get-FilesInfo.ps1  -folder C:\WINDOWS\SoftwareDistribution\Download -maxFiles 15

Output

getfilesinfo

 

 

Source Code Version Control

Git Hub

Files

 

SQL Server – Get Version Numbers Using PowerShell

Background

I have a list of SQL Server Instances that we manage.

Wanted to get Version Numbers for the sake of ensuring that we have the last Service Pack for each SQL Server Version.

Here is the set of scripts that we have thus far.

Code

Source Code Type File Use
 Data files
datafile\SQLServerInstances.txt List of SQL Server Instances; each SQL Server Instance in a line by itself
 Microsoft C#
 _SQLInstanceNode.cs Supporting – Object ( _SQLInstanceNode )
 _SQLServicePackLatest.cs Supporting – Object ( _SQLServicePackLatest & _SQLServicePackLatestList )
 Microsoft PowerShell
 SQLInstanceVersion.ps1  Main App

 

 

Code – C#- _SQLInstanceNode.cs



using System;
using System.Collections.Generic;
using System.Collections;

public class _SQLInstanceNode
{
	
	public _SQLInstanceNode()
	{
		errorMessage = "";
		failedItem = "";
		patchedATLatest = ' ';
	}		
	
	public bool	  active { get; set; }
	public string sqlInstance { get; set; }
	public string edition { get; set; }
	public string productLevel { get; set; }
	public string productVersion { get; set; }
	public string marketing { get; set; }
	
	public string servicePackLatest { get; set; }
	public char   patchedATLatest { get; set; }
	
	public string errorMessage { get; set; }
	public string failedItem { get; set; }

}

Code – C#- _SQLServicePackLatest.cs





using System;
using System.Collections.Generic;
using System.Collections;

public class _SQLServicePackLatest
{
	
	public string marketing   { get; set; }
	public string servicePack { get; set; }
	public int    servicePackAsInt { get; set; }

}


public class _SQLServicePackLatestList
{
	
	private ArrayList _objList = new ArrayList();
	private _SQLServicePackLatest objSPK;
	
	public ArrayList list
    {
        get { return _objList; }
    }
	
	public int count
    {
        get { return _objList.Count; }
    }
	
		
	public _SQLServicePackLatestList()
	{
		
		//2016
		objSPK = new _SQLServicePackLatest();
		objSPK.marketing = "2016";
		objSPK.servicePack = "RTM";
		objSPK.servicePackAsInt = 0;
		_objList.Add(objSPK);
		
		//2014
		objSPK = new _SQLServicePackLatest();
		objSPK.marketing = "2014";
		objSPK.servicePack = "SP2";
		objSPK.servicePackAsInt = 2;
		_objList.Add(objSPK);
		
		
		//2012
		objSPK = new _SQLServicePackLatest();
		objSPK.marketing = "2012";
		objSPK.servicePack = "SP3";
		objSPK.servicePackAsInt = 3;
		_objList.Add(objSPK);
		
		//2008-R2
		objSPK = new _SQLServicePackLatest();
		objSPK.marketing = "2008-R2";
		objSPK.servicePack = "SP3";
		objSPK.servicePackAsInt = 3;
		_objList.Add(objSPK);
				
		//2008
		objSPK = new _SQLServicePackLatest();
		objSPK.marketing = "2008";
		objSPK.servicePack = "SP4";
		objSPK.servicePackAsInt = 4;
		_objList.Add(objSPK);
						
		//2005
		objSPK = new _SQLServicePackLatest();
		objSPK.marketing = "2005";
		objSPK.servicePack = "SP4";
		objSPK.servicePackAsInt = 4;
		_objList.Add(objSPK);
								
			
	
	}
	
	
}


Code – PowerShell – SQLInstanceVersion.ps1



<# Functions a) Create Your Own PowerShell Functions http://windowsitpro.com/windows/create-your-own-powershell-functions b) PowerShell – Create collections of custom objects https://www.andreasbijl.com/powershell-create-collections-of-custom-objects/ c) Scope::Global https://www.sapien.com/blog/2013/03/06/first-rule-of-powershell-scoping-rules/ #>
Set-StrictMode -Version 2

<# List of filenames #>
$fileIn = "datafile\SQLServerInstances.txt"
$fileOut = "datafile\SQLServerVersions.txt"
#$fileOutTabbed = "datafile\SQLServerVersionsTabbed.txt"
$fileOutFailed = "datafile\SQLServerVersionFailed.txt"

#DB (SQL Server) Connection String 
$SQLConnectionStringTemplate = "Server={0}; Integrated Security = True;;Connect Timeout={1};";	

#DB (SQL Server) Connection Timeout / Default is 20 seconds
$connectionTimeout = 3;


<# Query for DB Version Using 1) ServerProperty a) serverName b) edition c) productLevel d) productVersion #>
$SQL_VERSION_RETRIEVE = 
"
	select 	
			  [sqlInstance] = serverproperty('serverName') 	
			, [edition]   = serverproperty('Edition') 
			, [productLevel] =serverproperty('productLevel')
			, [ProductVersion] = serverproperty('ProductVersion')
			, [marketing]
				= case cast
							(
								parseName
								(
									cast(serverproperty('ProductVersion') as sysname)
									, 4
								) 
								+ '.'
								+ parseName
								(
									cast(serverproperty('ProductVersion') as sysname)
									, 3
								) 
								as decimal(6, 2)
							)
						when 13 then '2016'							
						when 12 then '2014'
						when 11 then '2012'
						when 10.5 then '2008-R2'
						when 10 then '2008'
						when 9 then '2005'
						when 8 then '2000'
						else 'Undefined'
				  end 	
					  
									
"; 

<# Collections #>
$collectionSQLInstance = New-Object System.Collections.ArrayList
$collectionSQLVersion = New-Object System.Collections.ArrayList
$collectionSQLVersionInvalid = New-Object System.Collections.ArrayList

<# CHAR #>
$CHAR_TAB = "`t";
$CHAR_YES = "Y";
$CHAR_NO = "N";

<# String Format #>
$FORMAT_OUTPUT_SUCCESSFUL_HEADER = "{0,-28} {1,-20}  {2,-50}  {3,-20}  {4,-20}  {5,-6}   {6,-1} ";
$FORMAT_OUTPUT_SUCCESSFUL =  	   "{0,-28} {1,-20}  {2,-50}  {3,-20}  {4,-20}  {5,-6}   {6,-1} ";

#http://ss64.com/ps/syntax-esc.html
#`t  Horizontal tab
$FORMAT_OUTPUT_SUCCESSFUL_Tabbed = "{0,-28}`t{1,-20}`t{2,-50}`t{3,-20}`t{4,-20}`t{5,-6}`t{6,-1}";

$FORMAT_OUTPUT_FAILED = "SQL Instance: {0,-28}`r`nError:{1} ";

$FORMAT_SUMMARY_CONNECTION_ATTEMPTED  = "Number of attempted connections  {0,10}"; 
$FORMAT_SUMMARY_CONNECTION_SUCCESSFUL = "Number of successful connections {0,10}";
$FORMAT_SUMMARY_CONNECTION_FAILED     = "Number of failed connections     {0,10}";


<# Define Global Variables #>
$global:iNumberofSQLServerInstancesAttempted = 0;
$global:iNumberofSQLServerInstancesConnected = 0;
$global:iNumberofSQLServerInstancesFailed = 0;

<# addCSharpObjectTemplate Read C# file and make available to Powershell #>
function addCSharpObjectTemplate($file)
{
	
	$fileFullPath = resolve-path $file;
	
	$Source = [System.IO.File]::ReadAllText($file);
	 
	$strLog = "Importing Files :- fileFullPath: {0}"  -f $fileFullPath
	
	Write-Host $strLog
	
	Add-Type -TypeDefinition $Source -Language CSharpVersion3;

}


<# addCSharpObject Invokes addCSharpObjectTemplate for the listed files #>
function addCSharpObject
{

	$file = "_SQLInstanceNode.cs";
	addCSharpObjectTemplate($file);
	

	$file = "_SQLServicePackLatest.cs";
	addCSharpObjectTemplate($file);	

}


<# readInputFile Read Input file #>	
function readInputFile($filename, $collectionSQLInstance)
{
	ForEach ($sqlInstance in Get-Content $filename)
	{

		$collectionSQLInstance.Add($sqlInstance) | Out-Null
		
	}
}	


<# Connect to SQL Server Database from PowerShell http://stackoverflow.com/questions/25682703/connect-to-sql-server-database-from-powershell #>

function dbInteract($SQLServer)
{

	$bConnected = $false
	
	$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
	
	$sqlConnectString = $SQLConnectionStringTemplate -f $SQLServer, $connectionTimeout
	
	$SqlConnection.ConnectionString = $sqlConnectString;

	
	$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
	
	$SqlCmd.CommandText = $SQL_VERSION_RETRIEVE
						  		
	
	$SqlCmd.Connection = $SqlConnection 
	
	$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
	
	$SqlAdapter.SelectCommand = $SqlCmd 
	
	$DataSet = New-Object System.Data.DataSet
	
	
	##Try
	Try
	{
	
		#Increment Counter - Attempt
		$global:iNumberofSQLServerInstancesAttempted++;
		
		$FORMAT_SQL_CONNECTING = "Connecting to {0,4}:{1}...";
		$log = $FORMAT_SQL_CONNECTING -f $global:iNumberofSQLServerInstancesAttempted, $SQLServer
	
		Write-Host $log
		

		<# Connect to Database, run SQL, and fill dataset #>
		$SqlAdapter.Fill($DataSet) 
				
		<# Get first dataset element and place in table variable #>
		$table = $DataSet.Tables[0]
		

		<# Get datarows from table #>
		ForEach ($dataRow in $table)
		{

			#Increment Counter - Connected
			$global:iNumberofSQLServerInstancesConnected++;
		
			#Instanciate SQlInstanceNode
			$objSQLNode = New-Object _SQLInstanceNode

			#Add SQlInstanceNode to collecton			
			$collectionSQLVersion.Add($objSQLNode) | Out-Null		

			#Read Database data and place into class (_SQLInstanceNode) instance variables
			$objSQLNode.active =1;
			$objSQLNode.sqlInstance = $dataRow["sqlInstance"].ToString();
			$objSQLNode.edition = $dataRow["edition"].ToString();
			$objSQLNode.productLevel = $dataRow["productLevel"].ToString();
			$objSQLNode.productVersion = $dataRow["productVersion"].ToString();
			$objSQLNode.marketing = $dataRow["marketing"].ToString();

			
		}
			
		$bConnected = $true
	

	}	
	<# Print full exception in PowerShell try/catch block using “format-list” https://www.leaseweb.com/labs/2014/01/print-full-exception-powershell-trycatch-block-using-format-list/ #>
	Catch
	{
	
		#Increment Counter - Failed
		$global:iNumberofSQLServerInstancesFailed++;

		#Instanciate SQlInstanceNode
		$objSQLNode = New-Object _SQLInstanceNode
		
		#Add SQlInstanceNode to collecton	
		$collectionSQLVersionInvalid.Add($objSQLNode) | Out-Null			

		#Set class (_SQLInstanceNode) instance variables when failure occurs
		$objSQLNode.active =0;
		$objSQLNode.sqlInstance = $SQLServer;
		
		$objSQLNode.errorMessage = $_.Exception.Message
		
		$bConnected = $false

		
	}
	
	if ($SqlConnection)
	{
		$SqlConnection.Close() 
	}		

	#End :database Intraction


}

<# processQLInstance Process list of sql server instances #>
function processQLInstance($collectionSQLInstance)
{

	$bConnected = $false;

	$log = "Processing through SQL Server Instances..."
	
	Write-Host $log
	
	ForEach ($sqlInstance in $collectionSQLInstance)
	{
	
		$bConnected = dbInteract ( $sqlInstance ) | Out-Null
		
	}
	
	matchToSQLServerVersionMatrix $collectionSQLVersion
	
}	


<# matchToSQLServerVersionMatrix For each SQL Server Instance processed: a) Find matching record in list of sql server latest service pack #>
function matchToSQLServerVersionMatrix($SQLInstanceListConnected)
{
		
	$objSQLSPLL = New-Object _SQLServicePackLatestList
	
	$objSPList = $objSQLSPLL.list;
	$count =  $objSQLSPLL.count;
	$i = 0;

	$log = "# of SQL Server Service Pack List {0}" -f $count
	Write-Debug $log	
		
	#Iterate SQL Instances
	ForEach ($objIns in $SQLInstanceListConnected)
	{
	
	    #Reset counters
		$i = 0;
		
		#Set related sql instance variable to defaults
		$objIns.patchedATLatest = $CHAR_NO;
		
		#Iterate SQL Server Service Pack List
		while ($i -lt $count  )
		{

			#Get contextual Service Pack
			$objSP = $objSPList[$i]
			
			#If SQL Instance marketing is same as SP List Marketing
			if ($objIns.marketing -eq $objSP.marketing)
			{
			
				#Atach SP List Latest service pack to SQL Instance [ServicePackLatest]
				$objIns.servicePackLatest = $objSP.servicePack;

				# if product level service pack is at latest patch then set indicator to Yes
				if ($objIns.productLevel -eq $objSP.servicePack )
				{
				
					$objIns.patchedATLatest = $CHAR_YES;
					
				}			
				
				#Once found exit Service Pack loop
				break;

				
			}
				
			#Go to next Service Pack	
			$i = $i + 1;			
			
		} ## while	
		
	}
	
}	

function writeResult($SQLInstanceListConnected)
{

	$log = $null;
	$logTabbedAlt = $null;

	$log = "Writing Result to {0}" -f $fileOut
	Write-Host $log
	
	$log = $FORMAT_OUTPUT_SUCCESSFUL_HEADER -f "SQLInstance", "ProductVersion", "Edition", "Marketing", "ProductLevel", "Latest", "Patched"
	Add-Content $fileOut $log	
	#Write-Host $log

	$log = $FORMAT_OUTPUT_SUCCESSFUL_HEADER -f "===========", "==============", "=======", "============", "=========", "======", "=======";	
	Add-Content $fileOut $log	
	#Write-Host $log
	
	ForEach ($objIns in $SQLInstanceListConnected)
	{
		
		#Format Output
		$log = $FORMAT_OUTPUT_SUCCESSFUL -f $objIns.sqlInstance,$objIns.productVersion `
											, $objIns.edition, $objIns.marketing `
											, $objIns.productLevel, $objIns.servicePackLatest `
											, $objIns.patchedATLatest
							
		
		#Write-Host $log
			
		Add-Content $fileOut $log
		
		<# $logTabbed = $FORMAT_OUTPUT_SUCCESSFUL_Tabbed ` -f $objIns.sqlInstance,$objIns.productVersion ` , $objIns.edition, $objIns.marketing ` , $objIns.productLevel, $objIns.servicePackLatest ` , $objIns.patchedATLatest Add-Content $fileOutTabbed $logTabbed #>
		
		<# $logTabbedAlt = $objIns.sqlInstance ` + $CHAR_TAB + $objIns.productVersion ` + $CHAR_TAB + $objIns.edition ` + $CHAR_TAB + $objIns.marketing ` + $CHAR_TAB + $objIns.productLevel ` + $CHAR_TAB + $objIns.servicePackLatest ` + $CHAR_TAB + $objIns.patchedATLatest ` + " (2)" Add-Content $fileOutTabbed $logTabbedAlt Add-Content $fileOutTabbed "" Add-Content $fileOutTabbed "" #>
		
	}
	
}	


function writeResultInvalid($collectionSQLVersionInvalid)
{

	$log = "Writing Result for failed connections to {0}" -f $fileOutFailed
	Write-Host $log
	

	#$collectionSQLVersionInvalid
		
	$log = "";
	
	
	ForEach ($sqlIns in $collectionSQLVersionInvalid)
	{
		
		$log = $FORMAT_OUTPUT_FAILED -f $sqlIns.sqlInstance, $sqlIns.errorMessage;
		
		#Write-Host $log
		
		Add-Content $fileOutFailed $log
		
		Add-Content $fileOutFailed ""
		Add-Content $fileOutFailed ""
		
	}
	
	
}	


function writeSummary()
{

	#$iNumberofSQLServerInstancesAttempted
	$log = $FORMAT_SUMMARY_CONNECTION_ATTEMPTED -f $global:iNumberofSQLServerInstancesAttempted
	Write-Host $log
	
	$log = $FORMAT_SUMMARY_CONNECTION_SUCCESSFUL -f $global:iNumberofSQLServerInstancesConnected
	Write-Host $log
	
	$log = $FORMAT_SUMMARY_CONNECTION_FAILED  -f $global:iNumberofSQLServerInstancesFailed
	Write-Host $log
	
}	

addCSharpObject

readInputFile $fileIn $collectionSQLInstance

processQLInstance  $collectionSQLInstance



#https://blogs.technet.microsoft.com/heyscriptingguy/2015/06/07/powertip-use-powershell-to-create-new-file/
New-Item $fileOut -ItemType file  -force | Out-Null	
#New-Item $fileOutTabbed -ItemType file  -force | Out-Null	
New-Item $fileOutFailed -ItemType file  -force | Out-Null			

writeResult $collectionSQLVersion

writeResultInvalid $collectionSQLVersionInvalid

writeSummary


Source Code Repository

  1. Github

MS Windows – PowerShell – List Registered Network Filters

Background

Quick stolen code for listing Network Filters, Maximum Number of Network Filters allowed, and current count.

BTW, this is a quick follow up to our last post.

Code



$mesg="";
$branch = "hklm:\SYSTEM\CurrentControlSet\Control\Network"

$objNetwork = (get-itemproperty $branch)

if (!$objNetwork)
{
	$err = "(get-itemproperty $branch failed!"
	
	Throw $err
}

$maxnumfilters = $objNetwork.maxnumfilters

$mesg = "Maxnumfilters: " + $maxnumfilters
Write-Host $mesg
Write-Host 

$children = get-childitem -path $branch\"{4d36e974-e325-11ce-bfc1-08002be10318}" `
				| get-childitem `
				| where-object {$_.PSChildName -eq "Ndi"} `
				| get-itemproperty `
				| where-object {$_.FilterClass} `
				|get-itemproperty -name FilterClass


if (!$children)
{
	$err = "(get-childitem $branch failed!"
	
	Throw $err
}

$mesg="Filter Count: " + $children.count
Write-Host $mesg
Write-Host 

Write-Host "Filter List: "
Write-Host "-------------"
Write-Host 

$header =    "{0,-20} {1,-20} {2,-60}" `
				-f "infPath",    "componentID",  "description"
				
Write-Host $header

$headerSep = "{0,-20} {1,-20} {2,-60}" `
				-f "-------",    "-----------",  "-----------"
				
Write-Host $headerSep

$children | foreach-object { 

	$_objPath = get-itemproperty -path $_.PSParentPath 

	$_infPath = $_objPath.InfPath
	
	$_componentID = $_objPath.ComponentId 

	$_description = $_objPath.Description

	$data = "{0,-20} {1,-20}  {2,-60}" `
	          -f $_infPath, $_componentID, $_description

	Write-Host $data

}

Sample Output

networkfilters-self-20161019-0347pm

 

Takeaway

  1. Number of Network Filters
    • The # of Network Filters is 9
    • 9 is 8+1
    • And, that explains why we received an error when we tried to manually add netnm3.inf
    • BTW, the error message is “Filters currently installed on the system have reached the limit
    • Our experience is detailed here
  2. Based on collaborating posts on the Net, we increased our soft limit from 8 to 15