Set-PimSigninFrontSqlState
Give the sign-in front its place in a private PIM database
Purpose
Gives the PIM Manager's sign-in front exactly what it needs in the PIM database: a database user for the front's managed identity, the schema 'signin' owned by it, and the right to create tables there. It never adds a role. -WhatIf shows it first; running it again changes nothing.
When to run it
Once, on a PRIVATE installation (the SQL server has no public endpoint) that is moved onto the sign-in front, when the move or support says the front's database place is missing. Run it from a machine inside the PIM network. Public installations get this from the installation by themselves.
Who may run it
| Kind | Role | Scope | Why |
|---|---|---|---|
| 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 | Creates the database user and the schema and grants CREATE TABLE. |
| other | browser sign-in (your own account; an Azure Arc / VM machine identity on this computer is ignored), or the Invardia Support app's session in this window | the target tenant and subscription | No Azure CLI, no PowerShell module, no certificate, no secret on the command line. |
What it changes
| Where | Action | What | Permission / role | Scope | Why | Undo |
|---|---|---|---|---|---|---|
| The PIM database | create | a database user for the sign-in front's managed identity, created from its client id (CREATE USER ... WITH SID, TYPE = E) when no user has that id | the one PIM database (-SqlDatabase, default PimPlatform) | The front signs in to the database as its managed identity. | DROP USER [<identity name>] in the PIM database (after DROP SCHEMA signin and its tables). | |
| The PIM database | create | the schema 'signin', owned by that user (or its owner moved to that user) | owner of the schema signin only | the schema signin | The front keeps its sessions, token cache and key ring in two tables it creates in this schema; it can read and change nothing else. | Drop the tables in schema signin, then DROP SCHEMA signin. |
| The PIM database | grant | CREATE TABLE to that user | CREATE TABLE (database level; usable only in the schema it owns) | the PIM database | The front creates its two tables itself on first start. | REVOKE CREATE TABLE FROM [<identity name>]. |
What it only reads
- the database user with the identity's client id, the owner of schema signin and the CREATE TABLE grant, before and after the change
Parameters
| Parameter | Required | Default | What it does |
|---|---|---|---|
| -TenantId | yes | The tenant id. The sign-in is pinned to it. | |
| -SubscriptionId | yes | The subscription 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. |
| -IdentityName | yes | The sign-in front's managed identity name (the database user's name). | |
| -IdentityClientId | yes | The sign-in front's managed identity client id (the database user's SID). |
Preview with -WhatIf
Signs in, connects, prints the user, the schema owner and the grant as they are now, and the statements it would run. Nothing is changed.
Undo
In the PIM database: drop the tables in schema signin, DROP SCHEMA signin, REVOKE CREATE TABLE and DROP USER for the front's identity. The front then cannot keep sign-ins.
Network
It connects to: login.microsoftonline.com, management.azure.com, <server>.database.windows.net:1433 (the PIM SQL server, from inside the PIM network), 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/Set-PimSigninFrontSqlState.ps1 -OutFile Set-PimSigninFrontSqlState.ps1
.\Set-PimSigninFrontSqlState.ps1 -TenantId <TenantId> -SubscriptionId <SubscriptionId> -SqlServerName <SqlServerName> -IdentityName <IdentityName> -IdentityClientId <IdentityClientId>To check the download first, compare its SHA-256 with the published one:
SHA-256 89f6f395eb7636babcb480286617a1614f19b7bd8ef1598c9878fadb0ae89660
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 .\Set-PimSigninFrontSqlState.ps1 -Full · Source: Set-PimSigninFrontSqlState.ps1
Every script, identity and permission for your security review: PIM Manager