Query Logs SQL Server App

A DNS App for Technitium DNS Server that logs DNS queries to a Microsoft SQL Server database.

Overview

Async logging – writes log entries through a bounded queue
Cleanup support – prunes old records by age/count
Retained schema – uses a database name and SQL Server connection string for storage

Integration / extension points

Implements: IDnsApplication, IDnsQueryLogger, IDnsQueryLogs
Runs as a DNS query logger with asynchronous persistence.

Database

The database table stores values for some fields in numeric format. The fields and the values are described as below.

Protocol Field

Protocol	Value	Description
0	UDP	The standard DNS over UDP protocol
1	TCP	The standard DNS over TCP protocol
2	TLS	DNS-over-TLS RFC 7858
3	HTTPS	DNS-over-HTTPS RFC 8484
5	QUIC	DNS-over-QUIC RFC 9250
253	UdpProxy	PROXY Protocol over UDP
254	TcpProxy	PROXY Protocol over TCP

Response Type Field

Response Type	Value	Description
1	Authoritative	Response generated by the DNS server itself
2	Recursive	Response received from a recursive query to upstream
3	Cached	Response generated by DNS server's cache
4	Blocked	Response generated by DNS server to block a request
5	UpstreamBlocked	Response received from an upstream blocking a request
6	UpstreamBlockedCached	Response generated by DNS server's cache containing blocked response from upstream
7	Dropped	A `null` response generated by the DNS server indicating that the request was dropped

Configuration

dnsApp.config contains these keys:

Property	Type	Default	Description
`enableLogging`	boolean	`false`	Enables or disables query logging.
`maxQueueSize`	number	`1000000`	Maximum number of log entries allowed in the in-memory queue before new entries are dropped.
`maxLogDays`	number	`0`	Maximum age (days) to retain. `0` disables age-based cleanup.
`maxLogRecords`	number	`0`	Maximum number of records to retain. `0` disables count-based cleanup.
`databaseName`	string	`"DnsQueryLogs"`	Database name used to store logs.
`connectionString`	string	(required)	SQL Server connection string without selecting a database/initial catalog. The app uses `databaseName` separately.

Example

json

{
  "enableLogging": false,
  "maxQueueSize": 1000000,
  "maxLogDays": 0,
  "maxLogRecords": 0,
  "databaseName": "DnsQueryLogs",
  "connectionString": "Data Source=tcp:192.168.10.101,1433; User ID=username; Password=password; TrustServerCertificate=true;"
}

Runtime behavior

Queries are buffered in a bounded channel.
A background consumer thread bulk inserts records into SQL Server storage.
A periodic cleanup timer removes old records.

Risks / operational notes

Queue overflow drops writes (DropWrite behavior).
Database connectivity issues can stop logging.
High traffic deployments should monitor write latency.

Troubleshooting

Confirm the database is reachable and credentials are valid.
Check the connection string and databaseName.
Review server logs for SQL client errors.