Automic Workload Automation

  • Private Community

  • 1.  Another password challenge (&UC_JOBMD)

    Posted May 24, 2016 06:12 PM
    I have a new RA database agent solution that is logging onto a SQLServer database and running SSIS packages synchronously and performing thorough error checking.  I've also learned to pass connection object credentials into the SSIS package from UC4.  (We prefer to manage these credentials in UC4.)

    The problem is how to get the credentials from my LOGIN object into the desired SQL statement without exposing the password.  The &UC_JOBMD trick does not work in the context of an RA database job, so I don't think it is possible?

    If we can't get UC4 to do this, then we may have to manage the credentials in the SQLServer environment and code our SSIS solutions to pull them from there.


  • 2.  Another password challenge (&UC_JOBMD)

    Posted May 26, 2016 02:21 AM
    Pete Wirfs wrote:
     
    ...
    The problem is how to get the credentials from my LOGIN object into the desired SQL statement without exposing the password.  The &UC_JOBMD trick does not work in the context of an RA database job, so I don't think it is possible?
    ...
    Did you see my earlier discussion about this? I devised ways to do this in UNIX shell scripting, Perl, PowerShell, and Windows BAT scripting.


  • 3.  Another password challenge (&UC_JOBMD)

    Posted May 26, 2016 12:41 PM
    Michael_Lowry That is a useful thread.  Thank you for that.

    I think the ability to decrypt a LOGIN password is only available from UNIX and WINDOWS agents.  The solution I am working on is running in a SQL agent.  Even if I could decrypt the password, the SQL command would need to be scrambled from view, and I don't think there is a way to do that either.

    The SQL agent feature I would like to have is to be able to include an encrypted UC4 password in my SQL statements and have the agent automatically decrypt it at execution time and not display the decrypted form of the statement.  However doing this would still expose the decrypted statement to any person that is watching database SQL traffic, so from a password security standpoint what I am trying to do is simply not a good idea!

    The SQLServer solution that we will probably go with is to set up a SQLServer Integration Services environment variable to hold the credentials (which it can do securely), and map that environment variable to the SSIS connection object parameters. 


  • 4.  Another password challenge (&UC_JOBMD)

    Posted May 26, 2016 04:08 PM
    The SQL agent feature I would like to have is to be able to include an encrypted UC4 password in my SQL statements and have the agent automatically decrypt it at execution time and not display the decrypted form of the statement.
    Ah, ok. I overlooked the fact that it was an SQL job. Yeah, it would be nice if there were a way to do this. One way to implement this would be a new type of script/object variable that is protected from view. It would make troubleshooting more difficult, but would open up many possibilities too.


  • 5.  RE: Another password challenge (&UC_JOBMD)

    Posted Apr 25, 2020 03:09 AM
    Edited by MartinPelke Apr 25, 2020 03:15 AM
    This thread is kinda old already, but i just implemented an okay workaround for this topic. I created a new "PREP_PROCESS-Event" which does the decryption. The fun part is, that the decrypted password cannot be seen anywhere (e.g. as object variable or in reports), yet it is available to you in a script variable.
    Content of the object, in this case called EVENT.PWDECRYPT:
    :beginread 'UC_MsgNr:5229,&UC_NAME|&uc_runnr'
:  read &cmd,'00',          'UC_MsgNr:26000',,MCK
:  read &uc_eventfile,'00', 'UC_MsgNr:26001',,MK
:endread

##UC4[bash]&UC_JOBMD CMD="echo '&cmd'" > ~/&uc_eventfile 2>&1
##UC4[bash]UC_STATUS=$?

&UC_JOBMD MNR=&UC_MANDANT JNR=&UC_REALNR PNR=&UC_IP_PORT IPA=&UC_IP_ADDR TYP=E RET=$UC_STATUS TXT="        Job beendet"
exit $UC_STATUS​

    This is then used as follows (whenever you need a decrypted password):
    :SET &HND# = PREP_PROCESS("&MY_AGENT#", "PWDECRYPT",,"CMD=&I_CRYPTED_PASSWORD#", "UC_LOGIN=&MY_LOGIN#")
:PROCESS &HND#
:  SET &MY_DECRYPTED_PASS# = GET_PROCESS_LINE(&HND#)
:ENDPROCESS
:CLOSE_PROCESS &HND#​

    Be careful with the variable &MY_DECRYPTED_PASS# when using it to build plattform script. For example, it can be seen in the JCL during runtime.

    For different RA-Solutions I just found out, that you can simply use crypted passwords in some cases. In this case it is the Basic-Auth of REST-Agents (>= V4.5 only):
    :PUT_ATT "conn_auth_basic_password" = "&MY_CRYPTED_PASSWORD#"​


    EDIT: the latter only works for passwords not crypted by the password crypt cmd-utility.


    Hope this'll help someone out there.
    Original Message