Learn how to connect to your CMDB asset inventory database on Microsoft SQL Server to audit your protected and unprotected resources
The Bocada Cloud Inventory CMDB (Configuration Management Database) connector collects asset data from a variety of different inventory sources, including CMDB data stored in a Microsoft SQL Server. By adding your asset inventory to Bocada Cloud, you can take advantage of Unprotected Resource Audit reporting. Follow the step-by-step instructions below to connect Bocada Cloud to your inventory in MS SQL.
1. Create the Connection in Bocada Cloud
Log into Bocada Cloud and on the left navigation menu, select Connections > +Add to add a new connection. Then, click the Inventory (CMDB) tile.
Note: In Bocada Cloud, the configuration interface is unified across all CMDB connector types. All integration methods use the same configuration fields, with relevant fields displayed based on the selected connector type.
2. Download and Install the Bocada Cloud Data Collection Agent
Next, you will need to download and install the Bocada Cloud Data Collection Agent in the private network where your Microsoft SQL Server resides. The agent will connect to it to collect data locally and send it to Bocada Cloud for Resource Audit reporting.
Follow the prompts on the screen to: (1) give the agent a name, (2) download and install it,
(3-4) copy a unique Authentication key and paste it into the agent to register it, and then (5) test that the connection between the agent and Bocada Cloud is working correctly.
Note: We recommend installing the agent on the Microsoft SQL Server where the CMDB database is located to simplify the connection.
3. Add Connection Details
After you have successfully installed the Bocada Cloud Data Collection agent, the next screen will ask you add configuration details for the connection.
a. Organization
Select which Organization you want to associate the data from the Microsoft SQL Server connection. If you have not set up more than one Organization, it will default to your main Organization (Billing Entity for your Bocada Cloud account).
b. Connection Name
Enter a name for this connection, such as the name of the MS SQL Server. The name does not need to be an exact match, but we recommend using a name to easily associate the data collected from this CMDB inventory in the UI and reports.
c. Product
Select Microsoft SQL Server from the product dropdown menu.
d. Database Authentication
Choose the authentication method to connect to the database (SQL or Windows).
e. Username and Password
If using SQL authentication, enter the SQL Server database user used to connect to the CMDB database. If using Windows authentication, enter the username in the format Domain\Username. Then enter the password for the user you have entered.
f. SQL Server Name
Enter the SQL Server hostname or IP address where the CMDB database resides. Use 'localhost' if the Bocada Cloud Data Collection Agent was installed on the same machine as the CMDB database.
g. SQL Instance
Enter the named SQL instance (if any) on the SQL Server. Append a port if needed (e.g., VEEAMSQL2016:1234).
h. SQL Database
Enter the name of the CMDB database to connect to on the SQL Server.
i. SQL Query String
Enter the SQL query that will run to pull the CMDB data. The query should return one row per asset and include consistent column names for mapping.
When configuring the SQL Query string, any values retrieved from the data source that are listed in the Column Name field in the table below will be committed to the Bocada database. Values in columns not listed here will be ignored.
Note: SQL comments break the query on some ODBC drivers.
Items marked 'KEY' are used to match backup clients in Bocada with assets aka Configuration Items (CIs). All values are in string/character format.
| Type | Column Name | Display Name | Description |
| REQUIRED KEY | clientfqname | CMDB: Client Name | Required name to cross match with client name in Bocada database. |
| KEY | clientname | CMDB: Alt Name | Additional backup client name, e.g. backup name or short name used to cross reference CMDB name with Bocada backup client name |
| REQUIRED | clientrecordid | CMDB: Client Identifier | CMDB CI unique identifier, e.g. sys_id |
| INFO | contact | CMDB: Contact | Info |
| INFO | contactemail | CMDB: Contact Email | Info |
| INFO | backupproductname | CMDB: Backup Product | Info |
| INFO | backupproductversion | CMDB: Backup Product Version | Info |
| INFO | osname | CMDB: Operating System | Info |
| INFO | osversion | CMDB: Client OS Version | Info |
| INFO | passexp | CMDB: Password Expiration | Info |
| INFO | ipaddress | CMDB: IP Address | Info |
| INFO | ipaddressbackup | CMDB: IP Address2 | Info |
| INFO | lastaccessdate | CMDB: Last Accessed Time | Info |
| INFO | commissioneddate | CMDB: Commissioned Date | Info |
| INFO | decommissioneddate | CMDB: Decommissioned Date | Info |
| INFO | backuprequired | CMDB: Backup Required | The default value is True. It's set to False for the following values: False, 0, N, No Any other value is set to True. |
| INFO | tag | CMDB: Tag | Info |
| INFO | cmdb_ci_name | CMDB: Configuration Item (CI) | Info |
| INFO | cmdb_company | Client info: CMDB Company | Info |
| INFO | cmdb_account | Client info: CMDB Account | Info |
| CUSTOM | custom1 | CMDB: Custom Field 1 | Custom informational field displayed in reports |
| CUSTOM | custom2 | CMDB: Custom Field 2 | Custom informational field displayed in reports |
j. SQL Query Timeout
Enter the maximum time (in seconds) to wait for the SQL query to complete. Increase the default value of 7200 seconds if you expect long running queries.
k. Time Zone
Enter the time zone where the MS SQL Server resides.
Test the Connection
Click the Next button to test the connection.
If the connection is successful, Bocada Cloud will begin collecting asset data from the CMDB database on the Microsoft SQL Server to populate the Resource Audit report.
If the connection is unsuccessful, you will be able to click the “Previous” button to edit your connection details and try again.
If you have issues connecting to your CMDB database inventory on MS SQL Server after trying these steps, create a Support Ticket in Bocada Cloud, or contact us.