Microsoft

The EnterpriseOne Microsoft category will include all posts and pages pertaining to Microsoft products in an EnterpriseOne installation.

Securing Oracle JD Edwards EnterpriseOne with Windows Firewall

EnterpriseOne Windows Firewall

On one of the Oracle JD Edwards EnterpriseOne installations that I manage, we have a few Windows 2003 Servers. Yeah, I know, it’s no longer supported but the Tools Release is 8.98.4.7 and there are a couple third-party applications that are not able to be upgraded. It’s crazy how messy, real-life situations cannot be duplicated in the squeaky-clean confines of the Oracle lab.

Since Windows 2003 Server is no longer supported by Microsoft, our IT Security Team has tried locking down these servers using several different methods. One of them has been to try and implement a software firewall on the server itself. Unfortunately, any third-party solution that we tried had such a negative impact on the performance of EnterpriseOne that we had to remove it. So, they asked that we turn on the Windows Firewall. While not as robust as they would have liked, it would provide another layer of security.

The good thing about the Windows Firewall, other than how simple it is, is that it shuts down all communication and only allows what you specify. That means, for EnterpriseOne to function, you need to make sure that all the applications and ports are allowed through the firewall. There were a few different documents that I used to come up with the correct recipe for successfully securing Oracle JD Edwards EnterpriseOne with Windows Firewall:

The easiest way to access the Windows Firewall settings is to go to [Start] -> Run -> firewall.cpl. I created a shortcut to firewall.cpl on the desktop to make it easier.

The following is a breakdown of what I came up with but since everyone’s configuration is different (CNC = Configurable Network Computing) your mileage may vary.

  • Made the following change to the jde.ini of the affected Windows Server:
    enablePredefinedPorts=1
  • Specified the following applications
    • E:\JDE_HOME\jdk\jre\bin\java.exe – Used by the JDE Server Manager
    • E:\JDEdwards\E900\DDP\system\bin32\jdenet_k.exe – Part of JDE Services
    • E:\JDEdwards\E900\DDP\system\bin32\jdenet_n.exe – Part of JDE Services
    • E:\JDE_HOME\bin\scfagent_64.exe – Used by the JDE Server Manager
    • E:\JDEdwards\E900\DDP\system\bin32\jdesnet.exe – Part of JDE Services
  • Specified the following ports
    • Oracle_Database_Port – Oracle DB communication port 1521
    • Server_Manager_Port – Oracle JDE Server Manager port 14501
    • Server_Manager_Port – Oracle JDE Server Manager port 14502
    • Server_Manager_Port – Oracle JDE Server Manager port 14503
  • Specified the following ports that correspond to the enablePredfinedPorts setting above:
    • Oracle_E1_Port_6015 – 6015
    • Oracle_E1_Port_6016 – 6016
    • Oracle_E1_Port_6017 – 6017
    • Oracle_E1_Port_6018 – 6018
  • Allowed PING for monitoring server availability by using the [Advanced] tab
    Windows Firewall Ping

There were a few more settings that I added to allow for our third-party applications but those are not related to EnterpriseOne.

Do you have any other tips or tricks to get Oracle JD Edwards EnterpriseOne to work with Windows Firewall?

Submit a Tip or Trick

Run Apache HTTP Server As A Windows Service

While working with a JDE EnterpriseOne add-on application, I needed to install the Apache HTTP Server as a Windows Service so that it would start automatically when rebooted. The command I used is:

httpd.exe -k install -n "MyHTTPServer_WindowsServiceName"

I got it from this Apache HTTP Server documentation:

Using Apache HTTP Server on Microsoft Windows

The relevant test is:

You can install Apache as a Windows NT service as follows from the command prompt at the Apache bin subdirectory:

httpd.exe -k install

If you need to specify the name of the service you want to install, use the following command. You have to do this if you have several different service installations of Apache on your computer. If you specify a name during the install, you have to also specify it during any other -k operation.

httpd.exe -k install -n “MyServiceName”

If you need to have specifically named configuration files for different services, you must use this:

httpd.exe -k install -n “MyServiceName” -f “c:\files\my.conf”

If you use the first command without any special parameters except -k install, the service will be called Apache2.4 and the configuration will be assumed to be conf\httpd.conf.

File Cleanup Using Windows BAT/CMD Files

I have used thisbatch script to purge file base on their last modified date and provide a log of the files that were deleted. It’s really easy to read and know exactly what it is doing.

I recently recieved a VBS script that did about the same thing but was pages long.

The little script below is what I use now. I love how simple this is:

forfiles /P "E:\JDEdwards\E900\DDP\temp" /S /D -2 /C "cmd /c del @path && echo @path >> E:\TempCleanup_%date:~4,2%%date:~7,2%%date:~10,4%.log"

Got any quick simple tricks? Post them in the comments.

Better Performance On Work with Payroll Transaction Ledger (P050999) With An Index On F0618

We have been getting complaints about the Work with Payroll Transaction Ledger (P050999) grid loading performance. So, I decided to take a look. What I found was that upon entering the application it does an automatic find on the grid using the following SQL statement with the current G/L date in the WHERE clause.

SELECT
YTDGL,YTMCU,YTSBLT,YTPAYM,YTPB,YTSBL,YTUAMT,YTPCUN,YTPRTR,YTDWK,YTSHRT,YTAN8,YTSHD,YTPPRT,YTPALF,YTBHRT,YTOBJ,YTGPA,YTSUB,YTCMTH,YTAUBP,
YTPHRW,YTPDBA,YTCO,YTAID,YTCRCD,YTCRDC
FROM PRODDTA.F0618
WHERE (YTDGL 

As you can see, this SQL statement is returning every record in the F0618 that has a G/L Date less than or equal to the system's current G/L Date. I don't know about your installation but the one that I was working on had 17+ million records. Why the heck would it need to return all that?

That screen was taking 8+ minutes to present information to the user.

With the help of my coworker Geordy and Technet, I came up with an index on the F0618 that didn't make the SQL statement finish any quicker, but it does present the user the first page of data extremely fast (LESS THAN 3 SECONDS!).

CREATE INDEX [F0618_CUSTOM]
ON [JDE_PRODUCTION].[PRODDTA].[F0618] ([YTDWK] ASC)
INCLUDE ([YTDGL],[YTAN8], [YTPALF], [YTPRTR], [YTCO], [YTMCU], [YTOBJ], [YTSUB], [YTSBL], [YTSBLT], [YTPHRW], [YTPCUN], [YTPPRT], [YTBHRT],
[YTSHRT], [YTSHD], [YTPAYM], [YTGPA], [YTPDBA], [YTPB], [YTUAMT], [YTAID], [YTCMTH], [YTCRCD], [YTCRDC], [YTAUBP])

You can see from the index that all I did was create an index over the field that the SQL was sorting on (YTDWK or Date of Work) and included the rest of the fields that were in the query.

There must be other little tricks with indexes in EnterpriseOne. Have you found any?

 

Restore SQL Server Database On A Schedule

Keep Calm and AutomateOne of our companies has a training environment in addition to the standard PS,DV, PY & PD environments. They have been working very hard refining their Procure-To-Process (P2P). Now that they know how they would like to handle this process, they need to train the users. That’s where the P2P Training (PTR) environment comes in.

We setup the environment and have had everything running smoothly for the last few months. However, last week we received a request to backup the data in that environment and then restore that backup copy every morning for the next 2 weeks.

It’s a really simple process, but kind of a pain that I had to manually do the restore.

Well, if you’ve been reading this blog for very long, you know that I can’t stand doing things more than once. A trained monkey, I am not. If it can be automated, I do it and it usually makes sense to do so. This was definitely one of those times.

What I ended up doing was making 2 files:

  1. A bat file to call the sql file
    echo off
    echo. >> restore.log
    echo %date% %time% >> restore.log
    sqlcmd -E -d JDE_PTR -i h:\restore.sql >> restore.log
    echo %date% %time% >> restore.log
    
    
  2. The sql file
    USE master
    GO
    
    ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO
    
    RESTORE DATABASE TestDB FROM DISK=N'H:\TestDB.bak' WITH  FILE = 1, NOUNLOAD,  REPLACE,  STATS = 10
    GO
    
    ALTER DATABASE TestDB SET MULTI_USER
    GO
    

Then, I just added a scheduled task that fired off the bat file every morning.

Note: When creating the scheduled task, you will need to fill in a value for the “Start in” option. I’m not sure why but it only works if you fill that in. I was using Windows Server 2008 R2 so your mileage may vary.

Scheduled Task Start In Option