Encrypt user name and password for sqlexp connecting to progress database

progress user

New Member
I have a sql broker setup for our database. I want to now monitor if sql broker is running or not.
I am using this shell script to connect to the database
$DLC/bin/sqlexp -S $SERVICENAME -db $DB -user "USERNAME"-password "PASSWORD" -infile "xxxxx".
infile has a basic select statement

How do i encrypt my user name and password for the above ?
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Searching the KB from "sqlexp encrypt password" give me this as the first hit:
Knowledge Article

Notes about that solution:
  • genpassword isn't strong encryption; it's barely encryption at all
  • the genpassword-obfuscated password is still visible in the shell as a sqlexp parameter
  • the genpassword-obfuscated password is itself a valid password, so it doesn't provide much practical security
 

progress user

New Member
Thanks , Rob.
Is there a way i can do it with a 4gl query where i can store all the user names and passwords in a separate database ?
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Is there a way i can do it with a 4gl query where i can store all the user names and passwords in a separate database?
If you have the ability to run a 4GL query, you don't need to run SQL statements at all.

This is a simple example of displaying the brokers and their attributes, including -ServerType.
Code:
// display brokers and their -ServerType values

define variable i          as integer   no-undo.
define variable servertype as character no-undo.

// select only the broker records, not the servers
for each dictdb._servers no-lock where _servers._server-type = 'login':
  i = 0.

  // the _srvparam-* array fields contain the client-server parameters, including -ServerType
  srvparams:
  repeat:
    i = i + 1.
    case _servers._srvparam-name[i]:

      // there are no more C/S params for this broker; we didn't find -ServerType
      when ? then do:
        servertype = "N/A".
        leave srvparams.
      end.

      // we found the -ServerType parameter for this broker
      when "-ServerType" then do:
        servertype = _servers._srvparam-value[i].
        leave srvparams.
      end.

      // this isn't the param we want
      otherwise .

    end case.

  end.

  // display the brokers and their attributes
  display
    _servers._server-id
    _servers._server-num
    _servers._server-id
    _servers._server-type
    _servers._server-broker-pid
    servertype
  .

end.

This code assumes you are using at least 11.6 (due to single-line comments) and your VST schema is at least at the 11.5 level (due to the _srvparam-* fields).

The _servers VST has one record for every broker and server. It is a broker if its _server-type is "login". The design of this table is kind of nasty (i.e. not normalized). It contains a collection of array fields that hold the various client/server startup parameters and their values. I am keying on the value of the -ServerType parameter to determine if the broker is a SQL broker. Possible values are "ABL, "SQL", or "BOTH".

Notes on "BOTH":
  • Don't use "BOTH". Each server type that is in use should have (at least) one dedicated broker with the appropriate -ServerType.
  • Confusingly (and in my opinion, incorrectly), if you start the primary broker without -S (i.e. it is not also a connection broker), it will still be reported in _servers as -ServerType "BOTH". In fact, it is "NEITHER". You can't connect a client to it remotely.
    I seem to recall old versions of the docs (maybe 10.1 something?) listing "NEITHER" as a possible value for -ServerType. Well, now it isn't. The upshot is that if you see -ServerType "BOTH" for a broker, it might actually be a broker that can spawn either 4GL or SQL servers, or it might be a primary broker that isn't bound to a port and won't spawn any servers at all. You can tell which is which because in the former case, _servers._server-broker-pid will have a value (the PID of the broker) and in the latter case that field's value will be zero.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Re-reading your original post:
I have a sql broker setup for our database. I want to now monitor if sql broker is running or not.
Are you having a problem where you are instantiating a secondary SQL broker and then it goes away, while the database is still running? I've never encountered that before.
 

progress user

New Member
Thanks a lot, Rob for the replies.

Yes, I had a secondary SQL broker that went down even when the database is running. So wanted to create a monitor and alert if that ever goes down.
That is the reason I am tying to make a connection to the database using sqlexp. the problem with it is we have to pass user name and password in the script which is not encrypted.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
With either ABL or SQL there isn't a good solution (that I am aware of) for password encryption. If someone learns the encrypted password, it's as good as knowing the plaintext equivalent.

At least with an ABL client you can move the -U and -P parameters into a parameter file, e.g. mpro mydb -pf /path/mydb.pf, where /path/mydb.pf contains the sensitive parameters, e.g.:
Code:
# mydb.pf
-U fred
-P fredspw

Then you can put the .pf in a directory where it can be locked down with OS directory and file permissions, so it is only accessible by the appropriate user(s).
 

Chris Hughes

ProgressTalk.com Sponsor
Just to add along the same lines as Robs post, Windows Server ships with encryption these days you could encrypt a batch file so only a service account can read it. This in Windows land avoids the issues of all your IT teams having local admin access and reading the file anyway!
 

progress user

New Member
Thanks for the replies guys, really appreciate it.

I stored my username and password in the database.
now, is there a way i can use 4gl program to take these values?

Thanks in advance.
 

progress user

New Member
Hi Tom,
I stored them in database using Encode function. I am just concerned now how to use/retrieve the original password to my 4gl script or the shell script.

Thanks in Advance.
 

TomBascom

Curmudgeon
That is nice to know but it isn't what you said you were doing.

It also is not going to help you to establish a SQLEXP connection.

The whole point of ENCODE is that it is a one-way function. There is no DECODE.

Usually you use this to compare a password that a user has entered by encoding the input value and comparing that to the stored endcoded value. That way your program can confirm that it is the same as what was previously entered without having to store the secret value in clear text.

(Technically you should encode a *salted* value and also salt the user-provided value. This makes the use of "rainbow tables" for password guessing much more expensive.)
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
If you stored the passwords in an application table, as opposed to _User, then you have no need to use encode. Encode is poor as a cryptographic hash function. I would suggest using multiple rounds of a stronger hash function like SHA-512, salted as Tom suggested.
 
Top