Initialize-PimSqlAdminGroup
Make the SQL admin group the Entra admin of the PIM Manager database server
Purpose
Make a role-assignable security group (default grp-pim-sql-admins) the Microsoft Entra admin of a PIM Manager environment's Azure SQL server, with the environment's managed identities and the nightly updater as its members. The Invardia Support app is never added.
When to run it
Once per environment when the install could not set up the SQL admin group itself (the installing identity did not hold the rights to create a role-assignable group), or when PIM Manager or the updater reports that its identity cannot log in to the database. Invardia support's database access is its read-only user (Grant-PimSupportAccess.ps1), not this group. Safe to run again: a second run reports no changes.
Who may run it
| Kind | Role | Scope | Why |
|---|---|---|---|
| entraRole | Privileged Role Administrator | tenant | The group is role-assignable (tier 0): only a Privileged Role Administrator or a Global Administrator can create such a group or change its members. Without it the group is not created and the current SQL admin is left as it is. |
| azureRbac | Contributor | the Azure SQL server (or its resource group); Owner works too | Sets the server's Microsoft Entra admin (Microsoft.Sql/servers/administrators/write) and reads it back. |
| azureRbac | Reader | the environment's resource group | Finds the environment's user-assigned identities (id-pim-*) and the updater job's identity. |
| other | The person running it, signed in in the browser (or the Invardia Support app inside an Invardia support session) | the subscription named with -SubscriptionId | The script takes its Microsoft Graph and Azure Resource Manager tokens from that sign-in (pinned to -TenantId) and refuses a token for another tenant. A certificate identity (-ClientId + -CertThumbprint) is for automation from the repository only; it then needs the Graph application permissions Group.Create and GroupMember.ReadWrite.All (or Group.ReadWrite.All), Directory.Read.All and RoleManagement.ReadWrite.Directory. |
What it changes
| Where | Action | What | Permission / role | Scope | Why | Undo |
|---|---|---|---|---|---|---|
| Microsoft Graph | create | security group grp-pim-sql-admins (or -GroupName), role-assignable (isAssignableToRole = true), only when it does not exist | tenant | One group as the SQL admin means every identity that must administer the store gets access by membership, without a database login created by hand. Role-assignable so that a Groups Administrator, a User Administrator or a group owner cannot add themselves to it. | Delete the group in the Entra admin center (Groups) -- only after another Microsoft Entra admin has been set on the SQL server, or nobody can administer the database. | |
| Microsoft Graph | grant | member of grp-pim-sql-admins: the environment identity (id-pim-<token>), the SQL identity (id-pim-sql-<token>, private SQL only), the nightly updater's identity (ca-pim-update), a build's own application (-SetupIdentityAppId) for the length of that build, every -ExtraMemberObjectIds, and the previous SQL admin (kept, so nobody loses access). Never the Invardia Support app: named with -SupportAppId / -SupportObjectId it is not added, and the run says so. | group member of the SQL server's Microsoft Entra admin group = administrator of the SQL server and every database on it | the environment's Azure SQL server | The updater checks the database schema before it rolls a release, and a setup or build administers the store while it runs. | Remove the member from the group (Entra admin center > Groups > grp-pim-sql-admins > Members). Removing the PIM identities stops PIM Manager. |
| Microsoft Graph | remove | with -ReleaseOwnMembership: the application this run signs in as (-ClientId with -CertThumbprint, or the Invardia Support session) leaves grp-pim-sql-admins; read back. No other member is touched, and nothing else is changed. | the environment's Azure SQL server | A build's identity is a member for the length of the build only. | Run the script again with -SetupIdentityAppId <that application id>. | |
| Azure Resource Manager | update | Microsoft Entra admin of the Azure SQL server (Microsoft.Sql/servers/administrators/ActiveDirectory) set to the group | the Azure SQL server named with -SqlServerName | Azure SQL allows exactly one Microsoft Entra admin; making it the group gives every member access. Entra-only authentication is not changed (only read before and after). Existing contained database users are not touched. | Set the previous admin back (the script prints it): Azure portal > the SQL server > Microsoft Entra ID > Set admin. |
What it only reads
- the group (by name or id), whether it is a security group and role-assignable, and its members
- the SQL server's current Microsoft Entra admin and its Entra-only authentication setting
- the user-assigned identities in the resource group and the updater job's identity
- the service principal of -SetupIdentityAppId, or with -ReleaseOwnMembership of the application this run signs in as (by application id)
Parameters
| Parameter | Required | Default | What it does |
|---|---|---|---|
| -SubscriptionId | yes | The subscription of the environment (always named explicitly; never a default context). | |
| -ResourceGroup | yes | The environment's resource group (where its identities and the updater job are). | |
| -SqlServerName | yes | The Azure SQL server name or FQDN. | |
| -SqlResourceGroup | no | the -ResourceGroup | The resource group of the SQL server when it is not the environment's. |
| -TenantId | no | the subscription's tenant | The tenant id; every token is checked against it. |
| -GroupName | no | grp-pim-sql-admins | The SQL admin group's name. |
| -GroupObjectId | no | Use this existing group (object id) instead of looking it up by name. | |
| -EnvironmentIdentityName | no | the one id-pim-* identity in the resource group | The environment's user-assigned identity, when there is more than one candidate. |
| -SqlAdminIdentityName | no | the one id-pim-sql-* identity in the resource group | The in-network SQL identity (private SQL). |
| -UpdateJobName | no | ca-pim-update | The nightly updater job whose system-assigned identity becomes a member. |
| -SupportAppId | no | The Invardia Support app's application id. It is not added as a member (no standing database administrator); the run says so. The old name -TroubleshootingAppId still works. | |
| -SupportObjectId | no | The Invardia Support app's service principal object id, instead of -SupportAppId (also not added). The old name -TroubleshootingObjectId still works. | |
| -SetupIdentityAppId | no | The application id a build runs as: a member for the length of that build. | |
| -ReleaseOwnMembership | no | off | Take the application this run signs in as out of the group and read it back; nothing else is changed. |
| -ExtraMemberObjectIds | no | More object ids to make members. | |
| -ClientId | no | Automation from the repository only: with -CertThumbprint, a certificate identity instead of the browser sign-in. | |
| -CertThumbprint | no | Automation from the repository only: the certificate (local store) of -ClientId. | |
| -MembersOnly | no | off | Only add the members, and only when the group already is the server's admin; never create the group or move the admin. |
| -DoNotKeepCurrentAdmin | no | off | Do not make the current SQL admin a member of the group. |
| -PlanOnly | no | off | Read everything and print the plan; change nothing (as -WhatIf). |
Preview with -WhatIf
Signs in and reads everything, prints the plan, then one "What if:" line per change it would make: create the role-assignable group, add each member (by name and object id), make the group the SQL server's Microsoft Entra admin (and who the admin was). Nothing is created or changed.
Undo
Set the previous Microsoft Entra admin back on the SQL server, then remove the members the script added (or delete the group if the script created it). Nothing else is changed by the script.
Network
It connects to: graph.microsoft.com, management.azure.com, login.microsoftonline.com (browser sign-in), 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/Initialize-PimSqlAdminGroup.ps1 -OutFile Initialize-PimSqlAdminGroup.ps1
.\Initialize-PimSqlAdminGroup.ps1 -SubscriptionId <SubscriptionId> -ResourceGroup <ResourceGroup> -SqlServerName <SqlServerName>To check the download first, compare its SHA-256 with the published one:
SHA-256 440410cb3be56bc5e61151ddba7988a1db7fab917fe5b3ec8499cc84a1557ba7
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 .\Initialize-PimSqlAdminGroup.ps1 -Full · Source: Initialize-PimSqlAdminGroup.ps1
Every script, identity and permission for your security review: PIM Manager