Windows in general, and SQL Server in specific, are spectacularly unsuitable for any kind of permanent, tamper-resistant logging. Tamper-resistant audit trails can include a lot of protections, but the #1 protection must be that once written, the original audit data can be recovered despite anything that happens at a logical level.
Note also that for those systems where logging is required, the system must fail if the logging fails - logging becomes, in database parlance, part of the ACID compliant transaction. If your system is allowed to continue operating when logging is failing, logging is not required - it may be desired, or requested, or wanted, or a best practice, but it is not required, since you may operate without it.
Devices that can store permanent, tamper-resistant logging include, but are not limited to:
Line printers with fan-fold paper
WORM platter jukeboxes*
CD/DVD/Blu-Ray jukeboxes with read-only media**
WORM tape jukeboxes*!
I would assume that United States SEC (Securities and Exchange Commission) guidelines have more detail on tamper-resistant auditing (i.e. auditing that's difficult to change after a bad actor commits financial malfeasance).
*In at least some modes, the OS can perform open a new file, write to it, close it, open it again and perform random write write operations even over previously recorded data - the platter's "journal" must be read to see whether this has happened, and if so, retrieve "prior versions", which both tend to be incredibly slow and cumbersome processes.
**I have to assume the above can also happen (see: Puppy Linux) but without the benefits of a good journal.
*! I suspect the above can happen, but I've never worked with these.