Help & Installation

# Verentra SCCM Patching Dashboard - Installation Guide

> All SQL access described here is **read-only**. The service account used by this
> application must have **SELECT permission only**. Never grant write, DDL or
> sysadmin rights on a Configuration Manager site database.

---

## 1. SCCM Reporting Services prerequisites

- A Configuration Manager site with the **Reporting Services Point** installed.
- SQL Server Reporting Services 2016 or later (or Power BI Report Server).
- Network access from the reporting point to the site database.
- The site database name, usually in the form `CM_ABC` where ABC is the site code.
- Software update summarization running on a schedule (Monitoring > Deployments).

## 2. Creating a read-only SQL service account

Domain account (recommended - supports Windows Integrated Authentication):

1. Create `VERENTRA\svc-sccm-reader` in Active Directory.
2. Set a long random password and enable "Password never expires" only if policy allows.
3. Do not add the account to any administrative group.

SQL login alternative:

```sql
-- Run on the SCCM site database server, in the master database
CREATE LOGIN [VERENTRA\svc-sccm-reader] FROM WINDOWS;
```

## 3. Assigning the minimum required database permissions

```sql
USE [CM_ABC];
CREATE USER [VERENTRA\svc-sccm-reader] FOR LOGIN [VERENTRA\svc-sccm-reader];

-- Least privilege: grant SELECT on the reporting views only.
GRANT SELECT ON OBJECT::dbo.v_R_System                    TO [VERENTRA\svc-sccm-reader];
GRANT SELECT ON OBJECT::dbo.v_R_User                      TO [VERENTRA\svc-sccm-reader];
GRANT SELECT ON OBJECT::dbo.v_FullCollectionMembership    TO [VERENTRA\svc-sccm-reader];
GRANT SELECT ON OBJECT::dbo.v_Collection                  TO [VERENTRA\svc-sccm-reader];
GRANT SELECT ON OBJECT::dbo.v_UpdateInfo                  TO [VERENTRA\svc-sccm-reader];
GRANT SELECT ON OBJECT::dbo.v_Update_ComplianceStatusAll  TO [VERENTRA\svc-sccm-reader];
GRANT SELECT ON OBJECT::dbo.v_UpdateScanStatus            TO [VERENTRA\svc-sccm-reader];
GRANT SELECT ON OBJECT::dbo.v_CIAssignment                TO [VERENTRA\svc-sccm-reader];
GRANT SELECT ON OBJECT::dbo.v_CIAssignmentToCI            TO [VERENTRA\svc-sccm-reader];
GRANT SELECT ON OBJECT::dbo.v_CombinedDeviceResources TO [VERENTRA\svc-sccm-reader];
GRANT SELECT ON OBJECT::dbo.v_DeploymentSummary            TO [VERENTRA\svc-sccm-reader];
GRANT SELECT ON OBJECT::dbo.v_StateNames                  TO [VERENTRA\svc-sccm-reader];
GRANT SELECT ON OBJECT::dbo.v_GS_OPERATING_SYSTEM         TO [VERENTRA\svc-sccm-reader];
GRANT SELECT ON OBJECT::dbo.v_GS_WORKSTATION_STATUS       TO [VERENTRA\svc-sccm-reader];
GRANT SELECT ON OBJECT::dbo.v_CH_ClientSummary            TO [VERENTRA\svc-sccm-reader];
GRANT SELECT ON OBJECT::dbo.v_LocalizedCIProperties       TO [VERENTRA\svc-sccm-reader];
GRANT SELECT ON OBJECT::dbo.v_CategoryInfo                TO [VERENTRA\svc-sccm-reader];

-- Optional wider alternative (still read-only):
-- ALTER ROLE db_datareader ADD MEMBER [VERENTRA\svc-sccm-reader];

-- Explicitly deny writes as a defence-in-depth measure:
DENY INSERT, UPDATE, DELETE, ALTER, CONTROL TO [VERENTRA\svc-sccm-reader];
```

## 4. Configuring the SCCM connection

Because this dashboard is hosted, it cannot open a TCP connection to an internal
SQL Server directly from the browser or from the hosted API. Deploy the
**Verentra on-premises connector** inside your network:

```text
Browser (dashboard UI)
        |  HTTPS
Hosted server-side API  (holds no SQL credentials for on-prem SQL)
        |  HTTPS + mutual token, outbound-initiated from your network
On-premises read-only connector service (Windows service / container)
        |  TDS 1433, read-only SQL login or Windows Integrated Auth
SCCM SQL Server (CM_ABC site database)
        |
SSRS report download (RDL imported into Monitoring > Reporting > Reports)
```

Connector configuration values are stored as encrypted server-side secrets:

| Secret | Purpose |
| --- | --- |
| `SCCM_SQL_SERVER` | SQL Server host name |
| `SCCM_SQL_INSTANCE` | Named instance (optional) |
| `SCCM_SQL_DATABASE` | Site database, e.g. CM_ABC |
| `SCCM_SQL_AUTH_MODE` | `integrated` or `sql` |
| `SCCM_SQL_USERNAME` | Read-only SQL login (SQL auth only) |
| `SCCM_SQL_PASSWORD` | Read-only SQL password (write-only, never displayed) |
| `SCCM_CONNECTOR_TOKEN` | Shared token authenticating the on-prem connector |

## 5. Running schema validation

Open **SQL Connection Settings > Schema Validation**. Every required view and
column is probed with `INFORMATION_SCHEMA` queries. Production queries stay
disabled until validation succeeds. Missing views are reported with supported
alternatives; the application never silently substitutes an SCCM base table.

## 6. Configuring SSRS shared data sources

1. In Report Manager, open the ConfigMgr reports folder.
2. Create or reuse a shared data source pointing at the site database.
3. Set credentials to **Windows Integrated** or a stored read-only account.
4. Copy the shared data source path, for example `/ConfigMgr_ABC/{GUID}`.
5. Paste it into **Report Builder > Shared data source path**.

## 7. Downloading the RDL file

Open **Report Builder**, choose your SSRS version, validate, then choose
**Download RDL**. The file is named `SCCM-Patching-Dashboard.rdl` and contains
no server name, database name, user name or password.

## 8. Importing the RDL into SCCM Reporting Services

1. Console: **Monitoring > Reporting > Reports**.
2. Right-click a folder and choose **Create Report** or upload via Report Manager
   (`http://<reportserver>/Reports`) using **Upload File**.
3. Open the report properties and set the **Data Sources** entry to your shared
   SCCM data source.
4. Save and run the report.

## 9. Testing report parameters

Run with `CollectionID = SMS00001` (All Systems) first, then a smaller
collection. Confirm `IncludeSuperseded` and `IncludeExpired` default to False and
that the trend parameters `StartDate` / `EndDate` cover at least 12 months.

## 10. Troubleshooting SQL permissions

| Symptom | Cause | Fix |
| --- | --- | --- |
| `The SELECT permission was denied` | Missing GRANT on a view | Re-run the grants in section 3 |
| `Login failed for user` | Wrong auth mode | Check `SCCM_SQL_AUTH_MODE` |
| Empty datasets, no error | Account can log in but sees no rows | Confirm you queried the correct site database |

## 11. Troubleshooting missing SCCM views

Older or lab sites may lack `v_CH_ClientSummary` or
`v_DeploymentSummary`. Schema Validation lists supported alternatives
(for example `v_ClientCollectionMembers` in place of
`v_FullCollectionMembership`). Never point queries at base tables.

## 12. Troubleshooting RDL compatibility

- "The report definition is not valid" - re-download choosing your exact SSRS version.
- Charts render blank - confirm the shared data source is assigned after import.
- Parameters not prompted - check that `dsCollections` returns rows for the account.

## 13. Configuring scheduled dashboard refresh

Set a refresh interval in **Administration**. The connector pulls summarized data
on that schedule and writes snapshots into the *application* database only.
Never schedule writes against the SCCM database.

## 14. Securing the on-premises connector

- Run as a low-privilege service account, outbound HTTPS only.
- Pin the hosted API certificate and rotate `SCCM_CONNECTOR_TOKEN` quarterly.
- Enforce a SQL statement allow-list; reject any non-SELECT statement.
- Enable query timeouts and row limits (defaults: 30s, 50,000 rows).
- Log every executed query hash to the Audit Log.

## 15. Backing up application settings

Use **Administration > Export configuration** to download a JSON template.
Secrets are exported as masked placeholders and must be re-entered on restore.

---
(c) Verentra Technology. Microsoft, SCCM, MECM, SSRS and Power BI are trademarks
of Microsoft Corporation. This product is not affiliated with or endorsed by Microsoft.