Skip to main content

ODBC script

Connector name

  • agtodbcscript

  • agtodbcscript-32

Connector type

Executable with configuration SQL script

Type (UI field value)

  • ODBC Database (Script)

  • ODBC Database (32-bit) (Script)

Target system versions supported / tested

Bravura Security Fabric can use Open Database Connectivity (ODBC) to access data from a variety of database management systems using the scripted connector for ODBC (agtodbcscript or agtodbcscript-32 ). Supported database management systems include: DB2, Oracle Database, Microsoft SQL Server, and SAP (Sybase) Adaptive Server Enterprise (ASE). You can also use the ODBC connector to target CSV file systems.

Connector status / support

Bravura Security-Verified

This connector has been tested and is fully supported by Bravura Security.

Installation / setup

Write a script file to define SQL commands used in the interaction between the ODBC connector and the ODBC-based database.

No connector software is installed on the ODBC-based database application server or the back end database.

Bravura Security Fabric can perform operations on ODBC-based database management systems. The ODBC connectors uses ODBC client software to bind to the ODBC-based database server and either directly updates user records (for example, SQL UPDATE) or calls stored procedures.

The following Bravura Security Fabric operations are supported by this connector:

  • user verify password

  • get server information

  • user change password

  • administrator reset password

  • administrator reset+expire password

  • expire password

  • unexpire password

  • administrator verify password

  • verify+reset password

  • enable account

  • disable account

  • check account enabled

  • create account

  • delete account

  • lock account

  • unlock account

  • check account lock

  • expire account

  • check account expiry

  • unexpire account

  • add user to group

  • delete user from group

  • create group

  • delete group

  • add group to group

  • remove group from group

  • move contexts

  • rename account

  • update attributes

  • list account attributes

  • List:

    • accounts

    • attributes

    • groups

    • members

For a full list and explanation of each connector operation, see Connector operations.

This connector also supports custom operations, as defined in the configuration script.

When targeting a CSV file system using the ODBC scripted connector, only the read-only list operations are supported.

Preparation

Before Bravura Security Fabric can manage accounts on an ODBC-based database management system, you must:

  1. Install the client software.

  2. Either:

    • Define a data source.

      or

    • Find installed ODBC drivers.

  3. Configure a target system administrator.

  4. Create at least one template account to provision accounts for this target.

  5. Write a script to configure connector behavior

Installing client software

Bravura Security Fabric communicates with the ODBC-based database management systems via APIs provided by the ODBC client. Before you target an ODBC-based database management application, install and configure the ODBC client software on the Bravura Security Fabric server.

Defining a data source

Bravura Security Fabric can target an ODBC database hosted application either using the data source name or via the driver. If using a data source name, you must specify this in the Server parameter in the target address. To define the data source:

  1. Start the ODBC data source administrator tool (odbcad32.exe).

    Depending on your system, this is located at:

    • c:\Windows\System32\odbcad32.exe (64-bit)

    • c:\Windows\SysWow64\odbcad32.exe (32-bit)

  2. Select the System DSN tab.

    The ODBC data source administrator tool displays a list of data sources.

    If a data source does not exist for the ODBC-based database management system that you want Bravura Security Fabric to target, click Add… and create a new data source. See your Windows or ODBC-based database management software help for more information. If you are targeting CSV files, see Preparation for targeting CSV files using the ODBC connector .

  3. Note the system data source name (DSN). You will need this name when adding the target to Bravura Security Fabric . This is usually the same as the ODBC-based database name.

  4. ( Optional) Test the connection to the target system database.

  5. Click OK to close the ODBC data source administrator tool.

See your ODBC-based database management documentation for more information on configuring the ODBC client software.

Finding installed ODBC drivers (Optional)

Bravura Security Fabric can target an ODBC database hosted application via an ODBC driver or a data source. If connecting to a driver, the driver name and connection parameters are required in the ODBC driver and Connection string settings in the target address. To find drivers:

  1. Start the ODBC data source administrator tool.

  2. Select the Drivers tab.

You can also find installed ODBC drivers in the following registry entries:

  • 32-bit ODBC drivers:

    HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBCINST.INI\ODBC Drivers

  • 64-bit ODBC drivers:

    HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers

Configuring a target system administrator (Optional)

Bravura Security Fabric uses a designated account (for example, psadmin) on the ODBC-based database target system to manage accounts passwords. The target system administrator requires ODBC-based database back end privileges that grant execution of SQL commands used in the written script.

Ensure that you set and note the account’s password. You will be required to enter the login ID and password when you add the ODBC-based database target system to Bravura Security Fabric .

This step is optional if the target system administrator is same as the database user or login id used in the ODBC data source configuration in previous step.

Normally connectors run on the Bravura Security Fabric server under the service account that runs the application services. If the connection to the target database files (for example on a network share) requires the connector to run using the target administrator credentials, check the Run as option.

Creating a template account

Bravura Security Fabric uses template accounts as models or "blueprints" for creating new application accounts in an ODBC-based database managed application.

The steps required to create a template account depend on your application. Consult your systems administrator or application documentation for more information.

Writing a script to configure connector behavior

Write a script file to define SQL commands used in the interaction between the ODBC connector and the ODBC-based database.

Learn about writing script files for SQL application connectors.

Preparation for targeting CSV files using the ODBC connector

Bravura Security Fabric provides two methods for targeting CSV files: the CSV connector (agtcsv) and the ODBC connector (agtodbcscript). Determining which method to use depends on:

  • The flexibility of the CSV format. Can it be modified or not?

  • The need to write back out to the CSV files. Are the CSV files read-only or do they also need to be updated by Bravura Security Fabric .

  • Do columns within the CSV need to be combined to create unique or meaningful data for Bravura Security Fabric ? For example are two columns required to make a unique key?

  • Do the CSV files exist on a remote share or are they local to the Bravura Security Fabric server?

  • Is object data and attributes spread across multiple CSV files or is it self-contained in a single CSV file?

Choose the CSV connector (agtcsv) if:

  • You have a requirement to write data back out to the CSV files from Bravura Security Fabric .

  • Your CSV files exist on a remote server and a persistent drive mapping cannot be established.

  • Your CSV file format is flexible and columns can be added in order to satisfy the requirements of the CSV connector’s configuration.

Choose the ODBC connector (agtodbcscript) if:

  • You do not require the ability to write data back out to the CSV files (read only).

  • Your CSV files exist locally on the Bravura Security Fabric servers, or can be accessed remotely via a persistent drive mapping.

  • Your CSV file format is unchangeable, and the connector must be able to join, merge, and manipulate columns of the CSV file to extract the appropriate data.

Attribute and object data is spread across multiple CSV files.

The rest of this section shows you how to target CSV files using the ODBC connector. To learn how to target CSV files using the CSV connector, see FLAT FILE SYSTEMS > CSV file .

Defining a data source for CSV file systems

The ODBC connector can perform operations with CSV files by leveraging the "Microsoft Text Driver (*.txt; *.csv)" ODBC driver. This driver is available from Microsoft in both 32-bit and 64-bit versions. Depending on the version of the driver you have installed, use either the 64-bit or the 32-bit ODBC connector.

To target CSV files via the ODBC connector you must first configure an ODBC source using the "Microsoft Text Driver (*.txt; *.csv)" ODBC driver:

  1. Start the ODBC data source administrator tool (odbcad32.exe).

    Depending on your system, this is located at:

    • c:\Windows\System32\odbcad32.exe (64-bit)

    • c:\Windows\SysWow64\odbcad32.exe (32-bit)

  2. Click the System DSN tab, then Add...

  3. Select the "Microsoft Text Driver (*.txt; *.csv)", and click Finish.

  4. Give your data source a name and description.

  5. Deselect Use Current Directory and select the folder location of your CSV files. If a network drive is required, it must be a persistent drive mapping with the same drive letter.

  6. Click Options > Define format... to configure the schema for each CSV file. From here you can define:

    • Whether the CSV contains column headers or not

    • The delimiter (if not a comma)

    • The character set (see UNICODE CSV files for UNICODE support)

    • The columns, data types and lengths for each CSV file

    The Guess button generally gets the columns correct; however if the CSV file includes a byte order mark (BOM) then you need to manually override the columns.

  7. Click OK to close the ODBC data source administrator tool.

The define format process above creates a schema.ini file along side the CSV files. This is a plain text file that can be edited afterwards for additional configuration. The format of the schema.ini file is:

[fileone.csv]
ColNameHeader=True|False
Format=CSVDelimited|Delimited(<char>)
MaxScanRows=25
CharacterSet=ANSI|OEM|<CodePage>
Col1=COLUMN_NAME <DataType> Width <FieldLength>
Col2=COLUMN_NAME <DataType> Width <FieldLength>
...
ColN=COLUMN_NAME <DataType> Width <FieldLength>
[filetwo.csv]
....

UNICODE CSV files

The ODBC data source administrator tool has a couple of limitations when it comes to supporting UNICODE. The editor does not properly detect/handle the byte order mark (BOM) that is present in many UNICODE files. Nor is UNICODE an option for the character set in the editor.

To support UNICODE CSV files, you need to manually override the settings in the schema.ini file.

For UTF-8 files you must:

  1. Manually change the CharacterSet= key-value in the schema.ini to:

    CharacterSet=65001

  2. Manually define the Columns' names and data types since the Guess option does not properly handle the BOM.

CSV sample files

A sample set of CSV files, schema.ini file and ODBC configuration is provided with the ODBC connector. This can be used directly or as a reference for defining your own CSV ODBC data source. The included fies are:

  • The CSV files which include groups, group memberships, computers, users, and subscribers samples.

    • agtodbcscript_sample_groups.csv

    • agtodbcscript_sample_members.csv

    • agtodbcscript_sample_servers.csv

    • agtodbcscript_sample_subscribers.csv

    • agtodbcscript_sample_users.csv

  • Schema definition file for the ODBC data source for each of the above CSV files:

    agtodbcscript_sample_schema.ini

  • Configuration file for the ODBC connector which targets the above CSV files:

    agtodbcscript-csv.cfg

Targeting an ODBC database hosted application

For each ODBC-based database system, add a target system (Manage the system > Resources > Target systems):

  • Type is:

    • ODBC Database (Script) for 64-bit data sources.

    • ODBC Database (32-bit) (Script) for 32-bit data sources.

  • Address uses syntax described in the table below.

  • Administrator ID and Password is the login ID and password for the target system administrator you configured in earlier.

    The target system credential is optional if the ODBC data source contains the password needed for connection.

    Check the Run as option to use the target credentials to run the connector.

The full list of target system parameters is explained in Target system options .

Table 1. ODBC-based database address configuration

Option

Description

Options marked with a redstar.png are required.

Server redstar.png

ODBC-based database system DSN or database server.

See Defining a data source to learn how to find the ODBC system DSN for your ODBC-based database application. Use the server name if connecting via ODBC Driver.

(key: server)

Instance

Optional script variable instance name.

(key: instance)

Script file redstar.png

The script file must be in the <Program Files path>\Bravura Security\Bravura Security Fabric\<instance>\ script\ directory and describes the SQL commands used in the interaction between the connector and the database.

(key: script)

ODBC driver

The ODBC driver used to access the ODBC-based database. If this value is empty, the Server parameter is treated as the ODBC system DSN, otherwise, the Server parameter is treated as the database server to connect to.

(key: driver)

ODBC connection string

Specifies options to use with ODBC Driver. It consists of a series of keyword/value pairs separated by semicolons:

<keyword1>=<value>; <keyword2>=<value>

(key: params)



The ODBC target system address syntax is as follows when using the system DSN:

{server=<system DSN>;[instance=<script_variable_instance>;]script=<script filename>;}

The ODBC target system address syntax is as follows when connecting to the database driver:

{server=<DatabaseServer>; [instance=<script_variable_instance>;]script=<script filename>; driver=<ODBC driver>;[params=<keyword1=value;...>]}

Handling account attributes

In order for Bravura Security Fabric to manage attributes, you must first add the attributes to Bravura Security Fabric.

See Account attributes in the Bravura Security Fabric configuration documentation for more information.

Troubleshooting

Data source not found ...

This is likely due to a mismatch between the version of the connector and the ODBC data source administrator tool that was used. Ensure that if a 32-bit data source is created that the 32-bit ODBC connector is used, and if a 64-bit data source is created that the 64-bit ODBC connector is used.

[SQLExecute] returned error [Code [07002] message [[Microsoft][ODBC
Text Driver] Too few parameters. Expected 1.] native error [-3010]].
Failed to perform operation [list].

This is likely caused by the use of an incorrect column name in the ODBC connector configuration script. Ensure that all selected columns are correctly specified in the configuration.

[SQLPrepare] returned error [Code [42000] message [[Microsoft][ODBC
Text Driver] Syntax error in FROM clause.] native error [-3506]].
Failed to perform operation [list].

This is likely caused by the use of special characters in the filename of CSV files. There are some limitations to the allowed characters in the filename for the CSV files. Dashes in particular are not allowed and cause the above error to be returned. Changing the dashes to underscores (or removing them) solves this problem.

[SQLExecute] returned error [Code [42S02] message [[Microsoft][ODBC Text Driver] File
      '<filename>.dbf does not exist.] native error [173]].  Failed to perform operation
      [list].

This is likely caused by insufficient permissions for access to the target database files. You may need to use the Run as option to use the target credentials to run the connector. You can also check the target administrator's privileges for the location of the files used by the target.

In this section: