Grant-PimSupportSqlAccess
Give the Invardia Support app its read-only database access on an existing installation
Purpose
Gives the Invardia Support app its read-only access to the database of an existing PIM Manager installation: its own database user (created from the application's client id) in the read role pim_support_read, and the support access step that gives admin rights only inside an open support session. Run once per installation; -WhatIf shows it first.
When to run it
Once, on an installation made before PIM Manager installs gave the Invardia Support app this access by themselves, 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. Also when the end-of-install check shows the line 'Invardia support: database access' as a warning. New installations do this by themselves when a Support app is named. For a private installation (the SQL server has no public endpoint), run it from a machine inside the PIM network.
Who may run it
| Kind | Role | Scope | Why |
|---|---|---|---|
| azureRbac | SQL Server Contributor (or Contributor / Owner) | the PIM SQL server (or its resource group) | Reads the server, its Microsoft Entra administrator and its firewall rules; opens and removes the one firewall rule 'AllowSetupHost'; and, only in the Support app's own session when the database refuses the sign-in, changes the server's administrator for the step and puts it back. |
| other | A database owner: a member of the SQL admin group (grp-pim-sql-admins) or the server's Microsoft Entra administrator | the PIM database | Installs the support access step: the read role, the consent level, the Support app's database user in the read role. In the Invardia Support app's own session this is not needed: the Support app is made the server's administrator for the step. |
| other | The Invardia Support app's session in this window, or your own browser sign-in | 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 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 | pim_support_read (SELECT on the product schemas only) | the one PIM database (-SqlDatabase, default PimPlatform) | Invardia support reads the store to troubleshoot. This is the Support app's only standing database right; with consent 'setup' a support session adds the database owner role until its end (at most 8 hours) and takes it back by itself. The run never adds the database owner role or a SQL admin group membership; one an earlier version gave is shown as a warning and left as found. | Run Grant-PimSupportAccess.ps1 -Remove (drops the user), or switch the Invardia Support app off in Microsoft Entra ID, which ends the access at once. |
| The PIM database | create | the support access step: the read role pim_support_read, the consent level (-ConsentLevel, else the level already recorded, else troubleshoot) and the signed procedures that open and close a support session (their signer certificate is a db_owner member; EXECUTE on the close and the state read for the product's own pim_engine, pim_web and pim_updater roles) | the one PIM database | The same step a new installation takes when a Support app is named. A support session with consent 'setup' adds db_owner to the Support app's user until its end and takes it back by itself. | Run Grant-PimSupportAccess.ps1 -Remove; the consent level is changed with -ConsentLevel. | |
| Azure SQL server | create | the firewall rule 'AllowSetupHost' for this machine's public address, only when it is not there yet; removed again at the end of the run and read back, also after a failed step | the PIM SQL server | The database steps connect from this machine. | None needed: the run removes it. If the run is killed: Azure portal > the SQL server > Networking > Firewall rules: delete 'AllowSetupHost'. | |
| Azure SQL server | update | the server's Microsoft Entra administrator, set to the Invardia Support app FOR THE STEP -- only in the Support app's own session and only when the database refuses its sign-in; put back exactly as it was read (login, id, type, tenant, Entra-only setting) and read back, also after a failed step | the PIM SQL server | An application that is only a member of the administrator group has not been accepted by the database on the servers this was measured on; as the administrator it can create its own database user. | None needed: the run puts it back and proves it. Exit code 2 means it could not: the values to set by hand are printed (Azure portal > the SQL server > Microsoft Entra ID > Set admin). |
What it only reads
- the subscription (visible, and in -TenantId)
- the SQL server: its public network access, its Microsoft Entra administrator and its Entra-only setting, before and after
- the firewall rule 'AllowSetupHost', before and after
- the Invardia Support app's service principal, the SQL admin group and its members (Microsoft Graph)
- 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)
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 SQL server. | |
| -ResourceGroup | yes | The resource group of the PIM SQL server. | |
| -SqlServerName | yes | The SQL server name or its FQDN (sql-x or sql-x.database.windows.net). | |
| -SqlDatabase | no | PimPlatform | The PIM database. |
| -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. |
| -SqlAdminGroupName | no | grp-pim-sql-admins | The SQL admin group's name (read only: the run never adds the Support app to it). |
| -ConsentLevel | no | the level already recorded on this installation, else troubleshoot | troubleshoot: read-only. setup: also admin rights inside an open support session, at most 8 hours. |
| -SetupHostIp | no | read from api.ipify.org (said on screen) | This machine's public address for the temporary firewall rule. |
| -SignInWaitSeconds | no | 300 | How long to wait for the database to accept the Support app's sign-in after it became the administrator for the step. |
Preview with -WhatIf
Signs in, reads the SQL server (administrator, Entra-only setting, firewall rule), the Support app and the SQL admin group, prints 'before', and prints one 'What if:' line for each thing it would do: open and remove the firewall rule, make the Support app the administrator for the step (only in a Support session, only when refused) and put the administrator back, install the support access step with the database user in pim_support_read only. It does not connect to the database. Nothing is changed.
Undo
Run Grant-PimSupportAccess.ps1 -Remove (drops the database user), or switch the Invardia Support app off in Microsoft Entra ID. The firewall rule and the server's administrator are put back by the run itself.
Network
It connects to: login.microsoftonline.com (sign-in), management.azure.com (Azure Resource Manager), graph.microsoft.com, <server>.database.windows.net:1433, api.ipify.org (this machine's public address, unless -SetupHostIp is given), localhost (the browser returns the sign-in to a local port on this computer)
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/Grant-PimSupportSqlAccess.ps1 -OutFile Grant-PimSupportSqlAccess.ps1
.\Grant-PimSupportSqlAccess.ps1 -TenantId <TenantId> -SubscriptionId <SubscriptionId> -ResourceGroup <ResourceGroup> -SqlServerName <SqlServerName>To check the download first, compare its SHA-256 with the published one:
SHA-256 c174e1f10afca8d8d7e1d08ce0e61f361719dc3d7b4683f931103636cd29ac63
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 .\Grant-PimSupportSqlAccess.ps1 -Full · Source: Grant-PimSupportSqlAccess.ps1
Every script, identity and permission for your security review: PIM Manager