Secure Your Server-side EPM Automate with Encrypted Files

Server-side EPM Automate has become a powerhouse for streamlining administrative tasks, but just like its client-side counterpart, it faces the critical challenge of secure connection establishment to Cloud EPM applications. Traditionally, this has been achieved through two methods: password strings and encrypted password files. While both serve the purpose, a clear winner emerges when it comes to safeguarding your sensitive credentials – encrypted password files.

Keeping the Password Flame Alive: Avoiding the Purge with Regular Activity

If you’ve embraced encrypted password files, congratulations on taking a significant step towards securing your server-side EPM Automate environment! But hold on—there’s a potential pitfall lurking in the shadows: Oracle’s 7-day inactivity purge. Much like a flickering candle extinguished by a sudden gust, your carefully encrypted credentials could vanish if not used for a week, leaving your scripts stranded in darkness.

Here’s a tale from the trenches to illustrate this challenge:

In one of a previous engagement, we had crafted a manual admin sequence job designed to run only once a month. The first execution went smoothly, but upon attempting the second month’s run, a startling error emerged—the encrypted password file had vanished! The realization struck: Oracle’s purge mechanism had struck during the idle weeks, rendering the job incapable of fulfilling its duties.

The solution? A simple workaround:

To keep the password flame alive, we devised a clever script that performed a daily login and logout, akin to gently fanning the candle’s wick to preserve its glow. This ingenious approach circumvented the purge by ensuring regular activity, even in the absence of full-fledged job executions.

Unveiling a Hidden Gem: The Potential of EPM Connection Manager

As we’ve delved into the complexities of password management in server-side EPM Automate, a question naturally arises: “Why not simply harness the power of EPM Connection Manager, a tool already adept at securing REST API connections without exposing passwords?” It’s a compelling inquiry, one that merits exploration.

Indeed, EPM Connection Manager offers several enticing benefits:

• Password Obscurity: Credentials remain concealed within the tool’s secure confines, shielding them from prying eyes, including other service admins. This enhances security and confidentiality.
• Centralized Management: Password updates can be effortlessly managed within the tool, eliminating the need for tedious script modifications and reducing errors. This promotes efficiency and maintainability.
• Reusability: Once a connection is established using EPM Connection Manager, it can be seamlessly reused across multiple scripts, streamlining development and reducing redundant setup efforts.

Groovy Unlocks the Vault: Accessing Connection Manager Credentials

So, we’ve established the enticing potential of leveraging EPM Connection Manager within server-side EPM Automate scripts. But the question remains: how does Groovy, the scripting language that executes EPM Automate, interact with this connection manager? The answer may surprise you – it’s already equipped with the methods to unlock the vault! Thanks to the Groovy development Team.

EPM Groovy exposes a set of methods specifically designed to retrieve credentials from Connection Manager, effectively granting EPM Automate scripts authorized access to these secure connections. This eliminates the need for insecure password strings or creating manual encrypted files, offering a streamlined and secure solution.

Delving into the Details: Retrieving Credentials with Precision

Now that we’ve unlocked the gateway to Connection Manager credentials using Groovy’s dedicated methods, let’s dive into the specifics of how to extract the essential details we need – the username, password, and URL. These elements form the foundation for establishing secure connections within our server-side EPM Automate scripts.  

Here’s a closer look at the methods we’ll employ:

• getUserName(): This method retrieves the username associated with the specified connection, providing a clear identifier for authentication purposes.
• getPassword(): This method, rightfully encapsulated with the protected keyword, acts as the gatekeeper for the sensitive password. It safeguards this critical information, ensuring it’s not carelessly exposed within scripts.
• getUrl(): This method retrieves the URL of the target application, guiding our scripts to the correct destination for establishing the connection.

Step by Step process break down

1. Initial Login Attempt with Encrypted Password File:
   1. The script locates and accesses the existing encrypted password file.
   2. It attempts to use the stored credentials for login.
   3. If login is successful, the script proceeds with its intended tasks
2. Connection Manager Access (if initial login fails):
   1. Upon login failure, the script turns its attention to EPM Connection Manager
   2. It retrieves the latest credentials (username, password, and URL) from the specified connection definition
3. Encryption of Retrieved Password
   1. The script initiates the encryption process to securely store the password
   2. If encryption is successful, a new encrypted password file is generated
4. Reconnection Attempt with New Password File:
   1. The script attempts to log in again, this time utilizing the newly created encrypted password file
   2. If the reconnection is successful, the script resumes its intended tasks
5. Script Termination (if issues persist)
   1. If any errors or issues arise during the encryption or reconnection attempts, the script halts its execution

Script:

when it comes to retrieving credentials, custom functions hold the key in my example. While I won’t delve into the specifics of my own custom functions here, I invite you to explore their potential through the example below. Consider it a launchpad for crafting your own unique approach, tailored to your specific needs.

//define the connection name
Connection planning = operation.application.getConnection('localHost')
//login using the existing password file
EpmAutomate automate = getEpmAutomate()
EpmAutomateStatus loginStatus = automate.execute('login',getUserName(planning) ,'password.epw', getUrl(planning))
//check if the first login attempt is failed, if yes, then proceed to fetch password from connection manager
if(loginStatus.status != 0) {
	println "First Login Failed: Initiating password encryption from the Connection Manager"
	if(loginStatus.output.trim() == 'EPMAT-9:Invalid credentials') {
		EpmAutomateStatus encryptStatus = automate.execute('encrypt',getPassword(planning), generateEncryptionKey(), 'password.epw')		        
		if (encryptStatus.status != 0) {
			throwVetoException("Password Encryption Failed : ${encryptStatus.output}")
		}        
		//intiate second login attempt
		EpmAutomateStatus loginRetry = automate.execute('login', getUserName(planning), getPassword(planning),getUrl(planning))
		if(loginRetry.status != 0) {
			throwVetoException("Login Retry Unable to establish connection : ${loginRetry.output}")
		} else {
			println "Login Retry is successful: ${loginRetry.output}"
		}       
	}
} else {
	println "First Login Successful"
}

Job Console: Valid First Login

Job Console: Invalid First Login and New Password Encryption

Taming a jungle of connections? EPM Connection Manager to the rescue! It’s like a one-stop shop for your URLs and passwords, keeping everything neat and tidy.