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?
asked 28 Apr '11, 12:55
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:)
answered 28 Apr '11, 13:00
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.
answered 28 Apr '11, 13:29