Detent PointDomain: Always Encrypted · Risk: Changes state · Runs on: Windows, PowerShell 5.1 or 7
Always Encrypted needs a record in the database that says which certificate protects the encrypted columns. This command writes that record. It points the database at a certificate that already exists in a Windows certificate store; it does not create the certificate, and it does not encrypt any data.
Registering the same key a second time changes nothing — the command notices it is already there and reports that it skipped. You can preview the change with -WhatIf before running it for real.
Runs CREATE COLUMN MASTER KEY through the module’s write seam, naming the key-store provider and the key path. The default provider is MSSQL_CERTIFICATE_STORE, whose key path is Location/Store/Thumbprint. Idempotent — a column master key of that name already in the database returns Skipped. Supports -WhatIf. Never throws; returns a SqlCert.Result.
It does not create column encryption keys — that is client-side RSA_OAEP wrapping, done by the SqlServer module’s New-SqlColumnEncryptionKey — and it does not encrypt data.
Recipe 01 — Register a certificate-store column master key
Register-SqlColumnMasterKey -SqlInstance sql01 -Database Sales -Name 'CMK_Sales' -KeyPath 'CurrentUser/My/A1B2C3...'The key path for MSSQL_CERTIFICATE_STORE is Location/Store/Thumbprint — e.g. LocalMachine/My/<thumbprint> or CurrentUser/My/<thumbprint>.
Recipe 02 — Preview without registering
Register-SqlColumnMasterKey -SqlInstance sql01 -Database Sales -Name 'CMK_Sales' -KeyPath 'CurrentUser/My/A1B2C3...' -WhatIfWatchpoint — this is metadata, not encryption. Registering the column master key does not encrypt any column. Create the column encryption keys under it with the SqlServer module (New-SqlColumnEncryptionKey), then encrypt columns with that module.
Always Encrypted uses two layers of keys. The column master key is the outer one; it lives in a key store — here, a Windows certificate store — and the database only holds a pointer to it. This command writes that pointer: the provider name and the path to the certificate.
It is deliberately narrow. It does not make the certificate, it does not make the inner keys (the column encryption keys), and it does not encrypt anything. Those steps belong to the SqlServer module and the client side. This command records, in the database, which certificate the encryption is anchored to.
| Question | Answer |
|---|---|
| Outbound calls? | Connects to the SQL instance you name to run the metadata statement (through the dbatools/SqlServer provider). No other outbound calls. |
| What it changes | Adds one column master key definition (provider + key path) to the named database. |
| Privileges | A login permitted to create a column master key in the database (ALTER ANY COLUMN MASTER KEY). |
| Reversibility | Reversible — drop the column master key once nothing depends on it. |
| Dependency | Why | Required? |
|---|---|---|
| Windows + PowerShell 5.1 or 7 | Runs the command and the SQL connection. | Yes |
| Connectivity to the SQL instance | The metadata statement runs on that instance. | Yes |
| The certificate present in the referenced key store | Always Encrypted resolves the key path to that certificate when the key is used. | To use the key |
- Idempotent — re-running with the same name reports Skipped and writes nothing, so it is safe in a repeatable deployment.
-WhatIfpreviews the exact registration before any change is made.- The result records the name, database, provider, and key path that were registered — a record of which certificate the encryption is anchored to.
- Scope is limited to metadata: it never generates a certificate, creates column encryption keys, or encrypts data, so its audit surface is a single
CREATE COLUMN MASTER KEYstatement.