SCCM Reports Fail to Run – Incorrect User Name or Password Error for Default Data Source


When running reports in SCCM I kept getting incorrect user name or password errors for the default SCCM SSRS data source despite all accounts and passwords relating to the SCCM Reporting Services Point, the SSRS configuration and SQL Server being correct.


  • 1 x Standalone Primary Site Server
  • 1 x server running SQL Server for the SCCM and SSRS databases
  • 1 x server with SSRS installed
  • All servers Windows Server 2019
  • SQL Server 2017
  • SSRS 2017
  • Clean SCCM 1906 install
  • SCCM Reporting Services installed by the book following the link below and previous experience of doing the install countless times without any issues.

Spent two days trying to fix including reinstall and got nowhere.  In desparation tried to delete and recreate the default SCCM SSRS data source and that failed as well (data source already exists error when trying to reuse the default data source name) but it did unlink the default data source from all the canned SCCM reports.

Thought I now had two problems but the script below not only reattached the data source to the canned reports it also fixed the dodgy user name and password issue.

All has been fine since though I did need to run multiple times against each report folder, after changing the folder name in the script (sure there is a way to script this but I am happy enough doing this as-and-when I want to use a particular set of reports!).

#Set variables
#Enter the name of the SSRS server used by SCCM in the next line – in my case the server name is cmrs6svr
$reportserver = “cmrs6svr”;

#URL to connect to the SSRS server
$url = “http://$($reportserver)/reportserver/reportservice2005.asmx?WSDL”;

#Provide new data source path. In this case I am reapplying the default SCCM data source to fix the issue discussed in this post
$newDataSourcePath = “/ConfigMgr_WR6/{5C6358F2-4BB6-4a1b-A16E-8D96795D8602}”

#Provide new Data source Name which is part of above source path
$newDataSourceName = “{5C6358F2-4BB6-4a1b-A16E-8D96795D8602}”;

# SSRS report folder path that contains the reports to change the datasource. In this case the default SCCM folder is called ‘ConfigMgr_WR6’
# and the new datasource is being written to all the reports in the ‘Software Updates – E Troubleshooting’ folder
$reportFolderPath = “/ConfigMgr_WR6/Software Updates – E Troubleshooting”


$ssrs = New-WebServiceProxy -uri $url -UseDefaultCredential

$reports = $ssrs.ListChildren($reportFolderPath, $false)

$reports | ForEach-Object {

$reportPath = $_.path
Write-Host “Report: ” $reportPath
$dataSources = $ssrs.GetItemDataSources($reportPath)
$dataSources | ForEach-Object {

$proxyNamespace = $_.GetType().Namespace
$myDataSource = New-Object (“$proxyNamespace.DataSource”)
$myDataSource.Item = New-Object (“$proxyNamespace.DataSourceReference”)
$myDataSource.Item.Reference = $newDataSourcePath
$_.item = $myDataSource.Item
$ssrs.SetItemDataSources($reportPath, $_)

Write-Host “Report’s DataSource Reference ($($_.Name)): $($_.Item.Reference)”;

Write-Host “————————“

Thanks to Tim at is required, as well as Eswar at – more info using the links below.

CMPivot Standalone Installer

SCCM 1906 introduced some cool stuff relating to CMPivot and one of my favourites is the standalone installer for CMPivot so you can use without having to access the SCCM admin console (the installer is available in the Tools folder under your main install folder e.g. C:\Program Files\Microsoft Configuration Manager\tools.

Tip – there is a dropdown box in the top left of the CMPivot window that allows you to browse and select the collection to work CMPivot against.



Migrating the Sophos Enterprise Console Databases to a New Server

Basic Information
⦁ Sophos Enterprise Console (SEC) 5.5.0
⦁ One Management/Console Server
⦁ Database on a remote SQL Server – default instance

Database names
⦁ SophosSecurity

Database and Log File Names
⦁ SOPHOS550.mdf and SOPHOS550_log.LDF
⦁ SophosSecurity.mdf and SophosSecurity_log.LDF

SQL Server Settings
⦁ Database installed in the default MSSQLSERVER instance and not a SOPHOS instance – note the commands used with the batch files to backup and restore.
⦁ Sophos management user account called sopmgmt and this account has “sa” rights for SQL and is a member of the local administrators account on the server.
⦁ SQL Browser service running (install will not start if this service is not running).
⦁ Created a folder called C:\Temp\SophosBackups on both the new and existing SQL servers.

Note – to complete the install of the Sophos databases requires the SQL Server server to restarted.

Install the databases
⦁ Log on the new SQL Server server using the sopmgmt account.
⦁ Run the SEC installer and select Database Only from the options.
⦁ Change the instance name to MSSQLSERVER (can type free text in the dropdown box).
⦁ Set the account (used the logged on user – sopmgmt)
⦁ Restart the SQL Server server.

Stop Management Server Services
On the Management Server server stop the following services:
⦁ Sophos Management Service
⦁ Sophos Encryption Business Logic Service
⦁ Sophos Patch Endpoint Communicator Service
⦁ Sophos Patch Server Communicator

As this install was using a test environment the PowerShell command below was used to stop all Sophos Services:

get-service | ?{$_.DisplayName -ilike “Sophos*”} | stop-service

Note – use .\ to indicate the databases exist in the default SQL Server instance – the backup and restore batch files both default to a SQL instance named Sophos.

⦁ On the existing database server open an elevated Command Prompt and run the following command:
CD C:\Program Files\Sophos\Enterprise Console\DB\Core

Run the following in turn, assuming the backup location C:\Temp\SophosBackups exists:

⦁ backupdb.bat C:\Temp\SophosBackups\SOPHOS550db.bak .\ SOPHOS550
⦁ backupdb.bat C:\Temp\SophosBackups\SOPHOSPATCH52db.bak .\ SOPHOSPATCH52
⦁ backupdb.bat C:\Temp\SophosBackups\SophosSecuritydb.bak .\ SophosSecurity
⦁ Copy the backup files to C:\Windows\Temp\SophosBackups on the new SQL server.

⦁ On the new database server open an elevated Command Prompt and run the following command:
CD C:\Program Files\Sophos\Enterprise Console\DB\Core

Run the following in turn, assumng the backup location C:\Temp\SophosBackups exists:

⦁ restoredb.bat C:\Temp\SophosBackups\SOPHOS550db.bak .\ SOPHOS550
⦁ restoredb.bat C:\Temp\SophosBackups\SOPHOSPATCH52db.bak .\ SOPHOSPATCH52
⦁ restoredb.bat C:\Temp\SophosBackups\SophosSecuritydb.bak .\ SophosSecurity

Repoint the Management Server
On the Management Server change the server reference in the following registry locations to reflect the name of the new database server:

⦁HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Sophos\EE\Management Tools\DatabaseConnectionMS

⦁ Restart the Management Server server.


CMPivot Operators

== Equals
!= Not equals
like LHS contains a match for RHS
!like LHS doesn’t contain a match for RHS
contains RHS occurs as a subsequence of LHS
!contains RHS doesn’t occur in LHS
startswith RHS is an initial subsequence of LHS
!startswith RHS isn’t an initial subsequence of LHS
endswith RHS is a closing subsequence of LHS
!endswith RHS isn’t a closing subsequence of LHS


CMPivot Examples

To accesss CMPivot, highlight a Collection containing the machines you wish to query against and then click Start CMPivot from the ribbon or right-click context menu.

The CMPivot query below returns all VMware devices in the collection used to run CMPivot against:

device | where ManuFacturer == ‘VMware, Inc.’ and (Device like ‘%CM%’ and Model like ‘%VM%’)


  • where and like are case sensitve
  • Device, manuFacturer, ‘VMware, Inc.’,, ‘%CM%’, ‘%svR%’ and ‘%VM%’ are not case sensitive.
  • Need to use a double == for equals.
  • Use % as a wildcard.
  • Text in single snicketts.
  • Use of () is for nesting but not required if nesting not used.

Count the number if devices per manufacturer.
Device | summarize dcount (Device) by Manufacturer

Below shown to indicate presentation – does not need to be all one line.
| where (Manufacturer == ‘VMware, Inc.’)
| where (Device == ‘CM01’)

  • AD Security Groups that are members of local admins groups.

Administrators | where (PrincipalSource == ‘ActiveDirectory’ and ObjectClass == ‘User’)

  • Collection members where the SMS Executive service is running.

Service | where Name == ‘SMS_EXECUTIVE’ | where (State == ‘Running’)

  • Count the different OS version of the devices in the colllection and then display (render) as a bar chart.

OperatingSystem | summarize dcount (Device) by Caption | render barchart

  • Disk space with limited result set using the project operator to define the displayed columns.

| where (Description == ‘Local Fixed Disk’)
| project DeviceID, FreeSpace
| order by FreeSpace desc
| where (DeviceID == ‘C:’)

  • Installed software on all devices.  In the resultset note you can click on the underlined number to drill down.

InstalledSoftware | summarize dcount(Device) by ProductName

  • More specific – specifc application on a device.

| where (ProductName == ‘Microsoft Monitoring Agent’)
| where (Device == ‘OM01’)

  • Count all installed software that does not have Microsoft in the product name using !like as the not like operator.

| where ProductName !like ‘%Microsoft%’
| summarize dcount(Device) by ProductName


SCCM 1806 Update Notes

SCCM 1802 is the latest baseline version used for clean installs.

SCCM 1806 is an update to 1802 and the most effective way to install is using an SCP and the update is presented in the console.

If you are impatient for the latest release and it is not in the console you can hurry it up using the EnableFastUpdateRingXXXX.ps1 script that Microsoft provide – where XXXX is the new version number.  The script uses the site server name as its only parameter, e.g. .\EnableFastUpdateRing1806.ps1 CM01 where CM01 is the site server name.

More info here:

Check the ADK version and if need to update do this before the SCCM in-console update.  Remove the existing ADK and then install the newer version. More info here:

Also a good idea to temporarily stop any site maintenance tasks while running the update.

Full pre-install checklist here:

Suggest you run the prereq check before starting the install and address any highlighted issues.

Thanks to Mr Brady and the System Center Dudes for their continued excellent contributions to the SCCM community.

SCOM Agent Config for Domain Controllers

Well known configuration that needs applying to domain controler after the SCOM agent is installed.

Clue that this has not been done is that the relevent domain controller will show in the SCOM console as not monitored.

  • Open an elevated command prompt.
  • Navigate to the agent install folder on the domain controller, typically C:\Program Files\Microsoft Monitoring Agent\Agent.
  • Run  – HSLockdown.exe /A “NT AUTHORITY\SYSTEM”
  • Restart the SCOM agent service – run net stop healthservice & net start healthservice

Thanks yet agin to Kevin Holman.


SCCM 1702 Failed Install – OLEDB Data Link Properties Tool

Easy way to test a connection to an OLEDB  data source.

  • Save an empty file to desktop with a UDL extension.
  • Double-click to open and use the Data Link tool to test connectivity to the required source using the choice of providers.

Used this tool when troubleshooting a failed SCCM 1702 install.  Logs indicated SQL native client error.  Tested connection to SCCM DB using this tool using the server name (worked) and then FQDN (failed).  Found a reg key to change this setting.  Restarted the SCCM 1702 install and it worked!