Regarding Encrypted Parameters

[dstest]

Hi,

I want to verify like If in a Job if we have Database Stage (Oracle Enterprise,DB2 apI,ODBC --) and the password is parameterised and I want to check whether that is encrypted or not.Can you please suggest if there is any universe qurery to check if there is any database stage in a job and there are any parameters with encrypted type.

Thanks
dstest

[ray.wurlod]

The answer is yes, but it's messy.

Jobs With Encrypted Parameters
You would need to unpack the CParameters collection in the ROOT record for the job in DS_JOBOBJECTS, and then decode the enumerated parameter type.
Parameter types are in the multi-valued field #19 in this record. From JOBCONTROL.H you can learn that the value 1 is associated with Encrypted as a parameter type, and that there are fewer than 10 types so a search for "1" alone should suffice. Therefore we have a shortcut available. Something like the following should do it.

Code: Select all

SELECT NAME FMT '32L', CATEGORY FMT '40L' 
FROM DS_JOBS
WHERE JOBNO IN 
(
   SELECT OBJIDNO
   FROM DS_JOBOBJECTS
   WHERE OBJNAME = 'ROOT'
   AND EVAL "INDEX(@RECORD<19>,'1',1)" > '0'
);

As noted, this is a shortcut, though it does answer the question you asked about jobs that contain one or more encrypted parameters.

Jobs With Database Stages
This one is slightly easier, as the inner query can be driven directly off OLETYPE. You want ODBC, UV and Custom stage types (in server jobs database stages are all in the CCustomStage class).

Code: Select all

SELECT NAME FMT '32L', CATEGORY FMT '40L' 
FROM DS_JOBS
WHERE JOBNO IN 
(
   SELECT OBJIDNO
   FROM DS_JOBOBJECTS
   WHERE OLETYPE LIKE 'CODBC%'
   OR OLETYPE LIKE 'CUv%'
   OR OLETYPE LIKE 'CCustom%'
);

If you want both in one report, simply form a UNION in INTERSECTION on the inner query.

[dstest]

Hi Ray,

Thanks for your qucik reply.For example in a oracle enterprise stage or odbc stage we have a Password property there i defined #TargetPassword# and if there is any way i can find out what is the exact parameter name i defined in this property for all datasbase stages and then i use this parameter name to check if it is encrypted or not.

1)first i will check in a job if we have any database stages (odbc,oracle,db2).
2)I need to find what is parameter name i defined in the password property of the stage.
3)Use that paramater name and check if it is encrypted or not.


Thanks
dstest

[ray.wurlod]

You can do what I suggested above - simply form the intersection of the sub-queries.

If you want to attack it from the other end (that is, beginning with a particular parameter name) that's a fish of an entirely different colour, and a rather more complex query. Indeed, one that would take more time to develop than I have free at the moment. Yes it's do-able, but I only have so many unpaid hours I can devote to these kinds of things.

[dstest]

Hi Ray,

Can you please give me the underlying tables to get this information then i can explore my self.

Thanks
dstest

[ray.wurlod]

Already did.