RG069 – Setting up a Lab HR system for IT Store

By Max Ranzau

 

This article is to show you how you can stand up your very own free HR system and hook it up with IT Store. You may even have seen this particular system in a demo before in the past, as it’s quite popular with the RES presales teams as well. Just to be clear, this is far from SAP/HR, but a free web-based open-source Apache/MySQL/Pearl based HR System, called OrangeHRM (nope, besides the color there’s nothing in common with RES or Donald Trump :). Since I am in the process of building out a new Cloudlab these days, I figured I might as well take the time to share how to stand up this component as it may come in handy for everyone involved.

xor-logoOrange HRM is essentially a website like IT Store itself, just running off the Apache webserver instead. This means it can run on top of Linux and MacOS as well. In this article I will just cover the Windows installation, as there is a couple of quirks you need to know about to get it up and running in one smooth go. You can run the OrangeHRM server off most versions of Windows. Excluded are Windows XP and Server 2003.

 

Step 1: Gathering the components.

  1. First, you will need to download the OrangeHRM system. It’s only about 9MB. There are two downloads, a self-extractor and a ZIP file. In the past I’ve encountered some weird issues with the SFX thus I recommend you download the ZIP version. There are download links pages here and here.
  2. In order to run Orange HRM, you’ll need MySQL, Apache and Pearl. All these are wrapped up in a 150MB package called XAMPP. Download link is here. I pulled down the latest version at the time of writing (5.5.19), which does the job nicely. Note: You really want to stick with this version:  As of March 2016, if you are installing on Server 2016, do not go for newer versions of XAMPP as they’re likely to give you the dreaded blank-browser-page problem in the initial OrangeHRM setup
  3. For IT Store to be able to interface with the OrangeHRM MySQL database, it’s necessary to download the MySQL ODBC drivers as these aren’t part of Windows. Download drivers here. Be sure to download the MSI version and the proper bit-version for your environment.
  4. The MySQL ODBC driver install can be a bit fickle to work with. The installer handles like something out of the early 90’s and indeed it actually needs an old Microsoft DLL, MSVCR100.DLL in order to install properly. Grab the bitversion you need here: x64 or x86.
  5. You’ll optionally also want to pull down the MySQL equivalent of the MS SQL Studio, so you can muck around in the innards of the OrangeHRM database and explore the tables. You’ll want to download the MySQL Workbench, which can be found here. You’ll need the Visual C++ Redist package as a prereq as well for the MySQL Workbench. The VC2013 redist can be found here.

 

Step 2: Installing XAMPP

  1. First, go to the computer where your OrangeHRM system is going to live. I’d recommend a clean server 2008 and up, however if you are pressed for server real estate in your lab, you may opt to slap it on top of an existing server. If said server already is running IIS, the Apache server can co-exist with it. However you’ll need to tell it to run http and https on other ports than respectively tcp/80 and tcp/443. This also means you’ll have to include the port number in the URL to access OrangeHRM later. Also the Tomcat (javahost) will come up and complain if you are running the Service Store Catalog Server, as both uses tcp/8080. See RES port reference here. I’ll show you how to remap the Apache, but I’m leaving Tomcat out, as we don’t need it for OrangeHRM anyway. I trust you see my point in using a clean server now.
  2. Start by launching the XAMPP package, xampp-win32-*-installer.exe.
  3. Update: If you install on Server 2012 the package will prompt you about turning off UAC. Presuming you won’t have a problem doing that on a backend server, check this article on how to turn UAC off properly.
  4. xampp-inst2XAMPP will prompt you for what items you’ll want to install. Just unselect Tomcat as shown on the right You get a Filezilla FTP server as well as a Mercury mailserver thrown in as well, and those might perhaps come in handy later for alerts and whatnot. Hit Next.
  5. The XAMPP install directory defaults to C:\xampp. Go with this unless you have explicit reasons not to and generally know what you’re doing. The installer notes warns of some potential permission issues if you install to %programfiles%. Hit Next.
  6. We don’t need additional items on top at this point, so untick the ‘Learn more about Bitnami for XAMPP’ and hit Next.
  7. The setup takes a few minutes to complete. Sometimes it may sit on the last few percent of the progress bar for a few minutes, appearing to do absolutely nothing. Your milage may vary.
  8. At finish the XAMPP installer will ask you if you want to launch the XAMPP control panel. Say yes to the dress and it will appear as an orangy icon in the system tray and launch after a few moments. Note; newer versions of the XAMPP installer will at this point prompt you to choose a language. Choose the stars & stripes undless you vant tze softvare zu deutchen getalken..

xamppcpl1

As you can see above, we need to do some tinkering to get Apache and Tomcat running. However, OrangeHRM doesn’t use Tomcat, so you can safely ignore it. On the other hand I don’t like loose ends, so I suggest you disable Tomcat by removing the red X in the box next to it as shown.

xampstartNext thing to do is to ensure Apache and MySQL starts automatically upon boot. Hit the config button in the upper right corner of the XAMPP control panel and check the boxes for these two components. Next time the server boots, they’ll come back up by themselves. You may also want to check the “Start Control Panel Minimized” box while you’re at it to prevent screen clutter.

Last, if you took my advice (and of course you did, right? :) -and installed XAMPP on a clean server, without IIS and RES ITS Catalog server, you should be able to skip the next step and proceed to step 4 below.

 

Step 3 – Configuring Apache to run on alternate ports.

  • Obviously, Apache refuses to start due to the port conflicts on tcp/443 with my IIS. It also complains about tcp/80 further up although it’s not evident from the screenshot. Hit the config button and select Apache (httpd.conf), which opens up a notepad with the config file for the Apache http daemon.
  • apacheconfig1In the config file, scroll down to around line 58 and look for the Listen 80 entry, change the number to your desired (non conflicting port). In my case I chose 90. Save and exit, but dont try to Apache just yet as we need to take care of tcp/443 as well.
  • Back in the XAMPP Control Panel, hit the config button again, and select Apache (httpd-ssl.conf). Same deal as above. Scroll down to around line 36 and look for the Listen 443 entry. I changed mine to 444. Save and exit.
  • If you have a port conflict for MySQL’s TCP/3306 then the procedure to fix that is exactly the same. I’m presuming that you don’t have that issue.
  • serviceport-configYou will also need to configure these ports in another place, if you are going to run Apache and MySQL as services. In the XAMPP controlpanel, hit the Config button in the upper right, then hit the Service and Port setting button, and enter the alternate ports for main port and SSL.
  • To test that Apache and MySQL can start, just hit the start buttons and they should launch with green status.
  • You’ll want run both as NT services. To do this, stop them again for a moment. Then hit the red X to the left of each of them and say yes to installing the service. Then start them again. If you’ve done everything right, your XAMPP control panel should look something like this.

xampp configured

 

Step 4: Installing OrangeHRM

  • You can close the XAMPP control panel at this point.
  • Now that Apache and MySQL are up and running, we’ll need to install the OrangeHRM website: Unzip the contents of the previously downloaded orangehrm-3.2.1.zip into a temporary location (there’s 6000+ files in there, so it may take a few). A folder called orangehrm-3.2.1 will be created. It will be next to a file named orangehrm-quick-start-guide.html
  • Rename this folder to hr.
  • Move this folder to c:\xampp\htdocs
  • Open a browser and navigate to http://localhost:<portnumber>/hr. In my case that would be http://localhost:90/hr. Again this business with the port number could have been avoided if you installed on a server without conflicting ports.
  • At this point you should be seeing the OrangeHRM setup screen, which will walk you through the database setup:

orangehrmsetup1

Step 5: Configuring the OrangeHRM database

  1. Hit next to get started and accept the license agreement.
  2. xora-setup1On the Database configuration screen, leave everything as you found it, unless of course you in the meantime on your own have changed the root password for MySQL, which is blank (!!) per default. Relax, this is for a lab setup. In here we can get away with both murder and blank passwords :) Note: At this point you can’t specify another host besides localhost, since all external access must first be enabled in MySQL. Besides, it’s really not an issue since Apache and MySQL live on the same box in this case. See Step 6
  3. ohrm_eventWhen you hit next, the OrangeHRM installer will perform a Systems Check to verify that everything is kosher and give you a summary. On this screen you may likely see the warning “MySQL Event Scheduler status: Disabled. This is required for automatic leave status changes of Leave module.” You can opt to ignore this warning if you’re not going to use mentioned feature, but if you’re persnickety like me and want it fixed, you can launch the MySQL command line interface like described in section 7 further ahead, steps 1 and 2. Then type exactly (with semi-colon at the end too): SET GLOBAL event_scheduler = OFF; For more info see this article.
  4. Back on the OrangeHRM installation page, hit the Recheck button. If everything is SuperGreen, then go ahead and hit next.
  5. Setup the password for the OrangeHRM Admin. This is the account you’ll be logging into the HR system with initially until you create other users. Hit next, confirm the settings and start the installation.
  6. After a few moments you will be asked if you want to register. This is entirely optional as the software is free. Once you hit the Finish button, you will be taken back to http://localhost:<port>/hr, which now presents a logon screen where you can login with the Admin account. Once you’re in, it should look like this:

xor-mainscreen

From here on out, it’s really all up to yourself how you want to structure your virtual company in the HR system. I’m confident there are plenty of tutorials available on how to setup an organization in OrangeHRM, but that goes beyond the scope of this article. However I do suppose you want to get some employees and departments created as a bare minimum. The next step is to establish the ability to talk with the OrangeHRM database inside the MySQL DBMS.

Step 6: Installing the MySQL ODBC drivers:

  1. Since Windows doesn’t include MySQL drivers per default, we’ll have to download those and install them. Where do you install them? Presuming you have your ITS Console, Transaction Engine and Catalog Server on separate computers, you would need to install the ODBC drivers on the computers running the Console (edit: Sync & Setup tool) and the Transaction engine as these components talk to the external datasouces you define in ITS. If you in your lab have all the ITS components on one box it’s fine as well.
  2. First, we need to solve a prereq issue for installing the ODBC drivers: Extract the msvcr100x<bitversion>.zip you downloaded in step 1, then place the extracted msvcr100.dll file in %systemroot%\system32.
  3. Next, install the mysql-connector-odbc-5.3.4-winx<bitversion>.msi you downloaded in step 1. Just breeze through it, using default options.
  4. odbcdriversThe ODBC drivers should now be properly installed. To verify, go to Administrative Tools | ODBC Data Sources (<bitversion>-bit) and check the Drivers tab. You should now see a couple of MySQL drivers available.
  5. Now let’s create a DSN to connect to from IT Store. Being fully aware of the virtues of User/File DSN’s versus System DSN’s, I’m completely ignoring this and just setting up a System DSN for ease of use.
  6. connectionPicking the Unicode driver over the old ANSI driver ensures compatibility with foreign character sets.
  7. In the Connection Parameters dialog, you can go with localhost for the TCP/IP server field, if both RES ITS and MySQL is on the same box. However since I recommended you initially to install XAMPP and OrangeHRM on a clean server, you will probably want to have the FQDN of that server in the ODBC connection info. However if you try to put anything else than localhost in, you will get a connection error. In order to deal with this, you need to do a bit of one-off tinkering on the MySQL box to allow it to be addressed by anything else than localhost. We will deal with that in the following section:

Step 7: Enable MySQL (and perhaps phpMyAdmin) remote access:

  1. Open up a command prompt in this location: C:\xampp\mysql\bin and run mysql.exe -u root -p
  2. This will prompt you for the password (which is blank) and then give you a mysql> prompt.
  3. Issue the following command to allow the root user access to the DBMS from a given host: GRANT ALL PRIVILEGES ON *.* TO root@yourserver.fqdn; (the semicolon is important as that signifies end of commandline! Also, yourserver.fqdn refers to the client host which will be connecting to the MySQL database server) Alternatively, f you want to completely remove host access restriction and combine it with a password, you can use this command: GRANT ALL PRIVILEGES ON *.* TO root@’%’ IDENTIFIED BY ‘passwordhere’; The % char is a wildcard for any host. Be careful with that. More info here.
  4. phpmyadminEither command will return something like Query OK, 0 rows affected. This is fine as we are in fact adding a record.
  5. Follow either command with this command to make it take effect immediately: flush privileges;
  6. Terminate the mysql command line tool with the command exit, where after you can close the command prompt.
  7. You can at any time check the rules that are in effect by going to http://server:port/phpmyadmin/, then navigating to mysql -> users as seen on the right.

Some field notes for step #7:

  • You do not have to specify port in the url above if you are running Apache on default ports 80,443 (check the XAMPP control panel to verify this)
  • If you need to access the /phpmyadmin site from another server, you’ll have to edit the httpd-xampp.config file. Easiest is to 1) go via the XAMPP control panel in the system tray, 2) stop Apache 3) hit the config button and select the httpd-xampp.config file, 4) comment out (with #) the last 4 lines where the <LocationMatch> section is and 5) restart Apache. Now you can hit the phpmyadmin site from anywhere.
  • If your reverse DNS lookup sucks, you may have to replace the hostname with an IP address inside the phpMyAdmin MySQL interface.
  • If you just want to wildcard access it, just replace the hostname with a percentage sign (%) which is the MySQL equivalent of an asterix wildcard.

finisheddatasourceYes… I’ll probably make a buildingblock for the MySQL remote access one fine day. Once we’re done with the MySQL tinkering, you can fill out the ODBC connection information properly and select the proper database called: orangehmr_mysql. Save the DSN, then it’s time to move onto reaching the data source from within the IT Store.

Step 8: Reading HR data into RES IT Store

  1. its-hr-datasourceFirst, let’s fire up the RES IT Store Console (again, this is the Setup & Sync thingymagoo these days) and go int the menu Data Model|Datasources. We will likely need to create more than one ITS Datasource in hee, as you’ll want to be reading multiple tables such as employees, departments, etc. You will need a ITS Datasource definition for each. There is also provision in the IT Store for creating SQL queries. This is discussed in detail in the IT STore Admin guide, on page 141.
  2. RES-IT-Store-2014-Administration-Guide For now, fill out a name, the description and pick out the System DSN we created before. Be sure to fill out the credentials as root/password (if any).
  3. You are now able to browse the tables from the HR System and can start importing relevant data into your IT Store’s identity warehouse.

To wrap things up, now you have a fully functional and completely free HR system at your disposal. When you combine this with the RES Suite you will have the ability to configure automatic onboarding and offboarding, but that’s a story for another day.

5 Comments

  • By fbifido, May 25, 2015 @ 19:28

    Hi,

    1) Step 7 #3,
    “root@yourserver.fqdn;”
    this “yourserver.fqdn”, which machine is this reference to ?
    is it the msql server or the RES server ?

    2) How does one update the XAMPP & OrangeHRM when a new version comes out ?

    Thanks.

  • By RESguru, May 26, 2015 @ 00:29

    Hi there, it’s the SQL server. You are granting access to the SQLadmin (root) to the database on the dbms. I’ve updated the article to reflect this. Thanks for the feedback! /Max

  • By fbifido, August 11, 2015 @ 07:30

    Hi,

    How do I setup a complete RES ONE Suite, IT Store, and more, in a virtual lab, using the latest MS software, like Windows Server 2012 R2, Windows 7/10, office 2013 pro plus.
    Thanks.

  • By RESguru, August 12, 2015 @ 06:30

    Reach out directly and I’ll be glad to help. My contact info is on the front page. Best regards,
    Max

  • By Neelima, November 30, 2016 @ 12:47

    Hi,

    How can I access orangeHRM from multiple systems? I mean the same database…!!!

Other Links to this Post

RSS feed for comments on this post. TrackBack URI

Leave a comment

Comments are welcome as always. Just do the math below. * Time limit is exhausted. Please reload the CAPTCHA.