Powershell: Oracle, Exchange, secure string.

Apr 23 2013

Powershell: Oracle, Exchange, secure string.

We will describe in this post how to connect to an Oracle database using Powershell cmdlets, this database hosts HR data. We will check if every employee has an Exchange 2010 mailbox. The common key between both systems (Oracle and Exchange) has to be a filterable attribute for performance issues when querying the Exchange mailboxes. Exchange’s attribute is CustomAttribute13 in our example and the corresponding Oracle value is HRCODE.

In order to query the Oracle database with Powershell you need to install ODAC 64bits on a Windows 64bits machine. Launch the setup by installing only the ODP.net provider that matches your .NET Framework version. In our example:

install.bat odp.net2 c:\oracle myhome true

Now you can connect to the Oracle database by calling the Oracle.DataAccess.dll assembly brought by ODAC-64bits:

$Assemblyfile = "C:\oracle\odp.net\bin\2.x\Oracle.DataAccess.dll"
[Reflection.Assembly]::LoadFile($AssemblyFile) | out-null

Read this article for further information.

We do not want to leave the database connection credentials in plain text in the script. To avoid this we will use a secure string, the encrypted cred.txt file will be generated with the account launching the Exchange scheduled task.

If the file is read by another account, it will fail:

Here is how to generate the cred.txt file:

read-host -assecurestring "pass" | convertfrom -secure string | out-file c:\cred.txt

We now launch the connexion to the Oracle database:

#Read $Pword from file.
$Pword = get-content cred.txt | convertto-securestring
$user = "adm"
$Credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User, $PWord
 
$OracleConnection = New-Object -TypeName Oracle.DataAccess.Client.OracleConnection
 
#Change host, Port, Service_Name values
$connectionString = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=ldap389-oraclesrv)(Port=1532)))(CONNECT_DATA=(SERVICE_NAME=HR1)));User ID="+$credential.GetNetworkCredential().username+";Password="+$credential.GetNetworkCredential().password
$OracleConnection.ConnectionString = $ConnectionString
$OracleConnection.Open()

We look for the employees starting 3 days from now, to launch the right query we play with date formats.

#Date in format MM/DD/YYYY HH:MI:SS, +3 days from now
 
$date = Get-Date((get-date).addDays(+3)) -format "MM/dd/yyyy hh:mm:ss"
#Get users starting in 3 days in HR database, change table (LDAP389FAMILY), and attributes: HRCODE is customAttribute13
$CommandText  = "SELECT HRCODE,START_DATE FROM LDAP389FAMILY WHERE START_DATE BETWEEN SYSTEMDATE AND to_date('"+$date+"', 'MM/DD/YYYY HH24:MI:SS')"
 
#Launching query
$OracleCommand = New-Object -TypeName Oracle.DataAccess.Client.OracleCommand
$OracleCommand.CommandText = $CommandText
$OracleCommand.Connection = $OracleConnection
$OracleDataAdapter = New-Object -TypeName Oracle.DataAccess.Client.OracleDataAdapter
$OracleDataAdapter.SelectCommand = $OracleCommand
$DataSet = New-Object -TypeName System.Data.DataSet
$OracleDataAdapter.Fill($DataSet)
$OracleDataAdapter.Dispose()
$OracleCommand.Dispose()
$OracleConnection.Close()
$Oracleresults = $DataSet.Tables[0]

The scheduled task connects to the Exchange system as described in this previous post. It is launched with the account which has the authorization to read the secure string:

#With this command you do not need to install the Exchange Management Shell on the server, change the fqdn Cas-server.ldap389.local
 
$s = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri http://Cas-server.ldap389.local/PowerShell/
Import-PSSession $s -allowclobber

When creating a scheduled task, credentials set up to run the task are stored in LSAsecrets. You can improve security by using a gMSA under Windows 2012: The account has limited privileges and the password automatically updated, the password hash is also stored in LSAsecrets.

The script checks wether the HRCODEs retrieved from the Oracle database match a CustomAttribute13 of any Exchange mailbox or not. Wether the mailbox is already created or not, the relevant information is displayed:

foreach($Oracleresult in $Oracleresults)
{$customAttr13 = $Oracleresult.HRCODE
 
#Check if mailbox was created:  
$mbx = get-mailbox -filter "(CustomAttribute13 -eq '$customAttr13')"
	if($mbx){
	write-host '--------------------------------------------------'
	write-host 'MBX already created'
	write-host 'Exchange information'
	$mbx | select CustomAttribute13,WindowsEmailAddress,whencreated
	write-host 'HRCODE: '$customAttr13
	write-host 'Start date: '$Oracleresult.START_DATE
	}
	#Mailbox is not created:  
	else{
	write-host '--------------------------------------------------'
	write-host 'Create MBX ASAP'
	write-host 'HRCODE '$customAttr13
	write-host 'Start date: '$Oracleresult.START_DATE
	}
}

In order to download the full script just click on the link bellow:

No more excuses for creating a mailbox two weeks after an employee has started working at your company 😉

This post is also available in: French

Tags: Exchange, Oracle, PowerShell

Filed in scripts, security, tools | ldap389

2 Comments

By Name.ToString(), July 3, 2016 @ 3:05 pm

Hmmm I would reconsider storing the password as a secure string. A secure string, to my understanding, is basically an obfuscation of the password. It can be reversed to plaintext.
By ldap389, January 28, 2017 @ 12:41 am

It is not obfuscation, it is encryption according to MSDN https://msdn.microsoft.com/en-us/library/system.security.securestring(v=vs.110).aspx
But the user who encrypted the string has the key, so you just need to impersonate the user or dump the memory to retrieve the decryption key…

ldap389

Leonard Savina - @ldap389