SCCM reports show “The DefaultValue expression for the report parameter ‘UserTokenSIDS’ contains an error: The user name or password is incorrect. (rsRuntimeErrorInExpression)”

Had a rare fresh SCCM install recently with a client that has more of a security focus than many of our other clients.

Had SCCM 2002 (current at time of writing), Server 2019, SQL 2017 and Reporting services 2017.

Reporting services is using the default, “virtual service account”, which, under the covers, is “NT Service\SQLReportingServices”

 

After everything was installed, i tried to run a report and was greeted with

The DefaultValue expression for the report parameter ‘UserTokenSIDS’ contains an error: The user name or password is incorrect. (rsRuntimeErrorInExpression)

 

A bit of research showed that

  • The account logged on successfully when the reporting services service started and the security event log showed a successful logon – so i knew the username/password was correct
  • When trying to run a report, the security log showed event ID 4625 with the account “NT Service\SQLReportingServices” and a status and sub-status of 0xC000006D and 0xC0000064 respectively. These can be looked up at https://docs.microsoft.com/en-us/windows/security/threat-protection/auditing/event-4625
  • C:\Windows\ServiceProfiles\SQLServerReportingServices\AppData\Local\Temp\SCCMReporting.log showed “The username or password is incorrect”

Given that i knew the username and password was correct – i was a bit baffled by this for a while…. i spent a bit of time looking for how the auth is passed between the user session and SSRS. Googling presented a couple of suggestions, but they were the type of “answers” that just didn’t look like they fit the issue.

Fortunately, one of the other guys on a related project suggested adding the SCCM computer account to “Windows Authorization Access Group” – and it then proceeded to work.

Once finding that out, i searched and quickly came back with https://www.enhansoft.com/windows-authorization-access-group-ssrs-and-sccm/  which shows its been an issue for a while – one i just hadn’t run into before – and apparently my google searches were a bit too specific – and the error message others were getting was slightly different.

SCCM – picking random clients for a collection

There are plenty of ways to randomly add systems to a collection in SCCM, a common query being something similar to

select SMS_R_System.Name from  SMS_R_System where SMS_R_System.SMSUniqueIdentifier like “%0” OR SMS_R_System.SMSUniqueIdentifier like “%1”

 

In a recent instance, i needed to ensure that systems were randomly chosen for a product roll out across a 1500-ish servers. The client was specific about staging the deployment and wanted to selection process of what went when to be truly random… fair enough.

SCCM cant really do do random when limited to a number…. the query above for example is likely to retrieve approx 20% of your total client count…. where-as i needed a random 50.

I ended up using the following powershell to accomplish the task.

 

Function Connect-ConfigMgr {
Param(
)
write-host -ForegroundColor Magenta “Connect-ConfigMgr”
Try {
Get-WMIObject -ComputerName $CMSiteServer -Namespace “root\SMS” -Class “SMS_ProviderLocation” -ErrorAction Stop | foreach-object{
if ($_.ProviderForLocalSite -eq $true){$Script:SiteCode=$_.sitecode}
}
if ($SiteCode -eq “”) {
throw (“Sitecode of ConfigMgr Site at ” + $CMSiteServer + ” could not be determined.”)
Exit 1
}
}
Catch {
$ErrorMessage = $_.Exception.Message
$text = “Error, could not connect to Site server $CMSiteServer, check spelling, network connectivity, permissions etc.”
$text += “`nError message: $ErrorMessage”
Write-Error $text
Exit 1
}
#$CMNamespace = “Root\SMS\$($SiteCode)”

write-host -ForegroundColor Green “ConfigMgr Site Code: $SiteCode”
write-host -ForegroundColor Green “ConfigMgr Site Server: $CMSiteServer”
#write-host “ConfigMgr Name Space: $CMNamespace”

#Import ConfigMgr Module
if (!(Get-Module -Name ConfigurationManager)) {
write-host “PowerShell module not loaded.”
If (Test-Path -Path “$($ENV:SMS_ADMIN_UI_PATH)\..\ConfigurationManager.psd1” -PathType Leaf) {
write-host “Attempting loa PS module from: $($ENV:SMS_ADMIN_UI_PATH)\..\ConfigurationManager.psd1”
Import-Module “$($ENV:SMS_ADMIN_UI_PATH)\..\ConfigurationManager.psd1” | Out-Null
}
ElseIf (Test-Path “$($ScriptPath)\ConfigurationManager.psd1” -PathType Leaf) {
write-host “Attempting loa PS module from: $($ScriptPath)\ConfigurationManager.psd1”
Import-Module “$($ScriptPath)\ConfigurationManager.psd1” | Out-Null
}
Else {
$text = “Error, unable to load PowerShell module ConfigurationManager.psd1, file not found.”
Write-Error $text
Exit 1
}
}
if (!(Get-Module -Name ConfigurationManager)) {
$text = “Error loading PowerShell module ConfigurationManager.psd1.”
Write-Error $text
Exit 1
}

#Set the current location to be the site code.
Set-Location “$SiteCode`:”
write-host -ForegroundColor Green “————————————————————————”
write-host “”
}### End Connect-ConfigMgr ###

Function GetRandomCollectionMembers {
Param(
)
write-host -ForegroundColor Magenta “Getting collection members”
$CollMembers = Get-CMCollectionMember -CollectionName $SourcecollectionName

#Randomising and selecting first x members
write-host -ForegroundColor Magenta “Selecting $NumberofMembers members randomly”
$CollMembers = $CollMembers | Sort-Object {Get-Random}
$CollMembers = $CollMembers | Select -First $NumberofMembers

Foreach ($CollMember in $CollMembers) {
$CollectionName = $CollMember.Name
write-host -ForegroundColor Green “Adding $CollectionName to $DestinationCollectionName”
Get-CMCollection -Name $DestinationCollectionName | Add-CMDeviceCollectionDirectMembershipRule -ResourceId $CollMember.ResourceID
}

}

##############Start of Main Script ####################

#Begin {

## Get script path and name
$ScriptPath = [System.IO.Path]::GetDirectoryName($MyInvocation.MyCommand.Definition)
$ScriptName = [System.IO.Path]::GetFileNameWithoutExtension($MyInvocation.MyCommand.Definition)
$PowerShellVersion = [int]$PSVersionTable.PSVersion.Major

#Save current path, to return after CM operations
$path = Split-Path -parent $MyInvocation.MyCommand.Definition

#Set variables here
$CMSiteServer = ‘sccm01.adexis.com.au’
$Script:SiteCode = $null
$SourcecollectionName = “All Systems”
$DestinationCollectionName = “RandomSystems”
$NumberofMembers = “5”

Connect-ConfigMgr
GetRandomCollectionMembers

#} #End Begin

SCCM antimalware client policies – do they merge ?

There seems to be a great deal of mis-information about this floating around the web…. despite articles like this that lay it out quite well.

When you create an SCCM antimalware policy, the settings do merge.

You do not have to create bucketloads of policies and apply/re-apply the same settings over and over and over again.

This insane practice makes anti-malware policies much harder to manage and track.

 

Testing

In order to prove this for yourself, do the following:

  • add a process into the default anti-malware policy – give it an obvious name such as “DefaultPolicyExclusion.exe”
  • Now create a new policy, add an exclusion called “NewPolicyexclusion.exe”
  • Apply the new policy to a machine and update policy on that machine
  • Check the exclusion list (settings | Updates and Security | windows security | Virus and threat protection | manage settings | Add or remove exclusions)
  • You can now see that both exclusions are listed

 

You can also run the following from a command prompt to see which policies are applied

reg query HKLM\SOFTWARE\Microsoft\CCM\EPAgent\LastAppliedPolicy /f 2 /d

Notice that the exclusions are applied from both “Default client antimalware policy” and whatever you called your new policy.

 

Why this matters

We have clients that (correctly) create new antimalware policies for different types of servers…. e.g. to exclude sqlservr.exe on SQL servers – which makes complete sense.

Unfortunately, they then proceed to define every other setting that is available….. which means that if we want to change real time settings (for example) – we need to go into every single policy to do so. A little painful but not to base with 10 policies, but just unnecessarily time consuming when there are hundreds.

 

What about conflicting settings ?

This is where priority comes into play.

Its very simple… in the even of a conflicting setting (e.g. Real time scanning enabled in one policy and disabled in another) then the one with the lowest priority will win. This is referred to as “Order” in some parts of the console and Priority in other parts of the console. (As of SCCM 1910)

Notice that the default policy has a priority of 10000, which is not changeable – and therefore will always be the lowest priority settings.

 

A better way

Just like your SCCM client policies, the best way to handle anti-malware policies is to set the defaults and then only make additional policies where you need to deviate from those defaults.

Set all the default settings for all machines across the fleet in the “Default Client antimalware Policy”, then create additional policies where required…. but only set the specific settings that deviate from the default settings – not every setting!

 

Another approach, for slightly larger organisations is to:

  • Leave the default policy alone
  • Create a Default workstation policy – which the desktop guys control
  • Create a Default server policy – which the server guys control
  • And so on (for different area’s of the business if there is a management demarcation)
  • Deploy appropriately

The downside of this model is that the default policy will keep needing to moved to the lowest priority each time you create a new policy.

 

What should i be excluding ?

https://social.technet.microsoft.com/wiki/contents/articles/953.microsoft-anti-virus-exclusion-list.aspx

 

A practical example

In this example were going to focus on an organisation with x00 servers, 2 of which are:

  • SCCM with SQL co-loctaed
  • Payroll application server with SQL co-located

 

In this case we would

 

Apply the SQL server policy to anything running SQL

Apply the SCCM server policy to anything running SCCM

Apply the payroll server policy to the payroll server

 

This way, when we update the SQL server policy – it flows through to all SQL servers automatically. We dont have to update the SCCM policy and the payroll server policy.

Surface Pro X and Pro 7 – SCCM deployment

A few years back – an (ex) employee posted on the Adexis blog about the fun of Surface laptops and deployment in SCCM

https://blog.adexis.com.au/2018/04/09/microsoft-surface-laptops-and-sccm-os-deployment/#comment-1478

Personally, i’ve deployed a lot of Surface Pro 3’s and 4’s…. but not so much after that…. until now.

During the week i was called into a client to find out why their new Surface Pro devices were not PXE booting.

Once i attended site, had a look at the pxe log – and saw unsupported architecture… strange…. both the boot images are distributed and boot roms are correct…

Upon further investigation and google – i found that the devices were Surface Pro X’s which are ARM based and not supported for deployment via SCCM or MDT.

https://docs.microsoft.com/en-us/surface/surface-pro-arm-app-management

the TLDR of the article is that the Surface Pro X is not an enterprise device…. the client managed to get devices replaced with Surface Pro 7’s – and they actually got a refund, as the 7 was slightly less expensive than the X….

 

Onto the surface pro 7’s….

These can be detected in the task sequence via a MWI query of

SELECT * FROM Win32_ComputerSystem WHERE Model = “Surface Pro 7″

Or by using the custom model variable from global conditions….

I prefer having one application with multiple deployments, each with a global condition of model…. this keeps the driver database less cluttered and is, for want of a better term, “neat”.

Issue with this is that the Surface Pro 7 drivers install fine from the MSI, but kill the task sequence…. given the time restraints on this project, i didnt really have time to check… so i extracted the drivers from the MSI, imported them as drivers and allowed auto-apply to do its job.

 

TL;DR version of the whole article

  • Surface Pro X – no you cant deploy it via SCCM and yes it is shitty/confusing naming from MS
  • Surface Pro 7 – installing drivers via MSI will kill your task sequence – extract the drivers and install them as part of auto apply or apply drivers instead

 

Deploying the new chromium based edge via SCCM

New Edge, Credge, Edgium or Credgium – no matter what you call it, the Edge browser based on chromium was released on Jan 15th 2020.

To deploy the new versions using SCCM 1910 (or above)

  • Add “Microsoft Edge” a product that is sync’ed in your software update point component properties
    • Somewhat confusingly, the product is under the sub-category of “Windows”

  • Then navigate to Software Library > Microsoft Edge Management | All Microsoft edge update

 

  • Right click and select “:synchronise software updates”
  • You can follow the progress of this process by checking wsyncmgr.log – the same log as you use to follow all software update sync’s
  • At this stage – you can wait for anywhere from 5 minutes (for an environment that has sync’ed relatively recently) to hours (if this is your first sync)
  • Once completed, you can refresh by hitting F5 – and you should see some content update for Edge in the right-hand pane
  • Now right-click on the top node “Microsoft Edge Management” (yes, this is not intuitive) and select “Create Microsoft Edge application”
    • Select and name and a source directory to be used
    • Select a channel, or specific version that you wish to deploy. If you’re not sure here – use “stable” and “latest”
    • Deploy to a collection, select your DP’s etc – all pretty standard stuff here
  • You’ll notice that an application is created for you under the “applications” node – this is different to other software updates which are created as Software Update groups (SUG’s)
  • Personally, i’d prefer to see it all managed under the one node – however, its still a good feature overall – as simplifies deployment of edge greatly – but still allows the admin to go and edit properties of the deployment if… for the invetiable situation of where the Microsoft pre-defined install doesn’t meet your orgs needs

 

Once completed, as per any deployment – monitor your deployment via the “deployments” area in the monitoring tab.

 

*Update 28/02/2020 *

When installing, you may get the following error (or similar)

App install failed.
Install application action failed: ‘APP.Edge.Latest’. Error Code0x80004005
Sending StatusMessage
Setting the authenticator.
CLibSMSMessageWinHttpTransport::Send: WinHttpOpenRequest – URL: SCCM01.company.com.au:80 CCM_POST /ccm_system/request
Not in SSL.
Request was successful.
hrInstallation, HRESULT=80004005 (installapplication.cpp,989)
pInstall->InstallApplications(saAppNames, sContinueOnError), HRESULT=80004005 (main.cpp,284)
Install application action cannot continue. ContinueOnErrorFlag is set to false.
Install Static Applications failed, hr=0x80004005

This is due to the powershell policy…. to get around this, modify the command line from this

powershell -File “.\Install-Edge.ps1” -MSIName “MicrosoftEdgeEnterpriseX64.msi” -ChannelID “{56eb18f8-b008-4cbd-b6d2-8c97fe7e9062}”

to this

powershell -executionpolicy bypass -File “.\Install-Edge.ps1” -MSIName “MicrosoftEdgeEnterpriseX64.msi” -ChannelID “{56eb18f8-b008-4cbd-b6d2-8c97fe7e9062}”

SCEP updates fail on Windows 7 and 2008R2 clients where they are not patched

Ok, so, before the flood of hate comes my way

  1. Yes, its 2020, you should not be running Server 2008 R2 or Windows 7…… but the (unfortunate) reality is that many places still are
  2. Even if you are running them, they should be fully patched! “Patches break applications”…. mostly bullshit…. it does happen… but happens much much much less than what some admins claim.

Anyway, for those of you out there that have Windows 7 or 2008R2 SCCM clients that are, for whatever reason, unpatched, you may have also noticed that SCEP updates stopped working on them around October 21st 2019.

That’s because of this – https://www.microsoft.com/en-us/wdsi/defenderupdates

specifically the lines

Note: Starting on Monday October 21, 2019, the Security intelligence update packages will be SHA2 signed.
Please make sure you have the necessary update installed to support SHA2 signing, see 2019 SHA-2 Code Signing Support requirement for Windows and WSUS.

IF you try and install SCEP updates you will get an entry in the system event log similar to this:

Microsoft Antimalware has encountered an error trying to update signatures.
New Signature Version: 1.307.1945.0
Previous Signature Version:
Update Source: User
Update Stage: Install
Source Path:
Signature Type: AntiSpyware
Update Type: Full
User: Domain\User
Current Engine Version: 1.1.16600.7
Previous Engine Version:
Error code: 0x800b0109
Error description: A certificate chain processed, but terminated in a root certificate which is not trusted by the trust provider.

 

As this link – https://support.microsoft.com/en-us/help/4472027/2019-sha-2-code-signing-support-requirement-for-windows-and-wsus  specifies, you must install

  1. KB4474419
  2. KB4490628

once these updates are installed (and reboot of course), SCEP updates will then install.

Finding the right SQL view for your SCCM report

One of the banes of many SCCM admins existence is reporting. Some SCCM people are SQL guns, others, like me, know what they need to to get by, but a strong SQL understanding – that just isn’t me (and i know i’m not alone!).

One of the key things with SCCM reports, and something that some consultancies we deal with ignore is the requirement to use views for your reports, not directly talk to the tables.

The official SCCM doco dances around this and doesn’t explicitly state it (even though it does reference views constantly, it does not specifically state that using tables is unsupported – and it should)

https://docs.microsoft.com/en-us/sccm/core/servers/manage/creating-custom-report-models-in-sql-server-reporting-services

https://docs.microsoft.com/en-us/sccm/develop/core/understand/sqlviews/sql-server-views-configuration-manager

 

These posts are far better at laying it out as it is

https://www.enhansoft.com/what-are-the-supported-sql-server-views-to-use-with-sccm-reporting/

https://www.enhansoft.com/why-is-it-important-to-use-supported-sql-server-views-with-sccm-reporting/

 

The TL;DR version of the ehansoft articles is:

  • The SCCM reporting services account is specified during setup and is granted access to all the views – but not the tables etc
  • The 2nd enhansoft article lays out why views are utilised instead of tables – and the associated benefits
  • You can grant datareader access to accounts over the SCCM database to get around this – but its officially (MS) unsupported, and can cause a range of issues (again, laid out in the 2nd article)
    • There are people out on the web that recommend just allocating permissions – even some MVPs – which doesn’t help
  • My recommendation is to remain in “supported” territory and update the report to use views rather than tables directly (knowing full well that we have all referenced tables directly at some point in our careers!)

 

A couple of things i have found handy with this are:

 

When you know the table you wish to reference, but aren’t sure which views you can use (and you want to do the right thing and use a view)

SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE VIEW_DEFINITION like ‘%tablename%’ OR VIEW_DEFINITION like ‘%tablename%’ OR VIEW_DEFINITION like ‘%tablename%’

 

When you can find what your looking for in the database

https://www.apexsql.com/sql-tools-search.aspx

 

 

BITS / ConfigMgr client install error, no active network connections

Ran into this fun one today, admittedly it was in my dev environment and is not a common scenario for our clients, which usually have much larger networks and network segments.

Trying to install the ConfigMgr client, via client push or manually, fails with the below error repeating in ccmsetup.log

Download Update: A recoverable error has occurred.  A retry attempt will be made. Error: 0x80200010, Description There are currently no active network connections. Background Intelligent Transfer Service (BITS) will try again when an adapter is connected, Context: The error occurred in the Background Intelligent Transfer Service (BITS) queue manager.

It occurs when no default gateway is configured or pingable, even if the machines are on the same subnet as the server. The workaround is to add a pingable default gateway to the network adapter.

It’s an issue with BITS in Windows 10 1903 and continues to be an issue with 1909, it has already been logged with Microsoft and is supposedly fixed in 20H1.

Microsoft Endpoint Manager – potentially SCCM’s future

Microsoft Endpoint Manager – announced at Ignite – https://redmondmag.com/articles/2019/11/04/microsoft-endpoint-manager.aspx

There’s a lot of words in the announcement that don’t mean much….  but it is an indication of how MS plan to move forward in this space.

As it stands, the SCCM/Intune integration attempts have ranged from pretty bad to OK…. it will be interesting to see how its done…. i really hope its done well!

This should also hopefully quieten the array of enterprise architects out there running around saying that SCCM is dead – ignoring all the evidence to the contrary.

The CB updates to SCCM have made substantial inroads to making SCCM a better product – hopefully this continues along with solid intune integration.

SCCM 1906 release

SCCM 1906 released! – https://docs.microsoft.com/en-us/sccm/core/plan-design/changes/whats-new-in-version-1906

If you want it right now – you can opt-in via the fast ring script – https://go.microsoft.com/fwlink/?linkid=2099733

Another belting release from the SCCM team – while some releases have different focuses over time – generally releases have tended to have something which makes day-to-day admin life a bit easier for someone…. and sometimes, big things, such as passive site servers, that just structurally make the product substantially better.

Anyway – out of this release comes a couple of items that i think are particularly of note to me (other may be interested in different parts – depending on your setup):

  • Site maintenance UI is friendlier
  • Use your distribution point as an in-network cache server for Delivery Optimization
  • Support for Windows Virtual Desktop
  • OneTrace – a preview of a tool which could be dubbed the next-gen of CMTrace…
  • Improvements to co-management auto-enrollment
  • Retry the install of pre-approved applications
  • Task sequence debugger – not there yet – but great that its being thought about – can see this being exceedingly handy as this matures
  • The Disable BitLocker task sequence step has a new restart counter
  • Additional options for WSUS maintenance – these are brilliant. Hopefully this release will also fix bug id 4808740 – i haven’t been able to test this as yet.
  • New Windows 10, version 1903 and later product category – this enables admins everywhere to further reduce the number of updates stored by WSUS – which leads to improvements with server and client performance
  • Role-based access for folders – finally!
  • Administration service support for security nodes – this is potentially a big change – keen to test this out in a bigger environment
  • Collections tab in devices node – gradually chipping away at right click tools functionality
  • Multiselect and delete packages – finally!