I am looking for a clean way to do audit trails in MS SQL Server, for compliance reasons, preferably completely on the database side without involving the web application.
When talking about audit trails I mean a complete log of the changes to the database at the data level. So, who changed what and when?
I have a setup with active directory as authentication backend and I need to have a Java based web application developed.
So in order to get a valid audit trail I would need to have the information about who changed what and when. AFAIK there is a feature called SQL Server Audit which can provide exactly that.
My question is about the user management and authentication on the MS SQL side of things. The web application should use the AD users and then access the database. I would thus concur that the AD users need to be relayed to the MS SQL DB by the web application in order to show up in the audit trail.
This seems to be confirmed by this MS doc: https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/getting-started-with-database-engine-permissions?view=sql-server-2017
Which suggests that in a typical scenario one would try to manage users and groups via AD and have MS SQL just map these groups to roles on the database. These roles would in turn be granted privileges.
On the other hand some of my colleagues are saying that we should use one shared application account for database access and build the audit trail ourselves in the application. They are citing vague security reasons.
What is the right thing (tm) to do?