Second factor#
A standard login can sign in with a one-time code from an authenticator app in front of its password, and an administrator can require one of a login. SQL Server has no equivalent; it is a Querona feature.
Nothing changes until a login sets up an authenticator. Every existing login keeps signing in with its password alone - until an administrator requires an authenticator of it and the requirement is enforced, which is off by default (see Enforcing the requirement below).
How it works#
The login sets up the authenticator itself, from its own profile - see Setting up an authenticator app. It types its password first, so that a session left open cannot be bound to an authenticator the login does not hold, and only then is the key shown - once, to the login, as a QR code and as a key to type. An administrator cannot enrol on a login’s behalf. A set-up the login has not confirmed with a code is pending: the login keeps signing in with its password, and it can discard the set-up itself from its profile - on the set-up screen while the key is showing, or later, when the profile shows the set-up as pending - or start over, which replaces it.
The password typed to begin a set-up is checked the way a sign-in checks it: a wrong password counts as a failed sign-in toward the Failed sign-in lockout of the login and origin, and a login that is locked out is refused the step, without its password being examined, until the lockout passes.
The codes are the time-based ones every common authenticator app produces: six digits, a new code every thirty seconds. The app needs no network - only a clock close to the Querona server’s. The codes of the step before and the step after the current one are accepted as well, so a clock up to half a minute off always works, and one a minute or more off never does.
An enrolled login signs in by typing the six-digit code directly in front of its password, in the password field, with nothing between them. There is no second prompt, so it works with every SQL Client, and it is the same in the web interface’s sign-in and when a client requests an access token from the REST API with the login’s password. A code is not spent on use - it stays valid for its window, so a connection pool that reopens connections with the string the login typed keeps working while the window lasts.
A wrong code is a failed sign-in like a wrong password: the login is refused without being told which of the two was wrong, and the attempt counts toward the Failed sign-in lockout of the login and origin. The login’s trusted networks are checked before either.
Only a standard account - type Power User or End User with password authentication - can have a second factor. An account using integrated authentication (Windows or Microsoft Entra ID) holds no password in Querona to put a code in front of, and a Spark reverse account or System account is not one a person signs in with; for these, setting up and requiring an authenticator are refused on the server, however the request arrives.
Requiring an authenticator#
Go to .
Select the account and open it for editing.
Under Second factor, tick Require an authenticator.
Save the account.
This path requires the Alter any login permission - see Access rights - and covers every standard login, the administrator’s own included. The account is marked required and the moment is recorded: the edit form shows the box ticked, the login’s own profile says Required since that moment until it sets one up, and an account that has already set up an authenticator stays Authenticator enrolled. Requiring it again later does not move the recorded moment. Unticking the box lifts the requirement and clears the moment, but does not remove an authenticator the login has set up or begun to set up - only a reset does that.
Whether the requirement is enforced is a setting of the instance - see below.
The account’s details and its edit form show the state under Second factor: No authenticator, Enrolment pending (the login has started to set one up and has not confirmed a code yet - it keeps signing in with its password) or Authenticator enrolled, and the edit form shows whether one is required. For an account that is required to have an authenticator and has none yet, the state also says since when it is required and, while the requirement is enforced (see below), from which day the authenticator is required - or that the day has passed and the account is overdue: it signs in only to set the authenticator up. The days are the reader’s local calendar days. Reading another login’s state, like its trusted networks, needs the View server state or the Alter any login permission; a caller without it is shown no Second factor box at all, rather than a wrong state, and a login always sees its own.
The same state and operations are available through the REST API under api/1.0/users/{userId}/secondfactor:
GET reads the state, POST enrolment (with the current password), POST enrolment/confirm (with a code)
and DELETE enrolment are the login’s own set-up, and PUT requirement and DELETE the administrator’s.
The state carries the requirement’s recorded moment (requiredSinceUtc) and, while the requirement is
enforced, the deadline it sets (deadlineUtc) and whether it has passed (isEnrolmentOverdue); the same
members sit on the secondFactor member of the user edit model an administrator reads.
Enforcing the requirement#
By default the requirement is recorded and shown, and nothing more: a login that is required to have an authenticator and has not set one up still signs in with its password alone. Enforcement is switched on for the whole instance under Second factor settings, together with a grace period (fourteen days by default) that runs from the moment the requirement was recorded - the Required since the login’s profile shows.
With enforcement on, a required login without an authenticator keeps signing in until the grace period has passed, and is refused after it, on every path it signs in by:
a SQL Client is refused with error 18456 and the reason Multi-factor authentication is required for the account, but no authenticator has been set up yet - after the password was checked, so a wrong password still gets the plain login failure; a standard login signed in as a Windows identity of the same name is refused with the same reason;
the web interface admits the login, but only to its own profile, for the configured minutes, so it can set up its authenticator there; it is then signed out and signs in again with the code in front of its password; its Windows sign-in, which carries no password a restricted session could be issued on, refuses the login with the plain The username or password is invalid;
the Data API refuses the login with the same reason.
Before the deadline the web interface tells the login in its page header that an authenticator will be required from that date; a SQL client is told nothing until the refusal. A login that has set up an authenticator, and any account that cannot have one, are never refused on this ground. The refusal does not count toward the Failed sign-in lockout, because the password was right; it is recorded as a failed logon and audited as a failed login.
A reset records the requirement’s moment afresh, so a login whose authenticator was reset gets a whole grace period again to set up a new one. Lifting the requirement ends the enforcement for that login. What a session opened before the deadline keeps, and what the login sees on each path, is on the settings page.
Resetting an authenticator#
When a login loses the device that holds its authenticator, or has left a set-up unconfirmed that it no longer trusts, an administrator resets it: open the account for editing and click Reset authenticator, then confirm. The reset takes effect the moment it is confirmed, without saving the account, and drops the authenticator whether it was enrolled or still pending. From then on the login signs in with its password alone; a login that was required to have an authenticator is required again, with the moment recorded afresh, and sets up a new one from its profile.
This is the only recovery. There are no backup codes, and a login cannot remove or replace its own enrolled authenticator: while one is set up, setting up another is refused until an administrator - which may be the login itself, when it holds Alter any login - resets it. A login can only cancel a set-up it has not confirmed yet.
A login setting up its authenticator and an administrator requiring or resetting it - or two administrators - may act on the same account at the same time. Each operation writes only what it owns, and the administrator’s operations decide what they write from the account as it is stored, not from the copy of it they were started from. A set-up never touches the requirement. Requiring an authenticator keeps the moment the account already records, never lowers an account that has set one up meanwhile and never touches an authenticator, enrolled or pending; so a requirement that lands after another administrator’s reset leaves the account required and without an authenticator, which it sets up afresh. A reset drops the authenticator and takes from the stored account whether one is required; so a reset that lands after another administrator’s requirement leaves the account required, with the moment recorded afresh. An operation that can no longer apply, such as a code confirmed after the administrator reset the set-up it belonged to, is refused with a message saying that the login’s second factor was changed meanwhile, and nothing is written; read the account again and repeat the change if it is still wanted.
The name shown in the app#
An authenticator app lists each account under an issuer name. Querona takes it from the system instance that
serves the login’s request: open , edit the instance and fill in
Authenticator issuer. It is a setting of each instance, so on a deployment with more than one, fill
it in on every instance, or logins served by different instances see different names. Left blank, the name is
derived: Querona (<environment label>) when the instance has an environment label, otherwise
Querona (<licensee>) when the license names a company, otherwise Querona. A colon in the name is written
as a hyphen, because the app reads a colon as the end of the issuer. The name is a label the app keeps at set-up
time and takes no part in any code, so changing it affects only logins that set up an authenticator afterwards.
The master key, and clusters#
The key of an authenticator is stored encrypted under the instance master key - see CREATE MASTER KEY. On a node where the master key is not available, setting up an authenticator is refused rather than stored somewhere weaker.
On an instance with more than one node, setting up an authenticator, and requiring one of a login that has none
yet, are refused until the master key has a password protector (CREATE MASTER KEY ENCRYPTION BY PASSWORD):
a key that only the node it was created on can open would let the login sign in there and refuse it on every
other node. A single node is never refused on this ground, and neither is requiring an authenticator of a login
that has already set one up.
What is recorded#
Every change is audited as an alteration of the login - see Logging & diagnostics - and moves the
login’s modification date, as any alteration does. The audit records a text that names what changed:
ALTER LOGIN <name> WITH AUTHENTICATOR = PENDING when a login starts to set one up, = ENROLLED when it
confirms and = NONE when it cancels; ALTER LOGIN <name> WITH MFA = REQUIRED or = NONE when an
administrator requires or lifts it; and ALTER LOGIN <name> DROP AUTHENTICATOR for a reset. A refused
change - a wrong password or code, a missing permission, or a change that crossed another - is recorded as
failed. Requiring an authenticator of an account that is already required, or lifting a requirement it does
not have, is recorded but changes nothing and does not move the modification date. These texts are how the
audit describes the change; they are not statements Querona runs, and there is no ALTER LOGIN option for
the second factor.
The authenticator’s key, the QR code, the key typed by hand, the one-time codes and the password typed to begin a set-up are never written to the audit trail or to any Querona log, and never appear in a catalog view.
Where it fits#
The second factor and the trusted networks are the two controls a login’s sign-in can be held to. A password that leaks is of no use without the code, and because a code stays valid for its window, the network list is what bounds where a code someone captured could be replayed from.