With SQL Remote's passthrough mode, it's easy to reset a password for a remote, e.g. something like
However, when using a newer consolidated (v10 and up), the newly created password will be SHA256-hashed, and will be send in encrypted form to the remotes. Naturally, pre-v10 remotes won't recognized that format, and will reject the GRANT CONNECT statement. So how do you set the password in a compatible manner? |
You need to use an pre-10 database and set the password there (not in passthrough mode). Then you have to check the log (via DBTRAN) for the according encrypted GRANT CONNECT statement, something like
Use that exact statement (i.e. the encrypted part) in the passthrough statement, as it will be sent as-is and will contain the password in the former proprietary format used by pre-10 databases. And breathe deep:) |
Another (less secure) option would be to create, call and drop a stored procedure in passthrough.
I'm not a big fan of exposing the UID and PWD in the transaction log of the consolidated database, or the verbose output in the SQL Remote log, but this was the first idea I had. A prodedure was my second thought - but with a hard-coded PWD and used with hidden syntax, cf. that question). That should hide the contents from logs and SQL Remote verbose mode output.
(29 Apr '11, 05:13)
Volker Barth
Another (less secure) attempt I have thought of was the use of dynamic SQL:
However, dynamic SQL seems to be invalid in passthrough mode (and I tend to forget that). Besides, even if it is possible that statement (and as such, the PWD, too) would occur as-is in the consolidated log and SQL Remote verbose output.
(29 Apr '11, 05:28)
Volker Barth
|