Error 37000: [Virtuoso Driver][Virtuoso Server]SQ074 using "set password"

[JonnyJD]

This is somewhat related to #1083 (again, automation is intended and: Version 07.20.3229-pthreads for Linux as of Oct 20 2021; = 7.2.5.1+dfsg1-0.2 in Ubuntu 22.04; running Docker)

According to the Quick Start guide: https://docs.openlinksw.com/virtuoso/defpasschange/ the "dba" password can be changed with this:

set password <old password> <new password>

However, when doing this (with or without single quotes) I get:

*** Error 37000: [Virtuoso Driver][Virtuoso Server]SQ074: Line 1:
at line 0 of Top-Level:
set password 'dba' '...'

Interesting enough, the return code of isql-vt is 0, indicating no error, even though it clearly did not work and an error message is displayed. (really bad for automation)

I also thought I did get it to work 2 days ago. Changed the password (old one wasn't working, new one was; logging in to conductor) with this method. But maybe I just did some mistakes testing this (having a return code 0 in the case of error is of no help here, as ansible reports this as working fine and you never see that error message either), or there are circumstances where this does work.

It looks like the solution I was using to change the password of the "dav" user in #1083 also works for "dba". So that user does not seem to need special handling for changing the password after all:

user_set_password('dba', '...')

Anyways, I think I do get it to work (for real now), but maybe the documentation is wrong in that regard and maybe this ticket helps others having the same problem.

[imitko]

@JonnyJD

The SET PASSWORD [OLD] [NEW] statement accepts SQL identifier NAMEs (see 9.30. SQL Grammar and 9.4. Identifier Case & Quoting).

As with all SQL identifiers, if either old or new password contains non-alphanumeric characters, special symbols, or uses mixed-case, it must be quoted with double-quotes (") — single-quotes (') are for STRINGs — e.g., set password "old s\'eCRet" "new s\'eCRet". To be certain of intended case handling (i.e., lowercase "name" Vs. uppercase "NAME" Vs. mixed "nAmE"), it is best practice to always quote these identifiers.

Also please note that this SET PASSWORD statement changes the password for the current logged-in user. A DBA can change passwords for other SQL accounts with USER_SET_PASSWORD().

HTH