Posts

User sqlplus with Lawson (Tips and Tricks)

Most of customers have by now switched over to a Windows / SQL server environment, but we still have several customers who have stayed on Oracle for their Database needs. This mostly stems from having the Oracle skill set in-house as there is really no other advantage to staying on Oracle once you have moved over to Windows.

Often when there are troubles with connecting to the application, it is relevant to test the connection the database from the server itself. Of course there are several ways to do this, but test fastest way is to do so directly from the command prompt in LID as it doesn’t require any additional setup or software. But it is easy to forget how this is done so we decided to write this quick article to document this very simple syntax.
The utility we’re going to use is called sqlplus and it should already installed on your LSF application server. Simply login to the server using LID and on the command prompt type in the following command:
sqlplus <username>/<password>@dbserverName
If you have the correct username and password, and the server is responding, you will get a SQL> prompt on which you can run any query you want. Here’s an example:
However if you type in the incorrect username:
And finally, if you have the incorrect server name or the server is not responding, the prompt will be suspended for several seconds and you will see the following message:
A few small notes about using sqlplus:
  • Be sure to use a semicolon to end your statements. Otherwise the application doesn’t know when to run your query.
  • Make sure the environment variable %ORACLE_HOME% is set correctly. ($ORACLE_HOME on Unix):
  • To exit sqlplus user the “quit” command
  • The SQL buffer contains the last statement you ran, and you can run the previous query again by simply typing “RUN”  and hitting enter.
  • User the LIST command to see a list of your most recently executed SQL commands.
  • “HELP INDEX” shows a list of possible commands
  • To launch a sql script simply put the “@” symbol in front of the file name and execute it. like: @script.sql or even @/path/to/script.sql
  • You can have a multi-line sql statement.
  • The “SHOW USER” command prints the name of the Oracle user you’re logged in as
  • The “SHOW ALL” command prints all the current settings to the screen.

Certificate chaining error

During an LSF outage, we checked the latest logs and saw security_authen.log was updated. It showed a number of errors that a certificate was not trusted. We checked the trusted certificates and saw that the certificate and related certificates were all trusted. So why was the error returned?

Scroll further down the log list to ladb.log. You may see that there is a GEN failed message. Verify that the GEN database really is available by connecting to it directly with a database utility like SQL Studio. Then verify that the gen database connection info is correct in LAWDIR\gen\MICROSOFT. If you are using SERVICENAME to lookup the password, you may want to test commenting out the service name and including the id/password in the file and secure the file. Restart the server and test the portal.

 

SECURITY_AUTHEN.LOG

Caused by: java.security.cert.CertPathValidatorException: The certificate issued by CN=PKIROOT-01-CA is not trusted; internal cause is:

java.security.cert.CertPathValidatorException: Certificate chaining error

at com.ibm.security.cert.BasicChecker.<init>(BasicChecker.java:111)

at com.ibm.security.cert.PKIXCertPathValidatorImpl.engineValidate(PKIXCertPathValidatorImpl.java:199)

at java.security.cert.CertPathValidator.validate(CertPathValidator.java:278)

at com.ibm.jsse2.util.f.a(f.java:14)

… 68 more

Caused by: java.security.cert.CertPathValidatorException: Certificate chaining error

at com.ibm.security.cert.CertPathUtil.findIssuer(CertPathUtil.java:316)

at com.ibm.security.cert.BasicChecker.<init>(BasicChecker.java:108)

… 71 more

 

LADB.LOG
DBDataAreaFactory_1 Create of kind “GEN” failed.
java.lang.UnsatisfiedLinkError: com/lawson/rdtec
h/db/api/DBJni.jniTsDBConnect(I)Ljava/lang/Integer;
DBDataAreaFactory_1 Create of kind “GEN” failed.java.lang.UnsatisfiedLinkError: com/lawson/rdtech/db/api/DBJni.jniTsDBConnect(I)Ljava/lang/Integer;

 

MICROSOFT
#LAWGATENAME=msfdb2000
DBSERVER=lawdbserver
DBNAME=LAWGEN
#SERVICENAME=DBGEN
LOGINNAME=lawson
PASSWORD=lkaj7fde#%&hdsw
SCHEMA=dbo
FILEGROUPS=FALSE
#DEBUG=TRUE

 

PORTAL

Nogalis Soars at the 15th Annual West Coast Mega Conference

Kicking off 2018, the Lawson user group community held its first event, the 15th Annual West Coast Mega Conference. Bringing together the Pacific Lawson User Group (PLUG), Southern California, Arizona, Nevada Lawson User Group (SCANLUG), and Northern California User Group (INCLU), 19 vendors and over 100 attendees gathered at the Pacific Life building in Newport Beach, California for a 2-day conference on January 23rd and 24th. Attendees chose from over 60 educational sessions covering topics from upgrading to the latest Lawson version, to migrating from on premise to the cloud. Nogalis hosted 2 sessions (Payroll Automation – How we pay 100,000 employees weekly using IPA with no user interaction & How Several Lawson Clients have cut Operational Costs by 35%) with almost 40 attendees, among the largest turnout of any other vendor session.

In recent years, Nogalis introduced their new security product, LSFIQ . The 2018 West Coast User Conference was the first for attendees to see the latest version of LSFIQ. People who visited the booth were given quick demos of the product, signing those interested up for a free version to test out. In return, they would be entered in a drawing for a chance to win a bitcoin wallet valued at over $100. And just for visiting the booth, attendees were given phone hook clips branded with the Nogalis logo to attach to their mobile phones as a handy phone stand and holder. The bitcoin wallet gathered interest; however, the grand prize was the talk of the event. Those who attended either of the Nogalis sessions were automatically entered in a drawing for a chance to win a FREE helicopter ride from local flight school, Revolution Aviation. The ride would include an introductory lesson, a 40 minute – 1 hour tour of Orange and Los Angeles County, and an opportunity to take control of the aircraft in the pilot’s seat! With many of the attendees being Southern California residents, there was no doubt they wanted to get their hands on this amazing experience.

As gold sponsors once again, Nogalis hosted the evening  event at the Marriott hotel, where attendees and vendors enjoyed great food, played casino games, networked with one another, and won raffle prizes.

 

Batch Error

When Lawson batch jobs are not running, they return the following error:

 

——————————————————————————–

User: lawson Job: C Queue: **********

——————————————————————————–

BEGIN: Job Submitted: Wed Dec 6 11:30:01 2017

Step 1: CU201 Started. . . . . .: Wed Dec 6 11:30:01 2017

Token Command. . . . . .: D:\lsfprod\law\prod\obj\CU201.gnt

Executable Command . . .: D:\COBOL\bin64\run.exe D:\lsfprod\law\prod\obj\CU201.gnt prod NT00000002 C 1 Process ID . . . . . . .: 11996

Running as Account . . .: \lawson

Program Messages:

Load error : file ‘lacobrts.dll’

error code: 173, pc=0, call=1, seg=0

173 Called program file not found in drive/directory

Elapsed Time . . . . . .: 00:00:00

ERROR: Stopped On Exit 32. Elapsed Time: 00:00:01

END: Job Ended: Wed Dec 6 11:30:02 2017 

 

The error message from the job log indicated a problem with lacobrts.dll.  The actual problem was related to the local security policy.  This error was resolved by reviewing the setup for the lawbatch id.  The LSF system in this example had a secured ldapbind.  The domain lawbatch id should be added to the Local Security Policy under User Rights Assignment for “Log on as a batch job” and “Allow log on locally.”

 

 

Security Violation

When applying a patch to the LSF environment, we saw Security Violation errors on environment utilities even though security was turned off.  In the below example, the error was returned from trying to run ldunivtkns (to load environment tokens) and also on envrelease (to show the environment version.)  This issue was resolved after contacting the network team to replicate file system permissions from an older Lawson server.  While it was not shared exactly which permissions were changed that were not already in place, once the new permissions were applied, the Security Violations were replaced in the logs with the appropriate responses from the commands.

Initial Error

 

Error Resolved

 

Environment Security Settings

Landmark Authentication Fails

After completing federation and restarting LSF and Landmark, landmark authentication fails.  The security authen log returns the following error:  sun.security.validator.ValidatorException: PKIX path building failed.

This can happen if secured ldap bind is being used.  With the secured ldap bind (using ldaps protocol and port 636), the certificates from the AD server are missing from the java keystore on the landmark server.  This can happen even if you are using SSOP on LSF for authentication.  To resolve the issue, export the certificates from the AD server and import them into the java keystore.  If LSF was bound to AD, the certificates should already be on the LSF box.  They can be copied over from LSF and imported to the keystore on the landmark server using the following example.

 

D:\JDK\bin\keytool.exe  -keystore D:\JDK\jre\lib\security\cacerts -importcert -alias ADca –file D:\cacert.cer

D:\JDK\bin\keytool.exe  -keystore D:\JDK\jre\lib\security\cacerts -importcert -alias ADroot –file D:\root.cer

 

 

Error:

 

Wed May 31 09:49:13.112 MDT 2017 – default-724934462: Error encountered while getting users DN. Please see logs for details[egn1ldmam2ike26udaqvs9rs2g]Could Not Bind With privileged identity. User [lawson]simple bind failed:ldap.domain.com:636

Stack Trace :

javax.naming.CommunicationException: simple bind failed: ldap.domain.com:636 [Root exception is javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target]

at com.sun.jndi.ldap.LdapClient.authenticate(LdapClient.java:219)

at com.sun.jndi.ldap.LdapCtx.connect(LdapCtx.java:2788)

at com.sun.jndi.ldap.LdapCtx.<init>(LdapCtx.java:319)

at com.sun.jndi.ldap.LdapCtxFactory.getUsingURL(LdapCtxFactory.java:192)

at com.sun.jndi.ldap.LdapCtxFactory.getUsingURLs(LdapCtxFactory.java:210)

at com.sun.jndi.ldap.LdapCtxFactory.getLdapCtxInstance(LdapCtxFactory.java:153)

at com.sun.jndi.ldap.LdapCtxFactory.getInitialContext(LdapCtxFactory.java:83)

at javax.naming.spi.NamingManager.getInitialContext(NamingManager.java:684)

at javax.naming.InitialContext.getDefaultInitCtx(InitialContext.java:313)

at javax.naming.InitialContext.init(InitialContext.java:244)

at javax.naming.InitialContext.<init>(InitialContext.java:216)

at javax.naming.directory.InitialDirContext.<init>(InitialDirContext.java:101)

at com.lawson.security.authen.LawsonLDAPBindLoginProcedure.getDNForUser(LawsonLDAPBindLoginProcedure.java:446)

at com.lawson.security.authen.LawsonLDAPBindLoginProcedure._authenticate(LawsonLDAPBindLoginProcedure.java:233)

at com.lawson.security.authen.LawsonLDAPBindLoginProcedure.authenticate(LawsonLDAPBindLoginProcedure.java:681)

at com.lawson.security.authen.LawsonLoginSchemeImpl.authenticate(LawsonLoginSchemeImpl.java:1701)

at com.lawson.security.authen.LawsonProgrammaticAuthenticatorImpl.authenticate(LawsonProgrammaticAuthenticatorImpl.java:198)

at com.lawson.security.authen.LawsonProgrammaticAuthenticatorImpl.authenticate(LawsonProgrammaticAuthenticatorImpl.java:100)

at com.lawson.rdtech.gridadapter.provider.LmrkSessionProvider.createGridPrincipal(LmrkSessionProvider.java:287)

at com.lawson.rdtech.gridadapter.provider.LmrkSessionProvider.validatePassword(LmrkSessionProvider.java:254)

at com.lawson.rdtech.gridadapter.provider.AbstractSessionProviderBase.logon(AbstractSessionProviderBase.java:134)

at com.lawson.rdtech.gridadapter.provider.LmrkSessionProvider.logon(LmrkSessionProvider.java:159)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at com.lawson.grid.proxy.ProxyServerImpl$ProxyRequestThread.invoke(ProxyServerImpl.java:2715)

at com.lawson.grid.proxy.ProxyServerImpl$ProxyRequestThread.processRequest(ProxyServerImpl.java:2502)

at com.lawson.grid.proxy.ProxyServerImpl$ProxyRequestThread.runThread(ProxyServerImpl.java:2425)

at com.lawson.grid.util.thread.PooledThread.run(PooledThread.java:137)

at java.lang.Thread.run(Thread.java:745)

Caused by: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

at sun.security.ssl.Alerts.getSSLException(Alerts.java:192)

at sun.security.ssl.SSLSocketImpl.fatal(SSLSocketImpl.java:1949)

at sun.security.ssl.Handshaker.fatalSE(Handshaker.java:302)

at sun.security.ssl.Handshaker.fatalSE(Handshaker.java:296)

at sun.security.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1514)

at sun.security.ssl.ClientHandshaker.processMessage(ClientHandshaker.java:216)

at sun.security.ssl.Handshaker.processLoop(Handshaker.java:1026)

at sun.security.ssl.Handshaker.process_record(Handshaker.java:961)

at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:1062)

at sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1375)

at sun.security.ssl.SSLSocketImpl.writeRecord(SSLSocketImpl.java:747)

at sun.security.ssl.AppOutputStream.write(AppOutputStream.java:123)

at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)

at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)

at com.sun.jndi.ldap.Connection.writeRequest(Connection.java:426)

at com.sun.jndi.ldap.Connection.writeRequest(Connection.java:399)

at com.sun.jndi.ldap.LdapClient.ldapBind(LdapClient.java:359)

at com.sun.jndi.ldap.LdapClient.authenticate(LdapClient.java:214)

… 30 more

Caused by: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

at sun.security.validator.PKIXValidator.doBuild(PKIXValidator.java:387)

at sun.security.validator.PKIXValidator.engineValidate(PKIXValidator.java:292)

at sun.security.validator.Validator.validate(Validator.java:260)

at sun.security.ssl.X509TrustManagerImpl.validate(X509TrustManagerImpl.java:324)

at sun.security.ssl.X509TrustManagerImpl.checkTrusted(X509TrustManagerImpl.java:229)

at sun.security.ssl.X509TrustManagerImpl.checkServerTrusted(X509TrustManagerImpl.java:124)

at sun.security.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1496)

… 43 more

Caused by: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

at sun.security.provider.certpath.SunCertPathBuilder.build(SunCertPathBuilder.java:141)

at sun.security.provider.certpath.SunCertPathBuilder.engineBuild(SunCertPathBuilder.java:126)

at java.security.cert.CertPathBuilder.build(CertPathBuilder.java:280)

at sun.security.validator.PKIXValidator.doBuild(PKIXValidator.java:382)

… 49 more

 

Wed May 31 09:49:13.113 MDT 2017 – default-724934462: Error encountered while getting users DN. Please see logs for details[egn1ldmam2ike26udaqvs9rs2g]Could Not Bind With privileged identity.

Wed May 31 09:49:13.113 MDT 2017 – default-724934462: Failed to get DN for user: lawson

Installing CTPs in Lawson

Here is a quick reference to Install CTPs in Lawson.

  1. Download and save CTP to LSF Server
  2. Extract .tar (unzip) on LSF server to folder (d:\patch\CTPXXXX)
  3. Log on LID as Lawson
  4. Change directory to extracted CTP location:
    cd d:\patch\CTPXXXXX)
  5. Run command: perl %gendir%/bin/lawappinstall preview <PRODLINE> (make sure it completes successfully)
  6. Rename/Save Preview.log
  7. Run command: perl %gendir%/bin/lawappinstall update <PRODLINE> (make sure it completes successfully)
  8. Run command: perl %gendir%/bin/lawappinstall activate <PRODLINE> (make sure it completes successfully)
  9. Go to the Lawson application and perform general testing to make sure everything is up and running.

DBREORG ISSUE(S):

During the ACTIVATE mode if it stops at dbreorg, this is likely do to activity in DB (“database in use”) or “No such file or directory” Perform the following:

On the LSF Server

Window Services:  Stop the IBMWASXXService – LSFAPP – This prevents users from accessing Portal

Window Services:  Stop Lawson.insightEnvironment “PRODLINE” –  stops LID to disconnect LID users

Task Manager:  End all java processes

Windows Services:  Start Lawson.insightEnvironment “PRODLINE” – starts LID

In LID, run the reorg manually:  dbreorg PRODLINE

Run the ACTIVATE step again

 

Copy User Jobs/Reports to Another User

This scenario may be familiar for you if you’ve been living in the Lawson world.

Let’s say Sarah got promoted or transferred to a new position. The first thing we do is assign Sarah her new security access, but all the Lawson jobs/reports that she used to run no longer pertain to her new position. Rather, Sarah needs a new set of jobs to run and instead of creating them one by one from another user in her new department, we can simply copy them over from another user.

delusers

By the name of the command, you may think we are deleting users, but nope, there is a hidden gem inside.

First, lets login to LID and then type the delusers command and press enter

Copy User Jobs Reports to Another User_1

You’ll now see a list of your users >> Press F8

Copy User Jobs Reports to Another User_2

You’ll now be able to Copy reports and jobs:

Copy User Jobs Reports to Another User_3

Make sure you select “Yes” for Reports and/or Jobs.

Any existing jobs/reports that have the same name from the user you’re copying from will be prompted to rename the jobs/report before continuing.

Enjoy your new jobs/reports!

How to Implement SSL for Lawson Portal

image

If you haven’t already done so, implementing SSL after the install is a bit of a black art. Without going into gory detail, here’s a very simple set of steps to follow:

  1. On the LSF server turn off all the services related to lawson aside from ADLDS
  2. Import your new certificate (preferably a wildcard cert) into windows as a personal cert
  3. Create a binding within IIS using the imported certificate on port 443
  4. Load up  your favorite ldap editing tool. We prefer this one.
  5. Under O=lwsnrmdata -> OU=resources you’ll find all your users and services. You’ll want to edit the following identities (or more if you have other service URLs):
    • BPM
    • IOS
    • IOSAdmin
    • LSAdmin
    • mingle
    • mingle_env
    • SSO
    • SSOP
    • Environment
  6. In each of the cases above you’re going to modify the Service URL and any other http protocol. You’ll also want to change the PROTOASSERT attribute from “Use HTTP only” to “Use HTTPS always”.
  7. Then change every relevant entry in %LAWDIR%/system/install.cfg that refers to http, protoassert, or the secure ports. They’re relatively easy to find.
  8. You can now reboot the LSF server and restart your services.
  9. If you have Landmark installed, then bring up the rich client
  10. In the GEN productline, navigate to: “Security System management” > Services
  11. Change every service to HTTPS_ONLY and change the service properties to HTTP Port=-1 and HTTPS Port=443
  12. Change all the relevant entries in system/install.cfg
  13. Reboot the Landmark server
  14. Run all the smoke tests with updated URL to verify everything is working
  15. If you are using inbaskets you’ll want to import your certificates into Websphere as well but that’s a topic for another article

How to Setup Your 2012 Server for Easy Administration

Administering Lawson on Windows has traditionally required using the basic toolset within windows to get things done. With Windows 2012 it seems like some of the very basic tools users are used to are hidden away and require several extra clicks to get to. Here are some quick ways to make Windows 2012 easier to deal with:
Method 1
Install a 3rd party tool called Classic Shell found here: http://www.classicshell.net
This is by far the easiest way to get Windows 2012 to act more like Windows 2008.
How-to-Setup-Your-2012-Server-for-Easy-Administration_1
Method 2
Since installing 3rd party applications like Classic Shell is not going to appeal to every client. Here’s a quick way to get at least your programs menu back without the help of any 3rd party applications:
Steps (4 total)
  1. Display “Hidden items” on your C: Drive – Open File Explorer and browse to your C: Drive. On the View tab, check the “Hidden items” checkbox.
  2. Add a New Toolbar on your Taskbar. – Right-click on a blank area of your Taskbar and select Toolbars > New Toolbars.
  3. Browse to the Start Menu\Programs folder. – In the New Toolbar dialog box, browse to the “C:\Program Data\Microsoft\Windows\Start Menu\Programs” folder.
  4. Click the “Select Folder” button. – Click the “Select Folder” button to add the new Toolbar to your Taskbar.