Passing userId to SQL Server delete trigger from entity framework

If you’re in an enterprise environment some form of auditing is likely to be required so you can track who inserted, updated or deleted what in your database. Using triggers to insert into audit tables is usually a good fit for this, particularly if your database can be updated from multiple sources.

Within both insert and update triggers you have access to the original record inserted or updated from your web app so you can access the updated_by column from the original record and pop this into your audit_* tables. In the case of deletes however, there never is any user information passed to the delete statement so delete triggers don’t know who specifically (the DB connection itself is usually shared) issued the delete. Using a session like feature in SQL Server 2000+ called CONTEXT_INFO however we can ‘pass’ the specific id of the user issuing the delete from our web app to the trigger.

Passing user info to delete trigger using CONTEXT_INFO

CONTEXT_INFO allows us to associate a limited (very limited but enough space for user info) amount of information with the current SQL Server connection. This info can then be read back at any stage from within the same connection. Using this to pass user info to a trigger is quite easy then:

  1. Set CONTEXT_INFO to userID logged into your web app
  2. Issue delete statement from your web app
  3. Read CONTEXT_INFO back in the delete trigger

As long as all three above happen in the same connection this should work fine. Setting the context is easy, but is best done from within a store procedure to encapsulate the code to deal with CONTEXT_INFO (cumbersome binary). SetUserContext below takes the user id as a param and stores it into CONTEXT_INFO.

CREATE PROCEDURE [dbo].[SetUserContext]
 @userId NVARCHAR (30)

 DECLARE @context VARBINARY(128)
 SET @context = CONVERT(VARBINARY(128), @userId)


Reading CONTEXT_INFO is best done through a function so you can just directly call it from your delete trigger statement that inserts into your audit_* tables. The GetUserContext function below returns CONTEXT_INFO if it is set or the standard suser_name() if it is not set.

Checking for null and falling back on the connected DB user is important if a DB can be updated directly by an admin or support person outside the context of an app which would set CONTEXT_INFO.

CREATE FUNCTION [dbo].[GetUserContext] ()

  SELECT @IdToReturn = suser_name()
 RETURN @IdToReturn

And finally a sample delete trigger which calls the function is below:

     FROM deleted AS d

After you have created the sproc for setting the context, the function for reading the context and have a couple of delete audit triggers created that’s things from the database point of view set up.

Setting CONTEXT_INFO using entity framework

In your web app you only care about setting the context. This can be done by simply calling the procedure. In entity framework this is easy:

public virtual void SetUserContext(string userId)
 var idParam = new SqlParameter("@userId", userId);
 this.Database.ExecuteSqlCommand("SetUserContext @userId", idParam);

Then in an overload of the SaveChanges method you simple call the SetUserContext sproc before you call the base SaveChanges method:

public void SaveChanges(string userId)
   using (var scope = Database.BeginTransaction())

Both the above functions would go in your database context class (the one which derives from DbContext).

Due to the fact that when executing stored procedures entity framework opens a connection, executes the proc and then immediately closes the connection, the context_info will be lost by the time we call SaveChanges which is when the delete trigger will run. To ensure both the setting and the reading of the context takes places in the same connection, we need to wrap them both in the same transaction as can be seen above.

What about connection pooling?

When a connection is being reused .Net SQL Server client executes the sp_reset_connection stored procedure which resets most connection specific state such as temp tables, cursors, transactions and crucially from the point of view of this article, CONTEXT_INFO. This is the case since SQL Server 2005 and above.

This means if you’re using connection pooling, which you really should be, you will not run into state sharing issues and thus connection pooling does not preclude you from using CONTEXT_INFO as part of your auditing solution. SQL Server 2000 users… get with the times or just reset CONTEXT_INFO after you issue your delete.

I hope you find the above useful. Please let me know your thoughts or questions below.

Related Links

List of what gets reset when a connection is reused from the connection pool

Stack Overflow question from a user who is concerned about creating race conditions if using CONTEXT_INFO

Another article about using CONTEXT_INFO from within Entity framework

Article about SESSION_CONTEXT which is a new and improved session mechanism in SQL Server 2016 which overcomes a lot of the limitations associated with CONTEXT_INFO

Prevent account enumeration on login, reset password and registration pages

Account enumeration is a potential security risk whereby a web site gives out information about what accounts are already in the system. This may a) leave them susceptible to a brute force-esque attack and b) may violate their users privacy which may be very important for certain types of sites. There are a couple of prominent Irish websites covered below which I’m familiar with and I just happened to notice they allow account enumeration but it is important to note that the vast majority of ecommerce sites online do this too. Certainly there are greater security risks out there such as SQL Injection, badly stored passwords, not using https when you should etc. but when this exploit is found alongside other exploits… well let’s just say that’s when bad things can happen.

keep login failed messages generic

I think most developers have the possibility of account enumeration in their minds when considering the messages returned from a login page for failed logins. When logging into most websites with a non existing username you will commonly see ‘Invalid username/password combination‘ type messages rather than ‘That username does not exist‘ type messages. An attacker now cannot just keep trying usernames on the login page until they find one that does exist, at which point they may (if possible) launch a dictionary password attack against that specific account. A few examples of Irish sites doing a good job here include and

Generic Login messages


Another prominent Irish site; does present different messages to the user if the account exists or not on their login page. The following image shows the login for non existing accounts:


It all looks good right? No mention that the specific email address does not exist, just that the address and password as a whole did not match any accounts. If however, you know of one account that does exist (the one the hacker just created for himself) and try to login with that you’ll notice that the message is different:


Ticketmaster do appear however to have an account lock out facility which locks accounts after 5 failed accounts. What this means is that although an attacker can find out if an account exists due to the slightly different error messages returned (which would be really bad for a very private site.. think they will only get five password tries thus a full on brute force attack against a specific account with a password dictionary is not feasible.

Although locking accounts help mitigate against brute force attacks there are a number of potential problems with this approach. It can for example leave open to an account lockout attack, whereby the attacker can deliberately lock out a large number of accounts in a denial of service-esque attack (this is possible as ticketmaster sends a reset password rather than URL to reset a password). It also means an attacker could build a large list of valid accounts and try the same password on all of them (ticketmaster1234 perhaps) which could return a few matches. Ideally ticketmaster would not reveal if an account existed or not. 

preventing account enumeration on the forgot password page?

The same idea should of course apply to the forgot/reset password pages. Unfortunately what is very common is that although a website’s login fail message is generic, the websites goes and undoes all this good work on their forgot/reset password page. Remember who displayed ‘If you have an account but can’t remember your password, click on “Password Problems?” below‘ regardless if the account existed or not? Well here’s what happens on their reset password page:

easons-forgotpassword1 easons-forgotpassword2

Remember who upon failed login just presented ‘Please enter a valid username and password’ to the user? The first image below is what the forgot your password screen shows when the account doesn’t exist, while on the right, we see what the screen shows for an account that does exist.








So previously undiscoverable accounts are now discoverable on both and via their reset password pages. This is probably not a huge deal from a privacy perspective (does your neighbour like books? / has your neighbour paid his water charges?) for these particular sites. If however these sites don’t implement some kind of brute force mitigation individual accounts could be vulnerable., as noted does appear to have brute force protection in place, this is just as well because their password reset page gives up existing accounts too:

What should happen in reset password scenarios is that the website should display the same generic message regardless if the username/email address exists. A message such as ‘Further instructions have been sent to your email address‘ or similar is often used in both cases. If the email address does exist the email can contain a password reset link (not a reset password in email – search for ‘Sending a reset password versus sending a reset URL’) which the email account holder can click on if it was them that asked for the reset or ignore if it was not. If the email account doesn’t exist in the user DB, just don’t send any email.

…but what about the registration page? I have to tell a potential new user that their chosen username/email is already taken on screen!

Not necessarily. Preventing account enumeration is most problematic on the registration page that’s for sure, at least from a usability perspective. The goal here is the same as with the login and password reset pages and that is to keep the outward (what’s on the screen) process the same for usernames/emails that already exist and those that don’t.

If the site uses the email as the username, it should be possible to do this pretty nicely and in a manner similar to before. It might require some re-jigging of your registration pages, but the idea would be to start the process by requiring the user to enter their email address. When they submit this notify them that they have been sent an email with further instructions regardless if the email is in the DB or not. If the email address is already in the system the email will advise them that they have already registered and perhaps provide a link to the password reset page. If the email address is unused the email will contain a time limited URL link to verify email and continue the registration process. There is a very small usability hit on this approach as the notification about email addresses already existing now happens via email rather than on screen, but this will affect only a tiny percentage of registrations.

Both and use the users email as their username so could use the above approach. Their current registration processes let us all know if accounts already exist or not:

easons-createaccount ticketmaster-createaccont

Note – is not really applicable in this case as their registration process begins with the entering of a application ID and associated pin which has been sent out in the post.

What if a website uses arbitrarily chosen usernames rather than email addresses to login? Well, for sure preventing account enumeration in this case is a bigger problem than above. In this instance, the first step of the process could capture both of these and in the follow up email notify if the email entered is already taken and advise of the username already associated with the email. Not a major problem so far… but what if the email is available but the username is already being used by a different user.

On popular sites, millions of usernames could already be taken, so it would be impractical to send a ‘username already taken’ email everytime someone tries to use an existing username as it could take them god knows how tries to find a free one. In this case, the best balance between security and usability may be to notify the user trying to register that their desired username is already taken on the registration page itself rather than via email but also to reCAPTCHA enable the page so scripts won’t work. This means the task of trying to find if a username already exists requires manual intervention by the attacker which significantly reduces their ability to build lists of existing accounts. Of course reCAPTCHA from Google and similar plugins by others are not unbreakable so there is no 100% perfect solution here. Your specific implementation is likely to be yet another trade off between security and usability.

suggestions to prevent account enumeration:

  1. Use email addresses as usernames as this makes accounts easier to protect on password reset and registration pages.
  2. Show generic ‘Invalid username/password combination’ message on failed login.
  3. Show generic ‘Further instructions have been sent to your email address’ message on password reset. If email is already in DB, send a time limited reset password URL
  4. First registration step is user enters email address and submits, then the site shows generic ‘Further instructions have been sent to your email address’ message. If email is already in DB, email says already registered etc. If email not in DB, email should contain a continue registration URL.

related Links

Troy Hunt’s (my favourite microsoft MVP by a mile) amazing article ‘Everything you ever wanted to know about building a secure password reset feature‘ from 2012 which was how I was originally introduced to many of the ideas above.

Troy Hunt’s Secure Account Management Fundamentals PluralSight course. Another amazing resource from Troy.

OWASP page on preventing brute force attacks. Note in particular the section on account lockouts and the potential exploits that remain even when doing this after X bad attempts. The surface space of many of these exploits can be reduced by preventing account enumeration on login, reset and registration pages as per above.

please can I have your comments and/or questions?

I’m very interested to know your thoughts on the whole topic of account enumeration so please feel free to leave a comment. If you’re a developer or IT professional and would like to connect with me on LinkedIn please check out my profile and send me a connection request.

Organise ASP.MVC projects by feature by changing view location in MVC

I think it would be a safe bet to say most people stick with the default ASP.Net MVC folder structure and set up whereby the project structure is organised by architectural role (e.g. models, views and controllers) and not by business feature (e.g. registration, shopping cart, order history etc. ). For small projects to me this is fine. For bigger projects however it can be cumbersome to navigate as code related to particular features are scattered over multiple folders.

This ‘ by architectural role’ organisation goes against how in my experience software is written. Usually we build systems out feature by feature rather than writing all HTML markup, all controllers, all model classes etc. During development, this is particularly true in teams that embrace agile with its focus on delivering something that works and adds value at the end of each sprint. Imagine spending a three week sprint just writing viewModels… business value derived at end of sprint = 0. Check out this post on stackoverflow for some interesting comments on the merits of organising MVC by feature.

I’m sold… how do I organise my ASP.Net MVC project folders by feature? Well, given that MVC searches for controllers by type and name (not folder location) you can simply just start creating folders for ‘Registration’, ‘CheckOut’ etc. in the root and put your controllers, viewmodels and models in them and MVC will work fine. Views however are required to be in one of a number of predefined locations so simply creating folders won’t help you here. MVC is very extensible so we can of course change view locations and I will outline all the steps required to do this later. First however let’s look at MVC built in ‘Areas’ to see if they can meet our requirements ‘out of the box’.

Organise MVC projects using areas

Areas are around since MVC 2 and are logical groupings of controllers, models and views and other related folders (such as images, JavaScript etc.) for a particular module or feature in an MVC application.

mvcAREASA downside of areas unfortunately is that within them things are still organised into three folders; models, views and controllers. Yes everything within an area is related to a particular feature (a big step in the right direction) but code within that feature is still scattered as can be seen from the image to the left.

If this project layout gives you what you need, excellent, areas is what you should use. The extra advantage of using areas is of course they are available out of the box so if you have problems with them there is likely to be a lot of help available online. Snesh Prajapati’s article on areas in MVC 4 (no major changes in MVC 5 (not sure about MVC 6) except for how they are added I believe) on is one of the better articles on areas. In her article she notes the downsides of just creating moduleA, moduleB etc. folders under each of the root controllers, models and views folder. She also outlines the routing related code needed to wire up areas.


For my recent projects areas didn’t meet my needs as I was aiming for a structure similar to the right whereby everything could be in one folder. I know I could get close to this by just putting controllers, models and viewmodels in an area root and deleting the controllers and models folders. This however means my URLs get longer (a consequence of using areas), linking between areas becomes a little more ‘complicated’ and of course I’m still forced to put my views in the views folder (albeit a feature specific one).

Steps required to change view location in MVC

Areas don’t check all my checkboxes so I needed to find out how to change the location of where MVC looks for views. If you’re interested in doing this yourself, note there are a number of steps involved (overriding the default viewEngine is not enough!!!) but all are pretty easy to follow. The steps to change view location in MVC are:

Clear existing viewEngines, add back RazorViewModel, set new locations

The first step to change view location in MVC is to remove existing view engines and add RazorViewModel back in and define where the views are. Below I’m instructing MVC to look in ~/Shared/Views/VIEWNAME.cshtml (which will be the new location for shared views) and ~/CONTROLLERNAME/VIEWNAME.cshtml for my normal views. After this MVC does not care about the default ~/Views folder anymore. Note to change view location in MVC you don’t have to create a new ViewEngine like many places recommend.

Set the new view paths in Application_Start to change view location in MVC


Copy old ~/Views/web.config into ~/web.config so views will work correctly

Merge the ~/Views/web.config file with into the root web.config file. Views derive from System.Web.Mvc.WebViewPage and the root web.config doesn’t have this assembly referenced by default. Referencing it from root web.config means you don’t have to add a web.config for each new feature folder you add. Not everything needs to be brought across as some of it is there already, just focus on sections highlighted in yellow below.



Edit handlers in ~/web.config to explicitly allow JS, CSS etc. to be served

Set BlockViewHandler path to ‘*.cshtml‘ in the now updated root web.config. The web.config in the views folder had this set to ‘*’, meaning IIS would, for security reasons refuse to serve any files from the views folder. Setting it to .cshtml means all the CSS and JS will render correctly…. but wait…..

cshtml blocked

… note this is the blacklist approach where we are explicitly blocking .cshtml files from being served. We may add a new type of file in the future to a location in the project that doesn’t have it’s own web.config (and hence uses root web.config) which we do not want served but if we forget to change the above it will be silently served.

A more secure approach is to whitelist and explicitly allow whatever extension we know to be safe to be served. I’m obviously recommending the whitelist approach but I’ve included the blacklist approach just to illustrate the differences between the two as not many articles discussing how to change view location in MVC mention this when they advise the whitelist approach.



Move views to new shared views folder, update _ViewStart.cshtml and tidy up

Copy all files ~/Views/Shared to ~/Shared/Views. The original views/shared folder is now a dead duck, bring whatever views (most likely _Layout.cshtml at least) you need from there to the Shared/Views folder which hangs off the root.

Copy _ViewStart.cshtml from ~/Views to ~ and update the layout path inside it to point to new location of the _Layout.cshtml file.

new viewstart

Delete existing ~/Views folder as we have everything we need from it now.

Delete controllers and models folder in root when you moved all your classes to their own feature folders.

Related Links

The above relates to MVC 4 and MVC 5. MVC 6 has not been released yet, it’s still in RC1 and RC2 is not yet scheduled so it could even be 2017 before we see a final RTM version. If you’re playing with RC1 and looking to know how to do change view locations with it, the following two links may help:

Customize ASP.NET MVC 6 View Location Easily
What’s new in MVC 6: View Location Expanders


matthewFor an absolutely great overview of ‘Clean Architecture‘ which is the underlying pattern behind the idea of organising software around business features please check out Matthew Renze’s excellent video on Youtube.

Software development improvements from a recent sprint retrospective I participated in.

Wheel-of-FiveIn my current contract the use of agile processes is relatively new to the organisation as a whole and certainly to our team. This means we are still refining our processes, so things like sprint retrospective meetings were we meet at the end of a sprint and try and figure out what things went wrong and hence we should do less of, what things went right and hence we should do more of, what we can do better etc. are very important.

We had one of these sprint retrospective meetings recently and it was one of the better ones I’ve been involved in any previous role or project. I thought it would be nice to share some of the suggestions which came out of it, as I’m sure you have had similar suggestions particularly when agile was relatively new in your environment. Suggested process changes from our retrospective are below:

Split the daily scrum into multiple ones based around project teams. The team as a whole is 8-10 .net developers, format to date was one big stand up corresponding to multiple projects, meaning we all had to listen to other people talking about projects not related to us. For next sprint we are going to do separate scrums for each project (although we are going to keep the one retrospective for shared learning purposes), which should save us time.

Move the daily scrum to morning time, we had been previously doing it in the afternoon, nothing particularly went wrong or anything, just most of us are used to doing it in the morning from previous roles. Also having them early allow us to work on removing any identified blockers that day, whereas with scrums closer to the end of the day, issues are more likely to be forgotten about as people are starting to head off home shortly after the scrums. I suppose too that morning scrums align better with the three questions which are most commonly suggested for daily scrums, what did I do yesterday, what will I do today? and any blockers? A question about the best times for the standup on Quora has lots of different viewpoints. We are going to try morning scrums for a sprint or two at least anyhow and then review at another sprint retrospective.

Actually stand up in the daily scrums to encourage quick and to the point scrums. CurrentlyAAEAAQAAAAAAAAPqAAAAJDc5Y2YyM2VjLTllM2QtNDY1Yi04NDUyLTFhZDJmOTA2MWQ5Nw some of us seem to stand, some of us sit. I guess sitting during the scrum could possibly kind of maybe make it seem like a break. For the next sprint sitting will be frowned upon and be punishable by having documentation related tasks assigned to the culprits.

Stick to high level task related conversation in daily scrum, what I did (5)Daily scrum meeting 3 questionsyesterday, what I will do today, anything in my way? Really technical things should be ‘taken offline’. Everyone is going to be mindful of this during the next sprint, hopefully it will save us a small amount of time anyhow.

Finish one vertical slice/use case set of the application completely
before starting on new pages, unless a very good reason not to. At the moment we appear to be doing a few tasks from one page, then another few from a different page. At the end of the sprint when at the client demo we then need to say to the client you can test this page, but not this particular feature etc. Next sprint we are aiming to very selectively breakup parts of a particular piece of functionality that different team members can work on without stepping on each others toes (the system is not huge) so essentially we are going to try and go down (finish a section completely) rather than across (adding new pages).

Quick peer review of proposed steps to implement before someone begins on a new type of task/page. Although the team is very collaborative, there has been occasion were a developer (myself included) has gone off on a tangent and implemented something that was completely not needed or in a manner that was superfluous or more complex than it needed to be or just down right wrong. This quite often resulted in a lot of lost time due to rework. To minimise the chances of this we agreed for the next sprint that for any task that is new to the application(s) and hasn’t been done before will we get the person assigned the task to briefly outline the problem space and in four or five bullet points how they propose to implement the feature or page. After that the rest of the team will peer review and hopefully if something obvious is being missed as a collective we will be able to spot it.

tumblr_inline_nog5m4UVp61ry6czh_400Deploy to dev and uat via Visual Studio publish tool with web.config transforms so no manual manipulation needed. We were kind of half doing this but the deployment process is very easy to get wrong so for all future sprints we are going to lock this down fully as its quicker and more reliable.

Apply TFS label to the source code at the end of sprint so we can know what we presented to the clients, what version we fixed bugs in, introduced bugs in, easily restore, more easily prepare changelog files by comparing labels for sequential sprints etc.

Overall I think the team found the sprint retrospective very valuable, we are certainly looking forward to seeing if the process changes above have a positive effect on the burndown chart. Please share some of the suggestions that have come out of your sprint retrospectives too, particularly the ones that made a big difference for the subsequent sprints. Also note that I am actively building my LinkedIn network so if you’re an IT professional and interested please connect with me on LinkedIn. 

WordPress preview post not working… quick workaround

WordPress preview post links not working? Sick of unsuccessfully trying different things you’ve found on Google like I was? Pretty much every article I read offered a different solution. Some suggested it was related to a bad theme update, others that is was related to caching, others still pointed the finger at mod rewrite/isapi rewrite rules. Eventually I gave up and just went with a preview post workaround.

WordPress preview post not working workaround

Wordpress preview post not working workaroundWell a quick workaround for you might be to just go ahead and publish the post but set its visibility to privatePrivate content is only visible when you are logged in so can be used to preview posts before publishing to everyone. Note however other editors and administrators will be able to see this content too when they are logged in, but that may be fine for your case. You can set a post to private from the right hand side of the add/edit post page as shown above.

FYI: this is what the post looked like on the site before I made it public