How to set up a MySQL and MS SQL database for AIDA64?
MySQL and MS SQL are one of the several databases AIDA64 supports.
Creating and configuring a MySQL database
We need to install the MySQL service on our server (in this case, XAMPP), and then add a new user. We need to enter a user name and a password then select “Any host”. We can also create a database here with the same name and grant all privileges. Finally, we have to click on the “Create user” button.
We need to revoke any extra user privileges, preserving only those which they will require to perform modifications necessary for report creation. Global access rights to MySQL should be left as they are (USAGE), but we need to modify database-specific privileges, which we can do by clicking the “Edit privileges” link.
In the “Edit privileges: User 'aida64'@'%' - Database aida64” window we have to locate “Database-specific privileges” and click on “Uncheck all”, then in the “Data” column we need to check the privileges we want. We must select SELECT, INSERT and UPDATE in order to make report creation possible. If we also want to allow users to delete from the database, we can select DELETE.
To allow AIDA64 to use the database we need to initialize the tables. To do this, we have to copy the “DB - MySQL.sql” query from the “SQL_Schema” subfolder and run it on the newly created database.
The full query for these settings
This is the full query that creates the database as well as the user with the required privileges, and then initialize the tables.
Installing ODBC on each computer
You can read more about MySQL ODBC prerequirement here.
It is necessary to install a 32-bit MySQL Connector/ODBC (formerly known as MyODBC Driver) on all networked computers. This can be downloaded from http://www.mysql.com/products/connector/ .
Installation is simple with the Group Policy Management Console. We need to create a new GPO, or we can select the one we already use when starting AIDA64. Right-click the GPO, and select “Edit”.
Here, we can select if we want to assign the software installation to computers or users. In order to do this, we need to expand Computer configuration / Policies / Software settings / Software installation in the menu tree and select New Package.
Then we have to browse to the MSI file we want to install. The file should be available via a UNC path in a network share with read permission. Click “Open”, then in the “Deploy Software” window select “Advanced”, press the OK button, and click the “Security” tab. Here, grant read permissions to the users for the installation. “Authenticated users” have read permissions by default.
When we click OK, we immediately add the new software to be installed to the list.
At this point, only one thing remains to be done: refreshing the group policy on the clients. As we set up an event that runs at start-up, we will need to restart the PCs.
If we use a MySQL database, we can specify a data source, although this is not required. We need to specify the server name or IP address and the port required for the database connection as well as the user name/password of the created user, then select the database and the driver. MySQL optimization, which accelerates the process of inserting new records to the database, requires MySQL 3.22.5 or newer.
Then we have to click the “Test” button to check the settings.
Finally, we need to check the firewall settings. We should open TCP/UDP port 3306 to allow all computers on the network to connect to the database.
Creating and configuring the MS SQL database
We can use MS SQL Server on both smaller and larger networks.
First, open MS SQL Management Studio on our server, and after connecting to the SQL server select the server authentication mode we want to use (Windows Authentication mode or SQL Server and Windows Authentication mode). By right-clicking the Databases folder, create a new database called AIDA64. Then right-click on this database, and start a new query using the “DB – SQL Server.sql” schema located in the AIDA64 / SQL_Schema folder to create the required tables.
It is recommended to create a new user on the SQL Server. To do this, expand the Security folder and rightclick on Logins then select “New Login”. On the General page, add the user name, select SQL Server Authentication and type the password. Finally, select the newly created AIDA64 database as the Default database, select the Default language, then set the user privileges on the User Mapping page. Here, select the AIDA64 database and grant write (db_datawriter) and read (db_datareader) roles to it.
In the Preferences window in AIDA64, select the Provider “MS SQL Server”, add the server name and the authentication information (or select “Use Windows authentication”). If we have the appropriate privileges, we will be able to select our newly created AIDA64 database in the Database field.
If we want to use the “BULK INSERT” function, we need to add the “bulkadmin” role to the database on the “Server Roles” page in SQL Server Management Studio. This can accelerate the process of inserting new records to the MS SQL Server database. This feature requires a shared network folder with write permission for all users.
If we get a warning message on the client computer notifying us that the PCs cannot connect to the server, or there are problems with the connection, we need to run the following command on the SQL Server. This will open port 1433: