An interesting customer requirement that I thought was worthy of sharing today. Thanks to James for the requirement and Bryan for the proposed solution and me for testing the solution and proving it works :0)
A customers implementation of Sarbanes Oxley requires them to change all database account passwords every 90 days. This is scripted leveraging shell scripts today for most of their environments. But how can they manage the BI Publisher connections?
Now, the customer is running 11g and therefore using weblogic on the middle tier, which is the first clue to Bryans proposed solution. To paraphrase and embellish Bryan's solution a little; why not use a JNDI connection from BIP to the database. Then employ the web logic scripting engine to make updates to the JNDI as needed? BIP is completely uninvolved and with a little 'timing' users will be completely unaware of the password updates i.e. change the password when reports are not being executed.
Perfect! James immediately tracked down the WLST script that could be used here, http://middlewaremagic.com/weblogic/?p=4261 (thanks Ravish)
Now it was just a case of testing the theory. Some steps:
Create the JNDI connection in WLS
Create the JNDI connection in BI Publisher pointing to the WLS connection
Build new data models using or re-point data sources to use the JNDI connection.
Create the WLST script to update the WLS JNDI password as needed.
Test!
Some details.
Creating the JNDI connection in web logic is pretty straightforward.
Log into hte console and look for Data Sources under the Services section of the home page and click it
Click New >> Generic Datasource
Give the connection a name. For the JNDI name, prefix it with 'jdbc/' so I have 'jdbc/localdb' - this name is important you'll need it on the BIP side.
Select your db type - this will influence the drivers and information needed on the next page. Being a company man, Im using an Oracle db.
Click Next
Select the driver of choice, theres lots I know, you can read about them I just chose 'Oracle's Driver (Thin) for Instance connections; Versions 9.0.1 and later'
Click Next >> Next
Fill out the db name (SID), server, port, username to connect and password >> Next
Test the config to ensure you can connect. >> Next
Now you need to deploy the connection to your BI server, select it and click Next.
You're done with the JNDI config.
Creating the JNDI connection on the Publisher side is covered here. Just remember to the connection name you created in WLS e.g. 'jdbc/localdb'
Not gonna tell you how to do this, go read the user guide :0) Suffice to say, it works.
This requires a little reading around the subject to understand the scripting engine and how to execute scripts. Nicely covered here. However a bit of googlin' and I found an even easier way of running the script. ${ServerHome}/common/bin/wlst.sh updatepwd.py
Where updatepwd.py is my script file, it can be in another directory. As part of the wlst.sh script your environment is set up for you so its very simple to execute.
The nitty gritty:
Need to take Ravish's script above and create a file with a .py extension.
Its going to need some modification, as he explains on the web page, to make it work in your environment. I played around with it for a while but kept running into errors.
The script as is, tries to loop through all of your connections and modify the user and passwords for each. Not quite what we are looking for. Remember our requirement is to just update the password for a given connection. I also found another issue with the script. WLS 10.x does not allow updates to passwords using clear type ie un-encrypted text while the server is in production mode. Its a bit much to set it back to developer mode bounce it, change the passwords and then bounce and then change back to production and bounce again.
After lots of messing about I finally came up with the following:
#############################################################################
#
# Update password for JNDI connections
#
#############################################################################
print("*** Trying to Connect.... *****")
connect('weblogic','welcome1','t3://localhost:7001')
print("*** Connected *****")
edit()
startEdit()
print ("*** Encrypt the password ***")
en = encrypt('hr')
print "Encrypted pwd: ", en
print ("*** Changing pwd for LocalDB ***")
dsName = 'LocalDB'
print 'Changing Password for DataSource ', dsName
cd('/JDBCSystemResources/'+dsName+'/JDBCResource/'+dsName+'/JDBCDriverParams/'+dsName)
set('PasswordEncrypted',en)
save()
activate()
Its pretty simple and you can expand on it to loop through the data sources and change each as needed. I have hardcoded the password into the file but you can pass it as a parameter as needed using the properties file method. Im not going to get into the detail of that here but its covered with an example here.
Couple of points to note:
1. The change to the password requires a server bounce to get the changes picked up. You can add that to the shell script you will use to call the script above.
2. The script above needs to be run from the MW_HOME\user_projects\domains\bifoundation_domain directory to get the encryption libraries set correctly. My command to run the whole script was: d:\oracle\bi_mw\wlserver_10.3\common\bin\wlst.cmd updatepwd.py
- where wlst.cmd is the scripting command line and updatepwd.py was my update password script above.
I have not quite spoon fed everything you need to make it a robust script but at least you know you can do it and you can work out the rest I think :0)
