Logon tracking settings#
Querona records, for every login, when it last signed in successfully, when an attempt for it last failed,
and how many attempts failed since the last successful sign-in, together with the protocol and the client
program of the last successful sign-in. The Administration Portal shows the last sign-in on the user
screens, sys.dm_exec_sessions reports the previous logon of the current session in its
last_successful_logon, last_unsuccessful_logon and unsuccessful_logons columns, and
LOGINPROPERTY answers them per SQL login over
SQL: the failed attempts since the last sign-in (BadPasswordCount) and the moment of the last one
(BadPasswordTime) the way SQL Server does, and the last sign-in with its client under the
Querona-specific names LastSuccessfulLogonTime, LastLogonClientInterfaceName and
LastLogonProgramName. A Windows or Microsoft Entra ID login’s sign-ins are recorded too, but shown in
the Administration Portal and the REST API only: the function answers NULL for such a login, as on SQL
Server. To list every SQL login with its last sign-in, as a caller holding ALTER ANY LOGIN or
VIEW ANY DEFINITION, call the function for each row of sys.sql_logins:
SELECT name, CAST(LOGINPROPERTY(name, 'LastSuccessfulLogonTime') AS datetime) AS last_successful_logon_time
FROM sys.sql_logins
ORDER BY 2 DESC;
What counts as a logon#
Every completed sign-in counts, whichever way it came in: a SQL client over the TDS endpoint, whether with a
password, Integrated Windows Authentication or Microsoft Entra ID, and a sign-in to the web interface or the
REST API. A pooled connection that is reused does not sign in again, so it is not counted again. The
system account never signs in and is never recorded; the spark account, which the Spark driver uses to
connect back to Querona, is recorded like any other login.
A failed attempt counts when it named an existing login and was refused - a wrong password, a disabled account, an attempt from a network the login is not trusted from, or an attempt refused during a lockout (see Failed sign-in lockout). An attempt for a login that does not exist cannot be attributed and is not counted.
Nothing that identifies the person or the workstation is kept: neither the client address nor the client host name is stored. The client program name is what the client declared, such as an SSMS or a job runner.
Re-enabling a disabled account is recorded as a sign-in at that moment, with no protocol and no client program: the account then shows Last sign-in at the re-enable. It is what keeps a re-enabled account from being disabled again for inactivity - see Security policy settings.
Settings#
Configuration option |
Default value |
Requires restart? |
Description |
|---|---|---|---|
Track logons |
Enabled |
No |
Whether logons are recorded at all. Turning it off stops recording and writes what the
node observed since the previous flush; rows already stored are left alone. The user
screens and the REST API then show every login as one nothing has been observed for,
while |
Logon flush interval [seconds] |
60 |
No |
How often a node writes the logons it has observed to the metabase. A change takes effect after the interval currently running. |
Reading the dates#
A date is empty until the Engine observes the first sign-in of that login, with one exception: every account that existed when the inactivity rule was installed was given a last sign-in at that installation or upgrade moment, with no protocol or client program, unless a sign-in was already recorded for it - see Security policy settings. An account created since reports nothing until it first signs in.
In a cluster every node keeps its own exact view and merges it into one shared row, so the last sign-in an administrator sees covers the whole installation. A node’s own view is always at least as complete as the stored row, and the stored row catches up within a flush interval. A node that stops loses the sign-ins of its last interval. A node whose metabase is unreachable keeps recording in memory and writes what it holds once the metabase answers again; the outage is logged once, not at every interval.
The nodes of a cluster are expected to keep their clocks synchronised. Sign-ins and failed attempts are ordered by the clock of the node that observed them, so a failed attempt that a lagging node stamps earlier than another node’s later sign-in counts against the run that sign-in ended and is not reported as a failure since it.