SQL Server Auditing

Question

Post reply

SQL Server Auditing

simonsmithbsa

SSC-Addicted

Points: 461
More actions
June 28, 2017 at 8:48 am

#331337

We are installing SQL Server 2016 and are looking to use the Auditing functionality to have a proper audit trail of who (and when) is making changes to data within our databases.

My question is whether we can capture username from the application rather than the hostname or the SQL login account. The users will be using a web application create in Entity framework.

Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ryanbesko Ten Centuries Points: 1328 More actions · Answer 1

simonsmithbsa - Wednesday, June 28, 2017 8:48 AM

We are installing SQL Server 2016 and are looking to use the Auditing functionality to have a proper audit trail of who (and when) is making changes to data within our databases.

My question is whether we can capture username from the application rather than the hostname or the SQL login account. The users will be using a web application create in Entity framework.

Thanks

In your application's Web.config file you will need to add <identity impersonate="true" />. It will then pass the credentials of the Windows user to SQL. There are other IIS settings that need to be adjusted, such as disabling Anon access and only having Windows Auth on. More good reading about this here:
https://stackoverflow.com/a/7441365

crow1969 Hall of Fame Points: 3001 More actions · Answer 2

Most likely not. The application would have to pass the (web) user's name to the SQL Layer. SQL Server can only see the communication between the web server and the SQL Server. It can't see the communication between the client and the web server.

You could use the IIS logs to track who is logging in, and hitting which page, but it will be a chore to match a particular SQL statement with an individual session on the web server.

simonsmithbsa SSC-Addicted Points: 461 More actions · Answer 3

ryanbesko - Wednesday, June 28, 2017 8:57 AM

simonsmithbsa - Wednesday, June 28, 2017 8:48 AM

We are installing SQL Server 2016 and are looking to use the Auditing functionality to have a proper audit trail of who (and when) is making changes to data within our databases.

My question is whether we can capture username from the application rather than the hostname or the SQL login account. The users will be using a web application create in Entity framework.

Thanks

In your application's Web.config file you will need to add <identity impersonate="true" />. It will then pass the credentials of the Windows user to SQL. There are other IIS settings that need to be adjusted, such as disabling Anon access and only having Windows Auth on. More good reading about this here:
https://stackoverflow.com/a/7441365

I will investigate this, thanks.

Allen Rich SSC Enthusiast Points: 128 More actions · Answer 4

For details about SQL Server Auditing, please refer to the following article.
https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine
https://www.codeproject.com/articles/166264/audits-on-ms-sql-server
Alternatively, you may try this automated solution ( https://www.lepide.com/lepideauditor/sql-server-auditing.html ).