How to migrate users with current password to another IQ server

This is to help customers migrate database users with current their passwords to a new IQ server.

I think some of us may already have used this method when it is difficult to choose the restoration using a valid backup.

Please refer to the steps to be done.

1. Create a function which returns a string value of the encrypted password.

create function dbo.hextostr2(in bin_expr varbinary(255))
returns varchar(255)
begin
    declare data_len integer;
    declare i integer;
    declare newstr varchar(255);
    set data_len=datalength(bin_expr);
    set newstr=”;
    set i=1;
    while i <= data_len loop
       set newstr=newstr || ‘\\x’ || upper(substring(inttohex(substring(bin_expr,i,1)),-1,-2));
       set i=i+1
    end loop;
   return rtrim(newstr)
end

2. Execute a select statement to get a “grant connect …” command string for a specific user.

select u.user_id, 1,  ‘grant connect to ‘ + u.user_name +  ifnull(u.password,’;’,’ identified by encrypted ”’  + hextostr2( password ) + ”’;’ ) as grant_cmd
  from sys.sysuserperm u
  where u.user_id > 99
  and (u.user_name = ‘jerry’);
 
  It will return an “grant connect …” output.
  =>
  grant connect to jerry identified by encrypted ‘\x01\xEE\x0B\xE5\x64\x52\x09\x72\xDA\x1D\xE2\x21\x06\xEA\x68\x9F\x62\x9C\x50\x2F\x59\x1F\x32\x3B\xDB\x0C\xCA\xBC\x58\xE7\xA1\x61\x58\x9B\xB2\xF9\xB1’;
 
3. Run it on a target IQ server.

4. If the source IQ server’s version is IQ 15.0, IQ 15.1, IQ 15.2 or 15.3, you will encounter an error when running the select statement above(Step 2).

Function ‘substr’ has invalid parameter ‘3’ (‘-2’)
SQLCODE=-1090, ODBC 3 State=”08004″

It was because  of ANSI_SUBSTRING option which was introduced in IQ 15.0.
This option controls whether or not the substr function conforms to the ANSI standards. In particular, it disallows the third argument from being a negative number.
In order to allow the negative numbers and avoid encountering this error, it is recommended that the option ANSI_SUBSTRING is set to ‘OFF’.
  
   set temporary option ANSI_SUBSTRING = ‘OFF’ ;

HTH

Jerry