Support Logging to external MariaDB/Microsoft SQL Servers though new App(s)

[Cossey]

I would love for all the DNS requests to be logged to an external SQL server. I personally am looking for MariaDB support, but it makes sense to support Microsoft SQL Server also.

It would likely be a similar to the "Query Logs (sqllite)" app. Most likely, I assume this would be the App to base these other two on, with the SQL specific syntax and libraries dropped in and replaced.

The benefit for this would be:

• building reports based on data (ie via Grafana)
• better search via sql queries
• better performance for reading data

[ShreyasZare]

Thanks for the request. This is already planned but not getting prioritized due to some pending features being worked on.

[ShreyasZare]

Technitium DNS Server v13.4 is now available which adds Query Log apps for MySQL and MS SQL Server. More DB support will be added soon. Do update and let me know your feedback.

[dasunsrule32]

What are the database schema/tables? I don't see a sql database to import with that preconfigured and it doesn't create it if it sees the tables are missing.

Error! Table 'technitium.dns_logs' doesn't exist

[dasunsrule32]

Found the source where it should create the tables, but it didn't do it. I ran the SQL script against the database and it successfully created the required tables.

f00c5e3#diff-b937ebc0cb2d5b4d8b759e116ccb59574900e117469ecac3c9aade6f401908faR330

CREATE TABLE IF NOT EXISTS dns_logs
(
    dlid INT PRIMARY KEY AUTO_INCREMENT,
    timestamp DATETIME NOT NULL,
    client_ip VARCHAR(39) NOT NULL,
    protocol TINYINT NOT NULL,
    response_type TINYINT NOT NULL,
    response_rtt REAL,
    rcode TINYINT NOT NULL,
    qname VARCHAR(255),
    qtype SMALLINT,
    qclass SMALLINT,
    answer VARCHAR(4000)
);

[dasunsrule32]

Running into another issue where it's attempting to connect to the database mysql rather than the one specified.

Error! Access denied for user 'technitium'@'%' to database 'mysql'

Config:

{
  "enableLogging": true,
  "maxQueueSize": 1000000,
  "maxLogDays": 180,
  "maxLogRecords": 0,
  "databaseName": "technitium",
  "connectionString": "Server=192.168.5.2; Port=3306; Uid=technitium; Pwd=<strong-password>;"
}

[dasunsrule32]

So the previous error is with logging enabled. Disabling that will allow connections. Logs do not seem to update live, sometimes restarting the dnsserver docker container will dump the logs into the mysql database. Not sure what I'm missing there.

[ShreyasZare]

@dasunsrule32 thanks for the feedback. There are some issues which I will get fixed with an update soon. The app also needs you to grant privileges manually from mysql admin prompt to work.

So the previous error is with logging enabled. Disabling that will allow connections. Logs do not seem to update live, sometimes restarting the dnsserver docker container will dump the logs into the mysql database. Not sure what I'm missing there.

If you disable logging in config then the app will stop working. When logging is enabled, the app will take max 10 sec to update the logs to database, its done with a bulk update and not updated immediately.

[dasunsrule32]

Great! What permissions does it need? I only assigned all permissions to that user on the database in question only.

[ShreyasZare]

Great! What permissions does it need? I only assigned all permissions to that user on the database in question only.

For now, give permissions to the mysql db too. Once the update is available, this can be revoked.

[dasunsrule32]

I just gave it read-only access to the mysql database for now and that allowed it to connect.

[ShreyasZare]

Technitium DNS Server v13.4.1 is now available with update to the MySQL and MS SQL Server apps. Do update and let me know your feedback.

[gigantuar]

Just reconfigured my servers to log using MS SQL and so far it is working wonderfully. Works much nicer via Grafana instead of shipping all the logs over to a Loki instance.

Queries via the Technitium web interface against the SQLite DB with 6 million logs took about 10 seconds without time filters.

Might see if I can import the SQLite DB into MS SQL for the historic data and see what the performance impact is.

[ShreyasZare]

@gigantuar thanks for the feedback.

[Cossey]

Thanks for working on this. Unfortunately MySql.Data.MySqlClient is not compatible with MariaDB 10.10.1 or later.
When attempting to connect to the database it'll throw Error! Object cannot be cast from DBNull to other types and won't be resolved because MariaDB divergence from MySQL blah blahs.

Fortunately MySqlConnector is still compatible with later MariaDB versions so I can use your source code MySQL source as a base to work from.

[ShreyasZare]

@Cossey thanks for the feedback.

Thanks for working on this. Unfortunately MySql.Data.MySqlClient is not compatible with MariaDB 10.10.1 or later. When attempting to connect to the database it'll throw Error! Object cannot be cast from DBNull to other types and won't be resolved because MariaDB divergence from MySQL blah blahs.

Yes, MariaDB will need a separate app to make it work.

Fortunately MySqlConnector is still compatible with later MariaDB versions so I can use your source code MySQL source as a base to work from.

I am not really sure but I think MySqlConnector is windows only and not supported on other platforms. Do check if it works with Linux.

The code for the app will be almost the same with minor changes. If you manage to get it working for both Windows and Linux platforms then do make a PR.

[Cossey]

I just did it now, took like 5 minutes. I copied the MySQL app into a new folder, everything the same BUT:

• In the app.cs I removed the using MySql.Data.MySqlClient; and replaced it with using MySqlConnector;
• Removed the reference to MySqlClient in nuget
• Replaced it with MySqlConnector by Bradley Grainger in nuget

It was a drop in replacement so no changes to code required. Everything including config remained the same. Zipped up the compiled and uploaded to tdnss! MySqlConnector is cross platform AFAIK. I actually use it in another .net app running in docker on debian bookworm (I use tdnss in docker on top of Debian Bookworm also).

Obviously more testing is required.

I can do a pull request, but busy working full time so may not happen soon. However as the MySqlConnector is also compatible with MySQL, might be worth considering this vs the MySqlClient? That way it can cover both databases without having another app, if the testing works for others.

[ShreyasZare]

@Cossey In that case, having the existing app use MySqlConnector would make sense if its working well for all platforms and both the databases.

[dasunsrule32]

Technitium DNS Server v13.4.1 is now available with update to the MySQL and MS SQL Server apps. Do update and let me know your feedback.

I was able to remove the permissions to mysql and it connects without issue now. Thank you.

[ShreyasZare]

@dasunsrule32 Thanks for the feedback.