Options for ODBC Login Credentials

When using an SQL database as an input source, the SQL objects need to have the DSN and login credentials to access the data. This applies to Integrator when using a SQL input and Spectre when using odbc-input or pgsql-input for Builds and Dives. To avoid putting credentials in plain text inside a script, explore the options for externally storing and securing credentials.

NOTE: You can set Workbench access control so some users can view and edit scripts, while only allowing the user account that runs DI batch processes to read the data source definition and actually run the script.

Reasons to externalize credentials

Even a simple external text file containing ODBC data source connection information (including login credentials) has these advantages:

  • Prevents users from accidentally knowing the password when they are editing a script.
  • Allows updates to passwords without requiring changes to scripts.
  • Facilitates application of OS user account controls to prevent users from seeing the file with the credentials.

Using the DSN Configuration

A common method of externalizing login credential information from scripts is to put them into the DSN configuration. This allows the database and the OS (via user account controls) to be the gatekeepers. How this is achieved depends on the ODBC driver. For example:

  • Microsoft SQL Server (MS-SQL)—presents the option to save the credentials in the configuration screen.
  • Oracle's ODBC driver—allows you to include the credentials in the form <username>/<password>.
  • Linux—lets you add the credentials to the DSN definition file (odbc.ini), typically located in the /etc directory. Set the system file permissions to protect the file so that only the user account that runs DiveLine can read it.

Using the Integrator Connect File

If saving credentials in your ODBC or SQL configuration is not adequate security in your environment, you can use the input's connect_file attribute.

  1. Open a command prompt and navigate to the directory where integ.exe is installed. For example,

    C:\di\solution\live\diveline\bin

  2. Enter the following:

    integ -save_connect_file <filename>.cfile

    Where <filename> is the name for your connect file.

  3. The command returns the following and prompts you to enter the connect string.

    Data Integrator 7.1 (5) 64-bit

    Copyright (C) 1991-2019 by Dimensional Insight, Inc

    Licensed to: Dimensional Insight

    Enter connect string:

  4. Enter the connect string with the following format:

    • ODBC—DSN=<dsn name>;uid=<database username>;pwd=<database password>

    • PostgreSQL—host=<host name> port=<post number> dbname=<database name> user=<database username> password=<database password>

  5. The connect file is saved to the same directory where you ran integ.exe.

When the file is created, the information is saved in a binary file so the contents cannot be recovered without substantial effort.

NOTE: The DI generated connection file is not cryptographically secure. DI recommends that you set the operating system file permissions to restrict access.

Using Production

Another option is to use Production. For example:

  • Store credentials in an external file, in a separate directory from scripts, with strict OS file system permissions.
  • Scramble or encrypt the contents using an external tool.
  • Read the file in with a Production Parameter node.
  • Add a step to unscramble the password from the stored text file.
  • Pass the parameters to the Integrator script expecting parameters for credentials.

NOTE: When retrieving data, encryption of ODBC connections to the remote database server is handled by configuration of the database server and the ODBC driver. It varies according to the database in use and the ODBC driver in use. Check the vendor documentation.

See: