VB Script - Embedded SQL queries - How to avoid disclosing password

3590471

Hello,

I'm trying to query a database from a VB script program.

To be more specific, I'm trying to query an HFM database from HFM business rules.

But it's almost irrelevant for this question, as it could apply to any query embedded in a VB script piece of code.

I'm using built-in ADODB objects, it's working fine.

But my problem is that I have to explicitly disclose my DB password in the code...

Here is a sample code. I removed sensitive information from it, hence the inputs starting with "some_".

Option Explicit

Dim xADOCon     'As Variant

Dim xADORS      'As Variant

Dim xFields     'As Variant

Dim xField      'As Variant

Dim sSQLQuery   'As String

Dim sRecord     'As String

Const sSQLConnection = "Provider=OraOLEDB.Oracle.1;Data Source=some_url/some_service_name:some_port_number;User ID=some_connection;Password=some_password;Persist Security Info=True"

'Setting objects for SQL connection

Set xADOCon = CreateObject("ADODB.Connection")

Set xADORS = CreateObject("ADODB.Recordset")

xADOCon.ConnectionString = sSQLConnection

xADOCon.Open

'Testing connection

wscript.Echo xADOCon.ConnectionString

'Querying

sSQLQuery = "SELECT * FROM some_table"

xADORS.Open sSQLQuery, xADOCon

'Instantiating fields

Set xFields = xADORS.Fields

'Browsing records

Do While Not xADORS.EOF

    sRecord = ""

    For Each xField In xFields

        sRecord = sRecord & xField.Name & ":" & xField.Value & "|"

    Next

    wscript.Echo sRecord

    xADORS.MoveNext

Loop

'Closing objects

xADORS.Close

xADOCon.Close

How could I get the password from somewhere so that I don't have to hard-code it?

It could be encrypted, as long as there's a decryption program :-)

I heard of Oracle Wallet but I don't know how to use that. I don't even know if I have it as part of my Hyperion suite.

(It's simpler in FDMEE as passwords are stored in the DB, and it comes with a Python library to decode passwords.)

Any idea?

Thank you very much,

Alexis