This happened a few weeks ago, on a short one-off application that I wrote.
Here's the situation: ASP.NET web application hosting a web service. I just needed a minimal database, so used a SQL Express database, with the .MDF file contained within the ASP.NET application (connection string .SQLEXPRESS... yada yada).
On our production domain, we use a service account for Windows Authentication, which does not have interactive logon privileges (i.e. this is not an account we use to log on to the servers).
Gotcha #1 - SQL Express needs to rock some temporary data in C:Documents and Settings
My first surprise was that SQL Express could not create an instance because it needed to do some magic in the User folder (in this case it would have been C:Documents and Settings<service account here> etc etc). No problem, I enabled interactive logon for the service account just to get it up and running, logged on once to set up the profile goodness, and off we go.
Gotcha #2 - This is only temporary
To my surprise, I thought after logging on once with the service account once, creating the user profile, et al, we'd be good. No, that's not how technology works. A few days later it stopped working again. Log back on, and we're good.
So, when using SQL Server Express with service accounts, be careful and make sure that the user profile path is available!