Integrated security and SQL Server in ASP.NET
On a project I’m currently developing I needed to keep track of changes to database records made by users. Typically you would add 4 columns for this purpose: CreatedBy, CreatedOn, ModifiedBy and ModifiedOn. Filling these columns can be done via business logic offcourse, but I was looking for a sweeter solution (and found one!).
As most of you guys probably know, getting the current date/time isn’t that hard in SQL code. Just use the GETDATE() function as the default value in you INSERT and UPDATE queries. Ok, but how about the username? Here’s where integrated security is going to help us.
ASP.NET provides integrated security to be able to use the Windows account of the user using your application for other things in need of authentication (writing files, connecting to other computers, and also to SQL Server databases). The only thing you need to do to enable integrated security is setting <identityimpersonate=“true“/> and <authenticationmode=“Windows“/> in your web.config file. Using integrated authentication with SQL Server isn’t that hard either; just provide the string Integrated Security=True in your connectionstring.
Now you need to grant access to the domain users to your SQL Server database. Default public rights are enough here, don’t overdo it because you don’t want to grant too much rights to all of your Active Directory users. That’s it! To retrieve the username of the user currently logged into SQL Server you may use ‘USER_NAME()’ in your queries. So now you’re ably to fill all 4 columns mentioned above without having to write a single line of code; isn’t that sweet!?
Leave a Comment