How to set a password via SQL Remote passthrough mode for an older remote

[VolkerBarth]

With SQL Remote's passthrough mode, it's easy to reset a password for a remote, e.g. something like

:::SQL
PASSTHROUGH ONLY FOR <TheRemote>;
   setuser dbo;
   grant connect to "DBA" identified by 'SQL';
   commit;
PASSTHROUGH STOP;

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?

[VolkerBarth · Accepted solution]

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

 :::SQL
 GRANT CONNECT TO DBA IDENTIFIED BY ENCRYPTED '...';

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:)

[regdomaratzki]

Another (less secure) option would be to create, call and drop a stored procedure in passthrough.

CREATE PROCEDURE NewUser_201104081314( in @user varchar(128), in @pwd varchar(128) )
BEGIN
  MESSAGE 'NewUser_201104081314 place holder';
END;

PASSTHROUGH ONLY FOR rem1;
CREATE PROCEDURE NewUser_201104081314( in @user varchar(128), in @pwd varchar(128) )
BEGIN
  EXECUTE IMMEDIATE 'GRANT CONNECT to "' || @user || '" IDENTIFIED BY ''' || @pwd || '''';
END;
CALL NewUser_201104081314( 'DBA', 'SQL' );
DROP PROCEDURE NewUser_201104081314;
PASSTHROUGH STOP;

DROP PROCEDURE NewUser_201104081314;

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.

[VolkerBarth]

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.

[VolkerBarth]

Another (less secure) attempt I have thought of was the use of dynamic SQL:

:::SQL
PASSTHROUGH ONLY FOR <TheRemote>;
   execute immediate 'grant connect to "DBA" identified by ''SQL''';
   commit;
PASSTHROUGH STOP;

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.