Simple and secure Azure SSO for JDE

Who'd like to be able to use all native security options available to them via Azure AD used in JD Edwards - me!

Perhaps take advantage of self enrolling to be able to use the application in the fist place.

Our SSO solution will work side by side with the native capability of JD Edwards to use AD for long username and password authentication - but we give you the additional ability to use Azure and the power of Azure (for things like conditional access / MFA) natively in JDE.

We let Azure do all of the heavy lifting, see https://docs.microsoft.com/en-us/azure/active-directory/develop/v2-protocols-oidc We cleverly use openID connect (an extension of oauth2.0) to allow users to authenticate to Azure for their access to JD Edwards.  

You can see from the above that we do a couple of modifications to JDE to allow this to happen.  Firstly, we allow a redirect (manually with a button or automatically) to send users to Azure:

This button is the second dotted line in the MSFT supplied schematic.

The user then enters credentials with Azure - not with our products - they negotiate.  Said negotiation might include conditional access from various available signals - see below:

The browser then gets a login token from Azure - thanks for that and passes this back to the /auth handler that we have installed on JAS WLS instance.  This handler needs to go out to the internet to validate that the id_token is valid and then generates a PS_TOKEN to all of the user to log into JD Edwards.

The PS_TOKEN is generated for a custom security node, which improves the security posture of the JD Edwards installation.

Note that if the user already has a id_token - then the visit to Azure is ignored and goes directly to myAccess to validate the id_token - see below for a JDE centric view of things.

Once myAccess has generated A PS_TOKEN, this is sent to JDE for verification.  If JDE likes what it's sent, the user is granted login permissions and can continue to use said token for it's defined lifecycle.

So hopefully you can see from the above that all of the heavy lifting is being done by Azure.  We are neatly and securely playing the JDE specific part of logging the user into JD Edwards.

The creation of an enterprise application in Azure is trivial and only takes about 30 minutes.  We are then able to patch your tools release to have an additional "login" button as seen above and add some redirects into a couple of jsp files.  

Finally, the glue in all of this is an enterprise application that sits in WLS and does the heavy lifting in terms of validating the id_token and also generating the PS_TOKEN.  Note that this also rips apart the authentication JWT to extract the long username that is used next to the PS_TOKEN to ensure that the correct user is being logged into JDE.

The design above easily caters for additional Enterprise Applications to be defined in Azure, that will have different id_tokens / cookies - which will also allow for native logging into different JD Edwards environment.

see more here:  https://www.fusion5.com.au/jd-edwards/myaccess/ 

Stored procedure calling from JDE - yes please

I've been involved in a couple of projects that need the ability to call stored procedures from JD Edwards directly.  It does get messy.  Previously I've written an intermediate DLL which loads the OCI DLL's and eventually calls OCIExecuteStmt, which worked well and was secure.  This also did really nice and granular security [kinda] and transaction processing was a breeze.

Despite this being neat, it required constant maintenance, as you needed to repack the tools release with the intermediate DLL each time.  I also had to store the password for the user that needed to run the stored procedures in the JDE.INI.  This was cool and I wrote some encryption algorithms to that would encrypt and decrypt the passwords that I wanted to use.

I've changed tact, I don't know which method I prefer.  I now created a trigger on a standard JD Edwards table that would fire off and run the SP (with up to 3 parameters) before insert into the table.  I chose before insert, as this gave me transaction control.  It's pretty native that if the SP fails when the trigger calls it, then this will also bomb out the insert and return an error to JDE.  This allows for the transaction processing and error handling I need.

I actually do not really like this solution, an insert that calls a trigger that calls a stored procedure - but we are limited in how we are able to execute statements.

I created table F55TRIGX:

Name        Null?    Type          
----------- -------- --------------
TGMATH01    NOT NULL NUMBER        
TGY55SPROCN          NVARCHAR2(256)
TGY55PARM1           NVARCHAR2(512)
TGY55PARM2           NVARCHAR2(512)
TGY55PARM3           NVARCHAR2(512)

It's a little annoying that JDE needs a PK for a table like this, but hey-  I just soaked up a NN.  So, when you insert into the above, my trigger essentially runs:

execute immediate TGY55SPROCN[(TGY55PARM1,TGY55PARM2,TGY55PARM3)]

Note that everything in [] is optional.

I don't really need to provide much more than the trigger text below to get you working at an oracle site.  The trigger writing process is SO terrible for logging and debugging, so thank me later that you can start with a working example.

create or replace trigger TESTDTA.F55TRIGX_GO

before insert on TESTDTA.F55TRIGX

for each row

declare

  results2 varchar2(256);

  szProcedureName varchar(256);

  szParm1 varchar(512);

  szParm2 varchar(512);

  szParm3 varchar(512);

  szCommand varchar2(2048);

begin

  szParm1:=ltrim(rtrim(:new.TGY55PARM1));

  szParm2:=ltrim(rtrim(:new.TGY55PARM2));

  szParm3:=ltrim(rtrim(:new.TGY55PARM3));

  szProcedureName:=ltrim(rtrim(:new.TGY55SPROCN));

  if length(szProcedureName)>0 then

    -- Call this procedure with the parameters

    if(length(szParm1)>0 and length(szParm2)>0 and length(szParm3)>0) then

      szCommand := 'declare ReturnVal varchar(20); begin ' || szProcedureName || '(''' || szParm1 || ''',''' || szParm2 || ''',''' || szParm3 || ''')' || '; end;';

    elsif (length(szParm1)>0 and length(szParm2)>0) then

      szCommand := 'declare ReturnVal varchar(20); begin ' || szProcedureName || '(''' || szParm1 || ''',''' || szParm2 || ''')' || '; end;';

    elsif (length(szParm1)>0) then

      szCommand := 'declare ReturnVal varchar(20); begin ' || szProcedureName || '(''' || szParm1 || ''')' || '; end;';

    else

      szCommand := 'declare ReturnVal varchar(20); begin ' || szProcedureName || '; end;';

    end if;

    execute immediate szCommand ;

    results2 := 'True';

  end if;

end;

This is a nice way for you to automate tasks or perform some "heavy lifting" in the database.  note that you probably need to prefix the SP with the owner or manage that in a consistent way.