Detent PointDomain: Cell-level encryption · Risk: Changes state · Runs on: Windows, PowerShell 5.1 or 7
Cell-level (hand-rolled) column encryption needs a small set of keys inside the database before any data can be protected: a certificate, and a symmetric key that the certificate protects. This command creates them. It can also grant specific database users the right to use them, and nothing broader.
It creates only what is missing, so running it again is safe — if the certificate and symmetric key are already there it reports that it skipped. One thing it will not do on its own is create the database master key, because that key is recovery-critical; if it is absent the command stops and tells you, unless you explicitly supply a password for it. You can preview everything with -WhatIf.
Ensures the hierarchy in a user database: (optionally) the database master key, then CREATE CERTIFICATE, then CREATE SYMMETRIC KEY ... ENCRYPTION BY CERTIFICATE, and optionally GRANT VIEW DEFINITION on both to -GrantTo principals. Each object is created only if absent. If the certificate and symmetric key both already exist (and no -GrantTo is given) it returns Skipped. If the database master key is absent and no -MasterKeyPassword is supplied it returns Pending with guidance rather than creating that key implicitly. Supports -WhatIf; never throws; returns a SqlCert.Result. The -MasterKeyPassword SecureString is never logged, echoed, or placed in the result.
Default algorithm is AES_256 (AES_192, AES_128, and TRIPLE_DES_3KEY are also accepted). It provisions the keys only — it does not encrypt or decrypt column values.
Recipe 01 — Provision a cell certificate and key, and grant a role
New-SqlCellCertificateKey -SqlInstance sql01 -Database Sales -CertificateName 'Cell_Cert' -SymmetricKeyName 'Cell_Key' -GrantTo 'app_role'Creates the AES_256 symmetric key under the certificate and grants app_role VIEW DEFINITION on both.
Recipe 02 — Preview, creating the database master key as part of the call
$mk = Read-Host -AsSecureString 'DMK password'
New-SqlCellCertificateKey -SqlInstance sql01 -Database Sales -CertificateName 'Cell_Cert' -SymmetricKeyName 'Cell_Key' -MasterKeyPassword $mk -WhatIf-WhatIf shows what would be created without changing anything; -MasterKeyPassword opts into creating the database master key when it is absent.
Watchpoint — back up the certificate before it protects data. A cell certificate that is lost takes the columns it protects with it. Once this succeeds, back the certificate up and store the backup separately.
Cell-level encryption in SQL Server is a chain of keys. The database master key protects certificates; a certificate protects a symmetric key; the symmetric key is what actually encrypts a column’s values. This command builds the middle of that chain — the certificate and the symmetric key — inside one database.
Two design choices are worth understanding. First, it is idempotent: it checks what already exists and creates only the rest, so re-running it does not duplicate or overwrite keys. Second, it treats the database master key with caution: creating that key has recovery consequences, so the command refuses to create it silently and asks you to pass a password if you want it made. And because the certificate is the root of what you just built, backing it up is the step that keeps the encrypted data recoverable.
| Question | Answer |
|---|---|
| Outbound calls? | Connects to the SQL instance you name to run the statements (through the dbatools/SqlServer provider). No other outbound calls. |
| What it changes | Creates, in the named database, whatever is missing of: the database master key (only with -MasterKeyPassword), a certificate, and a symmetric key encrypted by it; optionally grants VIEW DEFINITION on the certificate and key to -GrantTo principals. |
| Privileges | Permission to create a certificate and symmetric key in the database, and to grant on them. |
| Reversibility | The created objects can be dropped, but dropping a certificate or key that already protects data makes that data unrecoverable — back the certificate up first. |
| Dependency | Why | Required? |
|---|---|---|
| Windows + PowerShell 5.1 or 7 | Runs the statements against the instance. | Yes |
| Connectivity to the SQL instance | The objects are created on that instance. | Yes |
A database master key, or -MasterKeyPassword to create one |
The certificate is protected by the database master key. | Yes |
-GrantTo principals that exist in the database |
GRANT targets must be real database principals. | Only if granting |
- Idempotent — creates only what is absent and reports Skipped when the certificate and key already exist, so it is safe in a repeatable deployment.
- Will not create the database master key implicitly; it returns Pending unless
-MasterKeyPasswordis supplied, keeping a recovery-critical key an explicit decision. - The database master key password is a SecureString and is never logged, echoed, or included in the result.
- Grants are least-privilege (
VIEW DEFINITION) and only to the principals named in-GrantTo; the result’sActionslist records what was created and granted. -WhatIfpreviews the full set of statements before any change is made.