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?

asked 28 Apr '11, 12:55

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%


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

permanent link

answered 28 Apr '11, 13:00

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

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.

permanent link

answered 28 Apr '11, 13:29

Reg%20Domaratzki's gravatar image

Reg Domaratzki
7.7k343118
accept rate: 37%

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:

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

(29 Apr '11, 05:28) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×103
×25

question asked: 28 Apr '11, 12:55

question was seen: 3,584 times

last updated: 29 Apr '11, 05:28