Managing Microsoft SQL Server Database Connections

FlexNet Manager Suite 2022 R2 (On-Premises)

Connections to Microsoft SQL Server databases may be needed for integration with several different inventory sources, as described with the Source Type attribute of the connection settings.

Start this process from the inventory beacon interface.

To manage Microsoft SQL Server database connections:

  1. Select the Inventory systems tab in the FlexNet Beacon interface.
  2. Choose either of the following:
    • To change the settings for a previously-defined connection, select that connection from the list, and click Edit.... (For an SQL Server connection, the Edit SQL Source Connection dialog opens.)
    • To create a new connection, click New.... (The Create SQL Source Connection dialog opens.)
      Tip: The New... button defaults to creating a connection for Microsoft SQL Server. If you use the down arrow on the split button, you can choose between SQL Server, Spreadsheet, PowerShell, and Other connections. However, while you are creating a connection to a Microsoft SQL Server database (regardless of the Source Type of the connection), use only the SQL Server option.
  3. Complete (or modify) the values in this dialog, as follows:
    Control Comments

    Connection Name

    A descriptive name for this connection that you will recognize later in lists. The name may contain alphanumeric characters, underscores or spaces, but must start with either a letter or a number.

    Source Type

    Choose an adapter type from the list, keeping in mind that your choice must match the back-end data storage. As well as the defaults described here, any custom connection types configured for you by Flexera consultants (automatically updated on all your inventory beacon servers) also appear in this list. The default values and intended purposes are:
    • Altiris

      Use for all Symantec™ IT Management Suite (formerly Altiris) inventory.

    • App-V Standalone
      Use for direction connection to the Microsoft App-V application server to import App-V applications and usage details.
      Note: Use of the App-V Standalone connector disables the import of App-V inventory from Microsoft Endpoint Configuration Manager (previously Microsoft SCCM). This prevents degrading inventory about these applications.
    • BigFix Platform
      Use to import inventory from BigFix platform (previously known by a variety of names, including Tivoli Endpoint Manager and IBM BigFix).
      Tip: If you use this connector and if your BigFix platform implementation is running on a Microsoft SQL Server back end, connect to the database called BFEnterprise.
    • Blade Logic Client Automation

      Use to import inventory from this BMC product (formerly known as Marimba).

    • BMC Atrium Discovery and Dependency Mapping

      Use to import inventory from BMC Discovery.

      Note: Requires installation of an intermediate Microsoft SQL Server database, typically on the inventory beacon server, for temporary storage. For more information, see the documentation included with the download of the BMC Atrium adapter.
    • Citrix XenApp (EdgeSight)

      Choose this to specify your EdgeSight server if you are collecting application usage data from Citrix XenApp versions 6.0 or 6.5.

      Tip: If you have an EdgeSight server available, and your XenApp servers (6.x) stream applications from a central file share to deliver to customers, you may like to create two separate connections, one for EdgeSight and one for the XenApp server agent. For streamed applications, EdgeSight does not report key application details needed by FlexNet Manager Suite for application recognition. The XenApp server agent in contrast, is able to retrieve such details from the streamed application’s .profile file. FlexNet Manager Suite automatically merges the data received from both sources to ensure usage of applications streamed to your XenApp servers is recognized.
    • Citrix XenApp (Server Agent)

      Use this to take inventory of applications available through Citrix Virtual Apps (formerly XenApp). This requires installation of an intermediate Microsoft SQL Server database, possibly on the inventory beacon server, for staging data. The connection details are to this staging database. For more information, see coverage of the XenApp server adapter in FlexNet Manager Suite Inventory Adapters and Connectors Reference.

    • Data Platform v5
      Use to connect to BDNA Data Platform (formerly from BDNA, and now from Flexera).
      Important: In the connection details for the SQL Server database, be sure to enter the database name, authentication, and connection string details for your Normalize database, which forms part of Data Platform.
      This connection imports software and hardware inventory from your Data Platform (Normalize) version 5 implementation into FlexNet Manager Suite. As with all inventory imports, overlapping device records are merged, and new records are created for inventory devices not previously known in FlexNet Manager Suite.
      Tip: For software records, FlexNet Manager Suite only imports these two record types:
      • Applications recognized in both Normalize and the Application Recognition Library (ARL) that are installed within your enterprise. After import, these appear in application listings, such as the All Applications page; and on the Evidence tab of the application properties, they have a Type of FlexeraID. As usual, the installation records are reflected in the Devices tab of the application properties.
      • All other applications recognized in Data Platform as installed are imported as unrecognized installer evidence, because they are not [yet] recognized applications in your downloaded ARL. These include:
        1. Applications known in Normalize, and also known there to possess a Flexera ID (these cases, highlighted in the System Heath Dashboard, are updated in FlexNet Manager Suite at the next ARL download, and in future imports thereafter are recognized as applications)
        2. Applications known in Normalize but without a Flexera ID (as the work of mapping applications proceeds, future updates to both Data Platform and the ARL will gradually move these into the set of recognized applications)
        3. Application records created locally within your enterprise (in FlexNet Manager Suite they are called "local applications", and in Data Platform were known as "private applications"), which are unknown in both Normalize and the ARL, so that you need to recreate these application records manually within FlexNet Manager Suite.
      Any unrecognized installations (unknown applications) identified in Data Platform are not imported.
    • Exchange ActiveSync

      Use this for importing mobile inventory from Microsoft Exchange ActiveSync (without using Microsoft Endpoint Configuration Manager (previously Microsoft SCCM)).

      Note: Requires the presence of a staging database, possibly installed on the inventory beacon.
    • HP Discovery and Dependency Mapping Inventory (DDMI)

      Use for imports from the Hewlett-Packard Discovery and Dependency Mapping Inventory tool.

      Note: Requires the presence of a staging database, possibly installed on the inventory beacon.
    • HP Universal Discovery
      For imports from the Hewlett-Packard Enterprise Universal Discovery tool (formerly HP Universal Discovery).
      Note: Requires the presence of a staging database, possibly installed on the inventory beacon.
    • ILMT

      Use for imports from IBM License Metric Tool (ILMT) or TAD4D or BigFix Inventory when these products are using a DB2 database.

    • ILMT SQL
      Use for imports from IBM License Metric Tool (ILMT) or TAD4D or BigFix Inventory when these products are using a Microsoft SQL Server database.
      Tip: In a standard implementation, SQL Server may offer two database names for connection:
      1. TEMADB – use this database when using ILMT or BigFix Inventory imported through this connection type
      2. BFEnterprisedo not use for this connection type (instead see the BigFix Platform connection type).
      Note: Inventory devices marked as deleted in ILMT are not imported into FlexNet Manager Suite except when both of the following are true:
      • ILMT is the source of truth for your sub-capacity PVU license consumption calculations
      • The inventory device was only marked as deleted within the last 90 days (so that for part of the current reporting period, it may have affected the peak consumption figures).
      A 'deleted' inventory device imported under these conditions is automatically removed again in the next reporting period, when it is no longer relevant for peak calculations.
    • ManageSoft

      Use for imports from legacy ManageSoft, or Flexera Inventory Manager, databases within your enterprise.

    • SCCM
      Use for imports from Microsoft Endpoint Configuration Manager (previously Microsoft SCCM), or legacy Microsoft SMS, databases.
      Important: To collect data saved by Microsoft Endpoint Configuration Manager, the account on the inventory beacon that is running the connection to the SQL Server database requires membership in the db_datareader role.
      Tip: If you encounter an issue that the EXECUTE permission is denied on some Microsoft Endpoint Configuration Manager database object when performing Microsoft Endpoint Configuration Manager imports, you can solve this issue by adding the service account to the smschm_users role in your Microsoft Endpoint Configuration Manager database. The smschm_users role grants the account the EXECUTE access permission to the Microsoft Endpoint Configuration Manager database.
    • Technopedia
      Use to import normalized application data from the Flexera Technopedia library, such as application publisher, product name, release, version, and edition. Technopedia's imported data can then be matched against the existing data in your Application Recognition Library.
      Tip: This option is available only in FlexNet Beacon release 15.0.0 or later.
    Type the server name or IP address where the database is hosted. Use the special value (localhost) if the database is installed on this same inventory beacon server. If the database instance you need is not the default one on the server you identify, add the instance name, separated with a backslash character. Example:
    Select one of:
    • Windows Authentication — Select this option to use standard Windows authentication to access the database server. The credentials of the account (on the inventory beacon) running the scheduled task for importing inventory are used to access the SQL Server database. This account must be added to an Active Directory security group that has access to the database.
    • Windows (specific account) — Use the following two fields (enabled when you make this choice) to specify an account on the inventory beacon that can make a connection to the SQL database.
    • SQL Authentication — Use the following two fields to specify an account and password registered as a user with database access on SQL Server . This account is used to access the database, regardless of the local account running the scheduled task on the inventory beacon server.
    Tip: The account used needs read-only privileges.
    Username The account name used for SQL authentication, or Windows (specific account). (Not required for Windows Authentication.)
    Password The password for the account name required for SQL authentication, or Windows (specific account). (Not required for Windows Authentication.)
    Database Enter the name of the database, or use the pull-down list to select from database names automatically detected on your specified server. For example, for a connection to Technopedia, select BDNA_Publish from the drop-down list.
    Connection is in test mode (do not import results)
    For inventory beacons that are free-standing (and not co-located with your application server, or in larger implementations your inventory server), this controls the upload of data collected from this connection and its import to FlexNet Manager Suite:
    • When this check box is clear, the connection is in production mode, and data collected through this adapter is uploaded to the central server and (in due course) imported into the database there.
    • When the check box is set:
      • The adapter for this connection is exercised, with data written to the intermediate file in the staging folder on the inventory beacon (%CommonAppData%\Flexera Software\Beacon\IntermediateData)
      • The immediate upload that normally follows data collection is suppressed, so that you can inspect the contents of the file
      • The catch-up process that retries stalled uploads, normally scheduled overnight, runs as usual and uploads the file to the central server
      • At the central server, the file contents are discarded (and not imported into the central database).
    Tip: When an inventory beacon is co-located with your application server, data from the connection is read directly into staging tables in the database, and no intermediate file is saved.
    Overlapping Inventory Filter
    If you use more than one inventory source, it is possible to get overlapping inventory (records about the same endpoint device from multiple inventory tools). Because of differences between inventory tools, the overlapping inventory records may contain slightly different data. In the web interface (in FlexNet Manager Suite), you may nominate one inventory source as Primary, which gives its collected data priority for hardware properties imported from target inventory devices. The choices here give another, separate axis of control, based on whether or not the data from this particular source is to be considered "stale". This is different from the Primary setting in the following ways:
    • It is assessed first (before the primary setting is taken into account), and so may even modify the effect of your Primary setting. It is best practice to make sure that your chosen primary inventory source is not marked as stale with the following settings.
    • It affects not only imported hardware properties, but also software inventory (installer evidence, file evidence, and so on), as follows:
      • With two inventory sources (when neither one is considered stale), the total software inventory is a union (merging) of the results from both sources. This is useful when two different inventory tools have different specializations for software detection: the union means you are not blind-sided by a software inventory tool that missed something you should have licensed. This is considered the 'normal' operational case in a stable environment using multiple inventory sources.
      • When one of the two available sources is declared "stale" (using either of the first two choices below), all of its overlapping software inventory is excluded from the possible union of data sources (hardware inventory too, but here we're considering software). This behavior is valuable, for instance, when a target inventory device has migrated from one inventory source to another (perhaps by moving offices), but has not yet been obsoleted from this first source. Imagine that, as part of the office move, the MyApp application had also been uninstalled from the inventory device. You do not want the old and stale record from this source insisting there's an installation of MyApp when in fact it's no longer on the device. Of course, best practice is to obsolete the device from this stale source so that it is no longer reported in this inventory source; but these first two settings allow a quick control of all the overlapping data from this source, rather than having to obsolete devices one by one in the source tool. Another common scenario is when you are migrating over time from an old version of your chosen inventory tool to a newer version (on a new connection), and during transition both systems are still running. Declaring the old version's connection as "stale" means that as soon as a record appears in the new version's inventory for a particular device, the old device import is automatically superseded, and updates rely entirely on the new version of your inventory tool.
    Each of the following settings only takes affect when FlexNet Manager Suite is comparing the inventory dates of a device from this current inventory source and an overlapping record from another inventory source:
    • Ignore the device’s inventory from this data sourceWhen you have more recent inventory from another source for the same target inventory device, the record from this source is completely ignored. (Technically, the device record is deleted from the staging table in the database, and so can never be imported.)
    • Ignore this device’s inventory if older than nn days — If you select this option, overlapping inventory collected by this source more than the set number of days before the import is ignored. Fresher overlapping data is still imported and considered for data merging.
      Tip: In the interests of keeping inventory current, this control has a maximum value of 60 days.
    • Import the inventory from this source for possible merging — Choose this option (the default) to declare that overlapping inventory collected from this connection is never considered stale. This is the normal operating setting when you are not trying to manage transitions from one inventory source to another. With this setting, overlapping records are merged in this way:
      1. If a data point exists in the Primary inventory source, it is used
      2. If two equal-priority sources have different inventory dates, the data point is taken from the most recent inventory
      3. As a tie-breaker, the connection ID for this source recorded in the database is used (normally meaning that the earliest-created inventory source has priority).
  4. Click Test Connection.
    • If the inventory beacon can successfully connect to the nominated database using the details supplied, a Database connection succeeded message displays. Click OK to close the message. Click Save to complete the addition. The connection is added to (or updated in) the list.
    • If the inventory beacon cannot connect, a Database connection failed message is displayed, with information about why that connection could not be made. Click OK to close the message. Edit the connection details and retest the connection.
    You cannot save the connection details if the connection test fails. If you cannot get the connection test to succeed, click Cancel to cancel the addition of these connection details.

FlexNet Manager Suite (On-Premises)

2022 R2