Configuring ODBC data sources

How to use Open DataBase Connectivity (ODBC) to set up and configure ODBC Data Sources with BigFix.

The BigFix components that use a database connect to it using Open DataBase Connectivity (OBDC).

To open an ODBC connection, you need:
  • An ODBC driver, a piece of software allowing your application call the ODBC APIs of the database engine.
  • An ODBC data source, the information identifying the target database and detailing the connection parameters.

There can be several ODBC drivers installed on the same computer and several ODBC data sources saved on it. Often, the data source also specifies what driver to use to connect to the target database.

Each ODBC data source is identified by a Data Source Name (DSN), not to be confused with a DNS (Domain Name System), which has a similar acronym but is an entirely different thing.

ODBC on Windows

On Windows, an ODBC data source can be 32-bit or 64-bit. There can be two sources with the same name if one is 32-bit and the other is 64-bit.

ODBC data sources with the same name but different bitness often refer to the same database and have the same connection parameters, except for the path of the ODBC driver they specify.

In fact, ODBC drivers often have a 32-bit dll for 32-bit applications and 64-bit dll for 64-bit applications.

ODBC data sources are saved in the registry:

  • 32-bit ODBC data sources appear in HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI
  • 64-bit ODBC data sources appear in HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI

You can edit them directly or configure them using these dedicated tools:

  • Start > Windows Administrative Tools > ODBC Data Sources (32-bit)
  • Start > Windows Administrative Tools > ODBC Data Sources (64-bit)

The BigFix Platform components use these ODBC data sources on Windows:

  • enterprise_setup, used by the installers, especially during upgrades
  • bes_bfenterprise, used by the BigFix Server to access its database
  • LocalBESReportingServer, used by Web Reports to access its database

All the above ODBC data source names have a corresponding 32-bit source and a 64-bit source.

In total there are 6 BigFix data sources (3 pairs).

The BigFix Administration Tool (BESAdmin) usually relies on the same ODBC data source as the BigFix Server, but it may use a different data source depending on the command it runs.

The BigFix WebUI does not use ODBC data sources and saves its connection data internally.

If you want to change a BigFix ODBC data source, remember to:

  • Update both the 32-bit and 64-bit versions of the data source.
  • Ensure that the corresponding settings in other data sources remain consistent.

If you do not keep all data sources aligned, you may encounter database connection issues when using certain BigFix functions but not while using others.

For example, if you change bes_bfenterprise and forget to update the corresponding information in enterprise_setup, you might only find out during the BigFix pre-upgrade checks.

BigFix components might connect to databases hosted on SQL Server either:
  • Via Windows authentication, which relies on the Windows credentials of the user running the application process.
  • Via SQL Server authentication, which relies on separate credentials, unrelated to Windows user credentials.

When a BigFix component uses Windows authentication, the user running its service is the same that will access the database. This is true for all components except for the BigFix WebUI.

When connecting to a local database, BigFix uses Windows authentication by default.

When connecting to a remote database, BigFix can be configured to use Windows authentication or SQL Server authentication.

This image shows the ODBC keys of a computer with both the BigFix Server and Web Reports installed.
ODBC keys in the Windows registry

Changes introduced in Patch 8

Given some differences in how the two drivers can be configured, any customization of the BigFix ODBC data sources done prior to upgrading to Version 10.0.8 might no longer work as expected after upgrading to Version 10.0.8. Therefore, if starting from a non-default configuration, after upgrading to Version 10.0.8, it is recommended to review and verify the consistency and effectiveness of the BigFix ODBC data source configurations.

ODBC examples for Windows

Example of ODBC configuration up to Patch 7

This configuration code shows the 64-bit and 32-bit ODBC keys of a computer with the BigFix Server and Web Reports using a local database and configured to use Microsoft SQL Server Native Client for the connection.

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI]

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\bes_bfenterprise]
"Database"="BFEnterprise"
"Driver"="C:\\Windows\\system32\\sqlncli11.dll"
"Registration"="System Data Source"
"Server"="(local)"
"Trusted_Connection"="Yes"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\enterprise_setup]
"Driver"="C:\\Windows\\system32\\sqlncli11.dll"
"Registration"="System Data Source"
"Server"="(local)"
"Trusted_Connection"="Yes"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\LocalBESReportingServer]
"Database"="BESReporting"
"Driver"="C:\\Windows\\system32\\sqlncli11.dll"
"Registration"="System Data Source"
"Server"="(local)"
"Trusted_Connection"="Yes"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]
"enterprise_setup"="SQL Server Native Client 11.0"
"bes_bfenterprise"="SQL Server Native Client 11.0"
"LocalBESReportingServer"="SQL Server Native Client 11.0"

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI]

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\bes_bfenterprise]
"Database"="BFEnterprise"
"Driver"="C:\\Windows\\SysWOW64\\sqlncli11.dll"
"Registration"="System Data Source"
"Server"="(local)"
"Trusted_Connection"="Yes"

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\enterprise_setup]
"Driver"="C:\\Windows\\SysWOW64\\sqlncli11.dll"
"Registration"="System Data Source"
"Server"="(local)"
"Trusted_Connection"="Yes"

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\LocalBESReportingServer]
"Database"="BESReporting"
"Driver"="C:\\Windows\\SysWOW64\\sqlncli11.dll"
"Registration"="System Data Source"
"Server"="(local)"
"Trusted_Connection"="Yes"

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\ODBC Data Sources]
"enterprise_setup"="SQL Server Native Client 11.0"
"bes_bfenterprise"="SQL Server Native Client 11.0"
"LocalBESReportingServer"="SQL Server Native Client 11.0"

The "Trusted_Connection" registry value is set to "Yes", which means it is using Windows authentication. If that value is missing, the connection defaults to SQL Server authentication.

Example of ODBC configuration from Patch 8 and later

This configuration code shows the 64-bit and 32-bit ODBC keys of a computer with the BigFix Server and Web Reports using a local database and configured to use Microsoft SQL Server Native Client for the connection.

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI]

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\bes_bfenterprise]
"Database"="BFEnterprise"
"Driver"="C:\\Windows\\system32\\msodbcsql17.dll"
"Registration"="System Data Source"
"Server"="(local)"
"Trusted_Connection"="Yes"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\enterprise_setup]
"Driver"="C:\\Windows\\system32\\msodbcsql17.dll"
"Registration"="System Data Source"
"Server"="(local)"
"Trusted_Connection"="Yes"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\LocalBESReportingServer]
"Database"="BESReporting"
"Driver"="C:\\Windows\\system32\\msodbcsql17.dll"
"Registration"="System Data Source"
"Server"="(local)"
"Trusted_Connection"="Yes"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]
"enterprise_setup"="ODBC Driver 17 for SQL Server"
"bes_bfenterprise"="ODBC Driver 17 for SQL Server"
"LocalBESReportingServer"="ODBC Driver 17 for SQL Server"

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI]

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\bes_bfenterprise]
"Database"="BFEnterprise"
"Driver"="C:\\Windows\\SysWOW64\\msodbcsql17.dll"
"Registration"="System Data Source"
"Server"="(local)"
"Trusted_Connection"="Yes"

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\enterprise_setup]
"Driver"="C:\\Windows\\SysWOW64\\msodbcsql17.dll"
"Registration"="System Data Source"
"Server"="(local)"
"Trusted_Connection"="Yes"

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\LocalBESReportingServer]
"Database"="BESReporting"
"Driver"="C:\\Windows\\SysWOW64\\msodbcsql17.dll"
"Registration"="System Data Source"
"Server"="(local)"
"Trusted_Connection"="Yes"

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\ODBC Data Sources]
"enterprise_setup"="ODBC Driver 17 for SQL Server"
"bes_bfenterprise"="ODBC Driver 17 for SQL Server"
"LocalBESReportingServer"="ODBC Driver 17 for SQL Server"

ODBC on Linux

On Linux, the concept of an ODBC data source is more vaguely defined and database connection settings are set by interacting with the DB2 Client and driver, usually via the command line.