After a lot of trials, tribulations and errors, I found a solution to the problem. Please mark this as an answer if it turns out helpful. I cannot vouch for the security of this solution. Some say its bad, but I say its good because it works.
1. Update your connection string to the following:
Driver={SQL Native Client};Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf; Database=dbname;Trusted_Connection=Yes;
Why is the Database parameter needed? If the named database have
already been attached, SQL Server does not reattach it. It uses the
attached database as the default for the connection.
2. Under the application pool properties in the identity tab, set "Network Service" or "ASP.NET" as the security account.
3. Make sure you grant read and write permissions to "Network Service" or "ASP.NET" account. Give full permissions and adjust later to be on the safe (or unsafe) side.
COMMENTARY:
The security model for .NET, ASP.NET, SQL Server, and
Windows is extremely difficult to wrap your head around. You must know each technology’s security
model and how they interplay with one another in order to become
proficient and productive. The exception error messages don't really tell you what the problem is. There are no books or
articles that cover these security models comprehensively. Consequently, many
developers figure out what works by trial and error and wind up compromising security, which renders the security system useless.