Automic Workload Automation

Expand all | Collapse all

Another password challenge (&UC_JOBMD)

  • 1.  Another password challenge (&UC_JOBMD)

    Posted 05-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 05-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 05-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 05-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.