In part one we installed and configured the Oracle client software, in this post we will query an Oracle database from Powershell. In addition we’ll look at one way to handle storing sensitive password information.

Querying and Oracle Database

To query an Oracle database we’ll use a function called Get-OLEDBData. The code listed below and is also available on PoshCode:

The Get-OLEDBData function has been tested against SQL Server, Informix, Oracle and Excel data sources. In addition other data source can be addressed all that is needed is a valid connection string, the ability the data source to support OLEDB connections and of course the appropriate drivers. See connectionstring.com for a list of connection string examples. The hard part of querying an Oracle database from Powershell is setting up the Oracle Client software demonstrated in part one. Using the Get-OLEDBData function is simple, just pass a connection string and a query as follows:

As long as your Oracle client software is installed and configured correctly and you have a valid connection string, specifying a database user with sufficient rights the query works. One issue immediately apparent is the sensitive password information. This especially true if you intend to use this technique for automated batch jobs. To address the password issue we’ll need to encrypt the connection string and the store the password somewhere. Let’s a look at one solution…

Encrypting Connection Strings

Ideally everything would use Windows authentication and you wouldn’t need to store password information. The reality is this simple isn’t the case especially with Oracle databases. Unfortunately there aren’t any native encryption cmdlets in Powershell (I’d love to see a cmdlet that would use certificates in order to avoid pass phrases), there are however a very nice and set of Powershell encryption functions created by Steven Hystad called Library-StringCrytpo.

To use the encryption functions download the Powershell script and source the library, then call the Write-EncryptedString function passing our connection string we want to encrypt with a passphrase. To decrypt the connection string call the Read-EncryptedString function with the encrypted string and passphrase.

The encrypt functions work well, but I like to do is then take the encrypted string and store it in a SQL Server table that is locked down. To do we’ll need to first create a table in SQL Server database as follows:

Note: I've created the server_lku table on a server named win2k8r2\sql2k8 in the database dbautility

In order to select and insert data into the server_lku table we’ll use the Get-SqlData and Set-SqlData functions from the LibrarySqlData script which I previously blogged about here.

Source LibrarySqlData , and insert the encrypted string along with few other pieces of information into our SQL table using the Set-SqlData function. To retrieve the encrypted connect string use the Get-SqlData function and then call the Read-EncryptedString function.

Chad Miller is a Senior Manager of Database Administration at Raymond James Financial. Chad has worked with Microsoft SQL Server since 1999 and has been automating administration tasks using Windows Powershell since 2007. Chad is the Project Coordinator/Developer of the Powershell-based Codeplex project SQL Server PowerShell Extensions (SQLPSX). Chad leads the Tampa Powershell User Group and is a frequent speaker at users groups, SQL Saturdays and Code Camps.