-
Title
Can I access the SMA's database using a Third-party reporting tool? -
Description
The KACE SMA database can be accessed from any ODBC-compliant third-party tool if you have installed the (32bit) MySQL ODBC driver or MySQL Workbench.
-
Resolution
You will need to turn on Database Access in System > Settings > Security Settings
The (32bit) MySQL ODBC driver can be found using this link:Version 8.3.0
Version 5.1 (use this for Microsoft Access):*NOTE* when using the "Enable Secure database access (SSL)" option for the SMA, the below versions of the (32bit) MySQL ODBC driver have also been reported as successful in conjunction with SQL Workbench:
Download the Windows version as needed. (Versions for Linux are also available; however, these instructions are for the Windows OS)
After installing the driver, you must configure an ODBC connection to the KACE SMA using these steps:
1. Open Control Panel
2. Open Administrative tools
3. Open ODBC Data Sources (OBDC)
4. click Add
5. Pick the MySQL 3.51 driver (second to last in most cases)
6. Configure the connection using these details:- Name: Add your desired name for the connection
- Server: SMA's IP Address or FQDN.
- Username: R1 (If you have organizations enabled, the report name and database will be different for connecting to each organization in kind. To verify the report username for each organization, go to System > Organizations > [Organization Name])
- Password: Add the password set up here. The database(reporting) password can be changed in System UI Settings > General Settings > Reporting Password
- Database/ Schema/ Catalog: ORG1 (or ORG2...ORG#)
7. This will then add the data source using the name of KACE SMA
Connection information for the KACE SDA appliance can be found here.
Please note that any network settings change will require a KACE SMA/KACE SDA reboot! Lastly, if your queries in 3.3 made reference to database objects using the database name in front then you will need.
These steps also apply similar to other solutions available, such as HeidiSQL, PowerBi, etc. For authentication and connection, follow step 6 and forward.
Other compatible tools examples can be found in the link below:
Graphical and Enhanced Clients - MariaDB Knowledge Base