CREATE MASTER KEY#
Establishes the instance master key — the encryption root Querona protects its own stored secrets with. The
statement is idempotent: on an instance that already has a master key it succeeds and changes nothing, so
scripts that open with CREATE MASTER KEY run unchanged.
This is a deliberate divergence from SQL Server in two respects. The key is instance-wide, not one key per database, because it roots secrets that belong to the instance rather than to a database. And it is not a prerequisite for CREATE DATABASE SCOPED CREDENTIAL: a credential secret is protected by the engine’s secret store whether or not the statement has ever been run. Querona also creates the master key by itself when the engine first starts, so on most installations the statement has nothing left to do.
ALTER MASTER KEY, OPEN/CLOSE MASTER KEY and BACKUP MASTER KEY are not supported.
Syntax#
Arguments#
ENCRYPTION BY PASSWORD = ‘password‘
Adds a password protector to the master key, so the key can be recovered on a machine other than the one that created it. The password itself is never stored: it derives a key that wraps the master key, and every place the statement text is rendered - formatted SQL, the log, the trace, the query store and the audit log - shows the password masked.
Once the key has a password protector, the statement does not change it. The password has to open the existing protector, and the statement then succeeds without changing anything, which makes it a check that the recovery password you keep is still the right one; any other password is refused. Changing the password is not supported yet.
A password that is about to protect the key must meet SQL Server’s password policy as SQL Server applies it on
Linux: at least eight characters, from at least three of these four sets - uppercase letters, lowercase letters,
digits and symbols. Letters with diacritics and letters of other scripts count by their case, but only ASCII
punctuation, such as !, # or _, counts as a symbol; a space does not. A shorter password is refused with
error 33062, and one drawn from fewer sets with error 33064. The policy does not apply to a password that opens an
existing protector.
Omit the clause (the Azure SQL Database syntax) to establish the key without a password protector. On Windows the engine protects the key with the operating system’s Data Protection API on each node, which is what lets it start unattended; that protection is machine-bound, so a key with no password protector cannot be recovered if the machine is lost. The engine reports a warning at startup while the master key has no password protector.
Remarks#
The statement runs on the node that receives it, and what the password form does there depends on whether that node can already open the master key.
On a node that holds the key, the password form adds the key’s password protector or, once it has one, checks the password against it. On a node that does not hold it, such as a node joined to the cluster later, the same statement with the recovery password opens the stored password-protected copy, gives the node the key, and lets the node’s own machine-bound protection take a copy, so the password is not needed there again. A password that does not open the stored copy is refused and nothing changes; so is the statement on a node where no password protector has ever been added.
The bare form is refused on a node that cannot open the master key, rather than reporting a success that would suggest the key is usable there.
If the node cannot read or write the metabase, the statement is refused with a message that points at the node’s metabase connection; the master key itself is not affected.
Every execution of the statement is recorded in the audit log, refused attempts included, with the password masked.
Permissions#
Requires the server-control permission (ControlServer) on the server. The statement decides which nodes and
which passphrases can open the encryption root of every secret the instance stores, so it takes the instance-wide
control permission rather than the connection-management permission the credential statements take.
Upgrade note: earlier releases accepted CREATE MASTER KEY from any login with the connection-management
permission (CreateConnection). A script that runs it under such a login now fails with a permission error. Run
the script as a login with ControlServer, or remove the statement: the engine creates the master key by itself.
Examples#
Establish the master key, without a password protector:
CREATE MASTER KEY;
Give the master key a password protector so it can be recovered elsewhere:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Str0ng!Passphrase';
Check that a password still opens the key’s password protector. The statement succeeds and changes nothing when it does, and is refused when it does not:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Str0ng!Passphrase';