Invoke-PimSupportSqlAccessJob
Give the Invardia Support app its read-only database user on an installation with a private database, from inside
Purpose
Gives the Invardia Support app its read-only database user on a PIM Manager installation whose database is private (public network access off), from inside: a one-time job in the installation's own environment runs the support access step (the user from the application's client id, in the read role pim_support_read only), the result is read back, and the job is removed. Nothing is opened; -WhatIf shows it first. With -SupportHours the database owner role is given for that many hours only (never above the level you consented to): the job stays, takes the role back at the end by itself, and the read-only access stays.
When to run it
Once, on an installation whose SQL server has public network access off, when you have an Invardia Support app and want Invardia support to be able to read the database and, with consent 'setup', to open time-limited support sessions. It is the route for a private database: Grant-PimSupportSqlAccess.ps1 stops there, because the machine it runs on cannot reach the database. You run it yourself; Invardia support does not run it for you. Safe to run again: the step is idempotent and keeps the consent level already recorded unless -ConsentLevel names another.
Who may run it
| Kind | Role | Scope | Why |
|---|---|---|---|
| azureRbac | Contributor (or Owner) | the resource group of the PIM Manager installation | Reads the installation's database setup job and its environment; creates, starts and deletes the one-time job; and assigns to that job the user-assigned identities the setup job already carries (the assign action on those identities is part of Contributor). |
| azureRbac | Log Analytics Reader | the Log Analytics workspace of the installation's Container Apps environment (optional) | Reads the job's log lines. Without it the verdict still comes from how the execution ended; the script then says the log could not be read. |
| other | Your own browser sign-in, or the Invardia Support app's session in this window | the target tenant and subscription | A session of another tenant than -TenantId, or of another application than -SupportAppId, is refused. No Azure CLI, no PowerShell modules, no secret on the command line. |
What it changes
| Where | Action | What | Permission / role | Scope | Why | Undo |
|---|---|---|---|---|---|---|
| The PIM database | grant | a database user for the Invardia Support app, created by the one-time job from the application's client id (CREATE USER ... WITH SID, never FROM EXTERNAL PROVIDER) when no user has that id; an existing user of the Support app is reused whatever its name; made by the support access step with the consent level (-ConsentLevel troubleshoot or setup, else the level already recorded, else troubleshoot) | pim_support_read (read-only: SELECT on the product schemas) | the one PIM database the installation's database setup job names (PIM_SqlDatabase, default PimPlatform) | Invardia support reads the store to troubleshoot. This is the Support app's only standing database right. The job never adds db_owner; one an earlier version gave is shown as a warning and left as found. | Run Grant-PimSupportAccess.ps1 -Remove from inside the network (drops the user), or switch the Invardia Support app off in Microsoft Entra ID, which ends the access at once. |
| The PIM database | grant | with -SupportHours: the level asked (-Level setup or troubleshoot), but never above the level the customer consented to (the lower of the two); for the database owner role the end is recorded with the role (one record per role added). At the end the job takes the database owner role back by itself, then ends the Support app's open database sessions, and ends; the read-only access stays. A role the Support app held before is never taken away. Every run of the job, and the background jobs of PIM Manager, take back any time-limited access whose end has passed | db_owner (database owner) until the end, at most 8 hours; pim_support_read (read-only: SELECT on the product schemas) stays | the one PIM database the installation's database setup job names (PIM_SqlDatabase, default PimPlatform) | Support gets the owner right only while it works on the installation; the access ends at the stated time without anyone having to remember it. | None needed: the job takes the owner role back at the end. To end it sooner: -CloseSupportAccess, or switch the Invardia Support app off in Microsoft Entra ID, which ends the access at once. |
| The PIM database | create | with -InstallSupportAccess -ConsentLevel: once per installation, the consent level the customer gives (none, troubleshoot or setup) for this Support app, in a table only a database owner can change; the read role pim_support_read (SELECT on the product schemas only) and the Support app's database user in it (an earlier membership of db_datareader is taken away once it is in that role: narrower, never wider) -- with none, no Support database user at all; three procedures that open, close and report the time-limited access and take no user as a parameter, signed by a certificate created in the database (its private key removed right after signing), with changes to them refused to everyone but the database owner | the one PIM database the installation's database setup job names | So the time-limited access can be opened and closed by PIM Manager's own jobs without them being database owners, and only ever for the Support app at the level the customer chose. | Run -InstallSupportAccess again with -ConsentLevel none: every open access is closed and the Support app's database user is removed. | |
| The PIM database | grant | with -InstallSupportAccess: the right to run the procedures that close the time-limited access and report it, for PIM Manager's engine and web app (so their regular jobs end an access that has passed its end), and the right to read the state for the updater; only the database job may run the one that opens it | EXECUTE on those procedures only | the three support access procedures in the PIM database | PIM Manager's own jobs close the time-limited access on their own schedule without being database owners. | Run -InstallSupportAccess again with -ConsentLevel none: nothing can be opened any more. |
| The PIM database | update | with -CloseSupportAccess: every open time-limited access is ended now (only the roles it added), then the Support app's open database sessions | the one PIM database the installation's database setup job names | Support is finished before the end that was set. | Open it again with -SupportHours. | |
| Azure (the PIM resource group) | create | the one-time Container Apps job (default name caj-pim-supportsql, tag pim-one-time-job = support-sql-access) in the installation's own Container Apps environment, on the image of the database setup job, with the user-assigned identities that job already carries (the SQL admin identity and, when it differs, the image-pull identity). No role, no group membership and no permission is given to any identity. | the resource group of the installation | A private database has no route from outside; the job runs where the route already is. No firewall rule, no public access and no network path is opened. | None needed: the run removes the job and reads back that it is gone, also after a failed step. If the run is killed: Azure portal > the resource group > caj-pim-supportsql > Delete, or run the script again (it removes a leftover that carries its tag first). | |
| Azure (the PIM resource group) | remove | the one-time job this run created -- or a leftover of the same name that carries this script's tag. A job of that name without the tag is never touched. | the resource group of the installation | The job is for one run. It carries the SQL admin identity, so it is not left behind. | None needed: the installation does not use this job. |
What it only reads
- the subscription (visible, and in -TenantId)
- the installation's database setup job (ca-pim-dbinit, else caj-pim-dbinit): environment, image, registry, identities, PIM_SqlServer, PIM_SqlDatabase, PIM_DBINIT_MI_CLIENT_ID
- whether a job of the one-time name is already there, and its tags
- the Invardia Support app's service principal (Microsoft Graph): does the application exist in this tenant
- the one-time job's provisioning state and its execution's status
- the Container Apps environment (its Log Analytics workspace id) and the execution's console lines in that workspace
- inside the job: sys.database_principals and sys.database_role_members in the PIM database (is there a user with the Support app's id, is it in pim_support_read, and did an earlier version put it in db_owner or db_datareader)
- inside the job: the consent level and the open time-limited support access, through the signed procedures
Parameters
| Parameter | Required | Default | What it does |
|---|---|---|---|
| -TenantId | yes | The tenant id. Every sign-in is pinned to it. | |
| -SubscriptionId | yes | The subscription of the PIM Manager installation. | |
| -ResourceGroup | yes | The resource group of the PIM Manager installation (its Container Apps environment and jobs). | |
| -SupportAppId | no | the application of the Support session in this window | The Invardia Support app's application (client) id. Required with a browser sign-in. In the Support app's own session it may be left out; a different id is refused. |
| -DbUserName | no | invardia-support | The name for the database user when one has to be created. An existing user with the Support app's id keeps its name. |
| -TemplateJobName | no | ca-pim-dbinit | The installation's own job the identity, the image, the environment and the database are read from. It is only read. When it is not given, ca-pim-dbinit is read, else caj-pim-dbinit (an installation with the new names): the first of the two that exists. |
| -JobName | no | caj-pim-supportsql | The name of the one-time job (created, run once, removed). |
| -SqlIdentityClientId | no | the identity the template job names (its setting PIM_DBINIT_MI_CLIENT_ID) | Only when the template job does not name its database identity itself: the client id of a user-assigned identity that job already carries. An identity the job does not carry is refused. |
| -TimeoutSeconds | no | 600 | How long to wait for the one-time job to end (with -SupportHours: for its result). |
| -SupportHours | no | 0 | 1 to 8: the level asked (-Level) for that many hours only, never above the consented level; the job stays, takes the database owner role back at the end by itself and ends, and the read-only access stays. 0: the read-only user only. Needs -InstallSupportAccess once. |
| -Reason | no | With -SupportHours: why the access is opened. Recorded with the end and in the audit records. | |
| -TicketNumber | no | With -SupportHours: the support ticket the access belongs to. Recorded with the end and in the audit records. | |
| -Level | no | setup | With -SupportHours: the level asked, setup or troubleshoot (owner / read are accepted as setup / troubleshoot). The level given is never above the level the customer consented to. |
| -InstallSupportAccess | no | off | Once per installation: record the consent level (-ConsentLevel) and install the time-limited support access. Needs the installation's database setup job (an owner-level identity). |
| -ConsentLevel | no | With -InstallSupportAccess: the level the customer consents to -- none, troubleshoot or setup (read / owner are accepted as troubleshoot / setup). Without a step switch (the read-only user): troubleshoot or setup; left out, the level already recorded, else troubleshoot. | |
| -CloseSupportAccess | no | off | End every open time-limited support access now (only the roles it added); the read-only access stays. |
Preview with -WhatIf
Signs in, reads the database setup job, whether a job of the one-time name exists and that the Support app exists in the tenant, prints the database, the identity and the image it would use, and one 'What if:' line for each thing it would do: create the one-time job, start it once (take back any time-limited access whose end has passed; the read-only user from the client id in pim_support_read only -- with -SupportHours, db_owner until the end), read the result, remove the job (with -SupportHours: leave it running until it has taken the access back). It also prints the whole script the job would run. No job is created, nothing reaches the database. Nothing is changed.
Undo
Run Grant-PimSupportAccess.ps1 -Remove from inside the network (drops the database user), or switch the Invardia Support app off in Microsoft Entra ID. The one-time job is removed by the run itself. With -SupportHours the owner role ends at the stated time by itself.
Network
It connects to: login.microsoftonline.com (sign-in), management.azure.com (Azure Resource Manager), graph.microsoft.com, api.loganalytics.io (the job's log lines), localhost (the browser returns the sign-in to a local port on this computer), from this computer: nothing to the SQL server. The database is reached only by the job, inside the installation's network, over its private endpoint
Download and run
Run these lines in PowerShell: the first downloads the script into the current folder, the second runs it.
Invoke-WebRequest https://invardia.com/support/pim/Invoke-PimSupportSqlAccessJob.ps1 -OutFile Invoke-PimSupportSqlAccessJob.ps1
.\Invoke-PimSupportSqlAccessJob.ps1 -TenantId <TenantId> -SubscriptionId <SubscriptionId> -ResourceGroup <ResourceGroup>To check the download first, compare its SHA-256 with the published one:
SHA-256 d1b58b76afa6869d7f202705883652fb444589eeca4f9892b32c4542c7920765
Checksums: SHA256SUMS.txt · Version 3.0.70
Before you run it, compare the file's SHA-256 with its line in SHA256SUMS.txt.
Every parameter: Get-Help .\Invoke-PimSupportSqlAccessJob.ps1 -Full · Source: Invoke-PimSupportSqlAccessJob.ps1
Every script, identity and permission for your security review: PIM Manager