ADFS 2016 Extranet Smart Lockout SQL Database Location?

Question

We've got an ADFS v.4 farm with SQL backend and ExtranetLockoutMode = 'ADFSSmartLockoutEnforce'

The feature seems to be working and we can successfully query for ESL activity via cmdlet Get-ADFSAccountActivity.

We're interested to get ESL data based on other criteria besides per UPN. For example, query all users with with X number of 'badpwdCountUnknown'. Perhaps this data is available in the Azure portal, but we've yet to locate it so thought we might have some luck querying the [ArtifactStore].[AccountActivity] table.

However, we are surprised to discover that we cannot locate this table, even when connecting to the SQL instance using the service account for which we had originally granted permission to create this table! We've tried to view via the table via both SSMS and shell - essentially getting back "Invalid object name 'ArtifactStore.AccountActivity.'" from database [AdfsArtifactStore].

Therefore:

1. Can this data be had from the portal?
2. Where else could the present data be returning [via the cmdlet] if not from an AccountActivity table??
3. Are there any plans to expand the cmdlet's ability to gather info besides on a per UPN basis?

Thanks for your time!
DaveC

Answer 1

Hi @Pierre Audonnet - MSFT ... or ALL

For anyone who may benefit... We learned of two items which caused this experience...

1. Per Microsoft documentation [https://learn.microsoft.com/en-us/windows-server/identity/ad-fs/operations/configure-ad-fs-extranet-smart-lockout-protection], the account being used to create the [AccountActivity] table must have sufficient rights in the 'AdfsArtifactStore' database. The cmdlet included in the ADFS module (Update-AdfsArtifactDatabasePermission) is used to ensure these rights exist, but that cmdlet does NOT return an exception or error when it fails; instead the exception is logged to the federation service event log. If one does not think to check the event log after running this cmdlet it potentially leads to the false belief that the permission was updated successfully. This was the case for us - our account did not have enough rights in the first place to modify the permissions on the database, so the table was never created. Once this was corrected by our DB admins, the permission change could be made and the table could be created. It would be helpful if the MS documentation could reflect this scenario OR the cmdlet should at least return the exception.
2. Although the federation service uses the new table to record account activity, the ESL feature still works properly even if the table does not exist. The data is cached in the process space of the federation service on whichever server is acting as the RoleOwner. In this case, if the federation service on role owner happens to terminate (or server is restarted, etc.) then the ESL data is lost. Otherwise as long as the role owner's service is running, the ESL feature works as designed, and one would not be aware the data is not being written back to any database unless one tries to go looking for it (and becomes very confused (like me)) :)

Thanks,
DaveC

Answer 2

The data is stored in the ArtifactStore.AccountActivity database.

It is not documented, hence not supported to query it directly. It doesn't mean it won't work if you do it. But the data format is subject to change without particular notice.

It is not written synchronously though. There is a timer component that write the stuff back to the DB. So the cmdLet might have more accurate data than the SQL direct lookup.

I am not aware of any plans of extending the lookup feature to use something else.

You could look for the failed log on events. It doesn't have the count (unless the account is actually locked out). But you would be able to measure things looking at the number of event per user.