Nextcloud 2FA borked. All admins locked out

No offense taken, just wanted to make sure… remove the brackets, it should just be ‘MarkB’ then.

And don’t forget the leading “u” from update… :wink:

ok back at it again

MariaDB [(none)]> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| nextcloud |
±-------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> use nextcloud;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [nextcloud]> update oc_twofactor_providers set enabled=‘0’ where provider_id=‘backup_codes’ and uid=’MarkB’;
ERROR 1054 (42S22): Unknown column ‘‘backup_codes’’ in ‘where clause’

Daily bump.
Any help is greatly appreciated!

Hi,

select the DB by issueing a:

use nextcloud;

otherwise prefix the table like this:

update nextcloud.oc_twofactor_providers set enabled=‘0’ where provider_id=‘backup_codes’ and uid=’MarkB’;

Server version: 5.5.60-MariaDB MariaDB Server
Copyright © 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]> use nextcloud

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [nextcloud]> update nextcloud.oc_twofactor_providers set enabled=‘0’ where provider_id=‘backup_codes’ and uid=’MarkB’;

ERROR 1054 (42S22): Unknown column ‘‘backup_codes’’ in ‘where clause’

MariaDB [nextcloud]>

logged in as the database user and still nothing.
theres no way it would be a different user is there?
i mean if i see a nextcloud database under the logged in user thats got to be it.
ive only set up one user as far as i know when i installed nextcloud

Hmm… why does it say, that there’s no column “backup_codes”? I mean, the column is named provider_id and the value we are looking for is backup_codes…

What does

describe oc_twofactor_providers;

return? Do you have a line break somewhere, this all has to be on one single line.

MariaDB [nextcloud]> describe oc_twofactor_providers;

+-------------+-------------+------+-----+---------+-------+
| Field   	| Type    	| Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| provider_id | varchar(32) | NO   | PRI | NULL	|   	|
| uid     	| varchar(64) | NO   | PRI | NULL	|   	|
| enabled 	| smallint(6) | NO   | 	| NULL	|   	|
+-------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

MariaDB [nextcloud]>

no line breaks im aware of

And please make sure, that you have single quotes around the values. I do believe, that if you copy this from the web forum, the single quotes get somewhat borked.

And this ladies and gentlemen has been another lesson on why it is always best practice to type out commands.
Dont be a slop like me…just type it

MariaDB [nextcloud]> update nextcloud.oc_twofactor_providers set enabled=‘0’ where provider_id=‘backup_codes’ and uid=‘MarkB’;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [nextcloud]>

ok soo…should i try logging in? restarting mariaDB?
im at your command

Just try loggin in - the database gets queried everytime you login. You can also try to get state of your user before as we did some time ago:

sudo -u apache php occ twofactorauth:state MarkB

sigh
still asking for codes. i have done it on a different browser with no history either

[root@ADELL nextcloud]# sudo -u apache php occ twofactorauth:state MarkB

Two-factor authentication is not enabled for user MarkB

Disabled providers:

  • backup_codes

[root@ADELL nextcloud]#

Aside from some more, …well intrusive methods, I am beginning to run out of ideas. Have you already tried to set all the twofactor apps to disable in the oc.appconfig table?

update oc_appconfig set configvalue=‘no’ where appid=‘twofactor_backcodes’ and configkey=‘enabled’;
update oc_appconfig set configvalue=‘no’ where appid=‘twofactor_u2f’ and configkey=‘enabled’;
update oc_appconfig set configvalue=‘no’ where appid=‘twofactor_totp’ and configkey=‘enabled’;

went back and re-entered all those commands with fixed quotes and they didnt return any errors

tried logging in and its still asking me. im ready to cry

one weird thing is…
the nextcloud client on my phone still works perfectly
i can pull files and upload without an issue

This only probably works until you log out from your phone client.

What does this query return?

select * from oc_twofactor_backupcodes where user_id=‘MarkB’;

MariaDB [nextcloud]> select * from oc_twofactor_backupcodes where user_id=‘MarkB’;

+----+---------+---------------------------------------------------------------------------------------------------+------+
| id | user_id | code                                                                                          	| used |
+----+---------+---------------------------------------------------------------------------------------------------+------+
|  1 | MarkB   | 2|$argon2i$v=19$m=1024,t=2,p=2$probably sensitive info |	0 |
|  2 | MarkB   | 2|$argon2i$v=19$m=1024,t=2,p=2$probably sensitive info |	0 |
|  3 | MarkB   | 2|$argon2i$v=19$m=1024,t=2,p=2$probably sensitive info |	0 |
|  4 | MarkB   | 2|$argon2i$v=19$m=1024,t=2,p=2$probably sensitive info |	0 |
|  5 | MarkB   | 2|$argon2i$v=19$m=1024,t=2,p=2$probably sensitive info |	0 |
|  6 | MarkB   | 2|$argon2i$v=19$m=1024,t=2,p=2$probably sensitive info |	0 |
|  7 | MarkB   | 2|$argon2i$v=19$m=1024,t=2,p=2$probably sensitive info |	0 |
|  8 | MarkB   | 2|$argon2i$v=19$m=1024,t=2,p=2$probably sensitive info |	0 |
|  9 | MarkB   | 2|$argon2i$v=19$m=1024,t=2,p=2$probably sensitive info |	0 |
| 10 | MarkB   | 2|$argon2i$v=19$m=1024,t=2,p=2$probably sensitive info |	0 |
+----+---------+---------------------------------------------------------------------------------------------------+------+
10 rows in set (0.00 sec)

MariaDB [nextcloud]>

where i put probably sensitive info it was a slew of numbers letters and symbols
should i log out of my phone and attempt all the commands on mariaDB again?

No, rather not. I’d suggest to wipe the codes from the DB, since we’ve deactivated all twofactor modules and also have set your account to not have any backup_codes enabled, but they’re there anyway.

So… go ahead and remove them from the table using this query:

delete from oc_twofactor_backupcodes where user_id=‘MarkB’;

1 Like

deleted and verified removed by checking with
select * from oc_twofactor_backupcodes where user_id=‘MarkB’;

still dead and losing hope

budy i would like to take this time to once again say i truly appreciate all your help
i know you owe me nothing and i cant thank you enough.

that said…should i run twofactorauth:cleanup?

I’d say, it cannot hurt - we’re already so deep into the weeds here. :wink:

And no worry about myself, although I actually thought, that this issue would be easier to tackle. Is this your personal instance? I am asking, because we might hit a point, where you will have to face a re-install of NC. You would not use any actual data, but this step would cause some real work on the database, but it is possible.